Edit Selected Row From Multiple Rows

[adam]

Hi,

I’m having a form with a list box where, when I double click the list box; the column contents of the selected row (from the list box) gets copied to the appropriate text boxes same as the code embedded in the workbook at Post=16710.

Since I’m having data rows with the same serial number as that of the worksheet in the workbook at Post=19270; the row that starts with the serial number gets edited instead of the row I was trying to get edited when I click the edit button.

How may I change the code so that it does what I’m asking for?

The code that I’m trying to imply the change is same as that of the second code at Post=19082

By the way, sorry for having too many references.

Thanks in advance.

[HansV]

Is there a combination of columns that is guaranteed to be unique, for example Serial number and Code?

[adam]

The same code will not exist with the same serial number however the same code would exist in another row with a different serial number, same as the workbook at Post=19270

[HansV]

That's not a problem. Instead of using Match to find the first row with the specified serial number, you should loop through the rows until you find the combination of serial number and code. This will be the row to update.

[adam]

Do you mean to change the line
r = Application.WorksheetFunction.Match(CLng(Me.txtSerial.Value), _
Range("Serial"), 0)

as

r = Application.WorksheetFunction.Loop Until(CLng(Me.txtSerial.Value), _
Range("Serial"), 0)
Please correct me if I am wrong.

[HansV]

That makes no sense at all. Combining VBA keywords at random will *not* work. You should really take some time to actually learn VBA. The time invested will be repaid manifold in the end, because you'll know how code works.

For the current situation, I'd have to see (a stripped down copy of) the workbook.

[adam]

For the current situation, here's a stripped down copy of the workbook.

[HansV]

Replace the part of the code that starts at

On Error Resume Next

and ends at

On Error GoTo 0

with the following lines:

Code: Select all

  ' Loop through the rows
  For r = 1 To Range("Serial").Count
    ' Compare both Serial and Code with the text boxes
    If Range("Serial").Cells(r) = Val(Me.txtSerial.Value) And _
        Range("Code").Cells(r) = Val(Me.txtCode.Value) Then
      ' If we find a matching combination, exit the loop
      Exit For
    End If
  Next r
  ' Did the loop continue until the end?
  If r > Range("Serial").Count Then
    ' If so, we didn't find a match
    MsgBox "Serial Number/Code combination not found", vbExclamation
    Exit Sub
  End If

I have added comments in the hope that you'll try to understand the code.

[adam]

Thanks Hans. That had helped.

Meanwhile, in reference to what has been told in Post=10053, I have added a worksheet with the name of Products.
With a new form included I’ve written a VLook up code which will fill the remaining text boxes upon filling the textbox txtCode.

But the code isn’t working after doing so.

What might be missing here?

The combo box included in the form selects the type of price to be filled in the textbox txtRate. If so how should I change the line

Me.txtRate = Application.VLookup(Me.txtCode, rng, 3, False)

so that the price of combo selection fills the textbox txtRate

Any suggestion would be kindly appriciated

[HansV]

You want to look up a number value. So you must use

Me.txtDescription = Application.VLookup(Val(Me.txtCode), rng, 2, False)

and similar for the others. For the rate: add a declaration

Code: Select all

  Dim lngCol As Long

at the beginning of the txtCode_AfterUpdate event procedure, and use

Code: Select all

    If Me.cboPriceType = "Discount" Then
      lngCol = 3
    Else
      lngCol = 4
    End If
    Me.txtRate = Application.VLookup(Val(Me.txtCode), rng, lngCol, False)

to set the rate.

[adam]

Thanks Hans. It works now. But the price does not get changed when the combo box in changed after filling the text boxes. Instead if the price type, either Normal or Discount is selected prior to filling the text boxes; the text boxes does get filled with the type of price that is in the combo box.

How may I change the price type after filling the text boxes?

[HansV]

You also need to create code for the cboPriceType_AfterUpdate event. This will basically be the same as the code for txtCode_AfterUpdate, but you only need to set the value of txtRate, not of the other text boxes.

[adam]

Thanks Hans. It worked.