stuck trying to get userform to update record....

[kbishop94]

I have the code figured out how to populate the form with a specific record that can be entered into a field, and then upon updating that field on the form, the rest of the form is populated wiht that specific record (which is what I want... ) However, I cannot get it to paste the new (updated) data to the existing record (it only copies it over to a brand new record and not the one that it should be copying over top of)

Here is the code I have thus far:

Private Sub txtIncidentID1_AfterUpdate()


[COLOR=#006400]' Lookup Incident ID Number[/COLOR]


If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtIncidentID1.Value) = 0 Then

[COLOR=#006400]' If no such ID exists:[/COLOR]

MsgBox "Incident ID not found."
Me.txtIncidentID1.Value = ""
End If

[COLOR=#006400]' ID is found so now the code is populating the rest of the user form with the data from that record linked to the specific IncidentID that was found:[/COLOR]


With Me


.DTPicker3 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 2, 0)
.cboLocation1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 6, 0)
.cboPriority1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 4, 0)
.txtCAPA1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 18, 0)
.cboCustomer1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 7, 0)
.txtProblem1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 8, 0)
.txtAction1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 9, 0)
.cboIssuedBy1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 10, 0)
.cboOnBehalfOf1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 11, 0)
.cboIssuedTo1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 12, 0)
.cboIssuedTo21 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 13, 0)
.txtCostProd1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 20, 0)
.txtCostShip1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 21, 0)
.txtCostConcess1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 22, 0)
.txtCostTravel1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 23, 0)
.txtCostFacility1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 24, 0)
.txtCostOther1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 25, 0)
.txtCost1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 16, 0)
.txtNotes1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 19, 0)

Now here is where I cannot get it to place the data correctly onto the existing record that was selected:

I have found various methods of accomplishing this, but nothing I try i can make work (?) Thanks for any assistance offered.

 

[Yongle]

You could simplify the look of your code and its readability by

1. Using a variables
Me is not required wth the userform - sometimes it helps to clarify references

2. Using With Application.WorksheetFunction

Dim [COLOR=#ff0000]DynRng[/COLOR] As Rng, [COLOR=#ff0000]Inc_ID[/COLOR] As String

Set DynRng = Sheet1.Range("DynamicRange")
Inc_ID = txtIncidentID1

With [COLOR=#ff0000]Application.WorksheetFunction
[/COLOR]
  DTPicker3 = .VLookup(Inc_ID, DynRng, 2, 0)
  cboLocation1 = .VLookup(Inc_ID, DynRng, 6, 0)
  cboPriority1 = .VLookup(Inc_ID, DynRng, 4, 0)
  txtCAPA1 = .VLookup(Inc_ID, DynRng, 18, 0)

etc
End With

3. Using underscores like this when naming objects
cbo_Location1
txt_CAPA1

 

[Yongle]

Values in your code are looked up in Sheet1.Range("DynamicRange")
The first column of that range is where you need to find the matching entry

Perhaps something like this to find the row in the range...

Dim DynRng as Range, Inc_ID As String, r As Long
set DynRng = Sheet1.Range("DynamicRange")
Inc_ID = txtIncidentID1
[COLOR=#008000]r[/COLOR] = Application.WorksheetFunction.Match(Inc_ID, DynRng.Resize(,1),0)

(DynRng.Resize(,1) is the first column of the range)

Your userform is populated like this

DTPicker3 = .VLookup(Inc_ID, DynRng, [COLOR=#b22222]2[/COLOR], 0)

The red number in the lookup is the column reference required

The r is the row reference, the number is the column reference in "DynamicRange"
Write to worksheet like this

DynRng.Cells([COLOR=#008000]r[/COLOR], [COLOR=#b22222]2[/COLOR]).value= DTPicker3.Value

It probably makes sense to add a textbox (txt_RowRef) to your userform to hold the value of r which would could then be used directly in your code. Something like this...

DynRng.Cells([COLOR=#008000]txt_RowRef[/COLOR].[COLOR=#008000]Value[/COLOR], [COLOR=#b22222]2[/COLOR]).value= DTPicker3.Value

 

[kbishop94]

Values in your code are looked up in Sheet1.Range("DynamicRange")
The first column of that range is where you need to find the matching entry

Thank you, Yongle. Everything you suggested worked PERFECTLY!

Thank you, I really appreciate it.

 

[Yongle]

Thanks for the feedback