Using a Vlookup when opening a new Form

[kurtc]

Morning all, I'm having some trouble with a Vlookup task i'm trying to complete.

i have a userform (form1) that has a combobox populated with a list of names, when one of those names is selected and the "Search" button is pressed, a new userform (form2) opens up with a number of different fields containing data related to that name (this is all kept in a hidden worksheet).

When doing this the first time it works perfectly, however there is a "Search again" button on form2 that takes you back to form1 to conduct another search. This also works fine, however if you select another name from the list and his "Search" again, it still displays the old information from the first search.

But i've also noticed if i hit "search again" on form2 again, dont change the name and click "search" in form1, it then does display the correct info for the new search in form2.

Any idea what may be causing the trouble? he is the code i am using in both forms

FORM1

Private Sub cmdSearch_Click()

If cmbDMC3.Value = "" Then
MsgBox "Unable to locate"

Else

dmclocator.Hide
DMClocatorresults.Show

End If

End Sub

Private Sub Cbclose_Click()

Me.cmbDMC3.Value = ""

dmclocator.Hide
Frontpage.Show

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then
Cancel = True
MsgBox "Please use the Back button", vbCritical
End If

End Sub

FORM2

Private Sub CBsearchagain_Click()

Me.txtDMCname.Value = ""
Me.txtCliadd.Value = ""
Me.txtCredadd.Value = ""
Me.txtClinum.Value = ""
Me.txtCrednum.Value = ""
Me.txtOpen.Value = ""
Me.txtDPA.Value = ""
Me.txtWebsite.Value = ""
Me.txtReg2.Value = ""
Me.txtCCL2.Value = ""
Me.txtGroup.Value = ""
Me.txtTrading.Value = ""
Me.txtComments3.Value = ""

Unload Me

DMClocatorresults.Hide
dmclocator.Show

End Sub

Private Sub UserForm_Initialize()

On Error Resume Next

txtDMCname = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 1, False)
txtCliadd = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 2, False)
txtCredadd = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 3, False)
txtClinum = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 4, False)
txtCrednum = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 5, False)
txtOpen = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 6, False)
txtDPA = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 7, False)
txtWebsite = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 8, False)
txtReg2 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 9, False)
txtCCL2 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 10, False)
txtGroup = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 11, False)
txtTrading = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 12, False)
txtComments3 = WorksheetFunction.VLookup(dmclocator.cmbDMC3.Value, _
Worksheets("Lists").Range("I:U"), 13, False)

Exit Sub


End Sub


Private Sub Cbclose_Click()

Me.txtDMCname.Value = ""
Me.txtCliadd.Value = ""
Me.txtCredadd.Value = ""
Me.txtClinum.Value = ""
Me.txtCrednum.Value = ""
Me.txtOpen.Value = ""
Me.txtDPA.Value = ""
Me.txtWebsite.Value = ""
Me.txtReg2.Value = ""
Me.txtCCL2.Value = ""
Me.txtGroup.Value = ""
Me.txtTrading.Value = ""
Me.txtComments3.Value = ""

DMClocatorresults.Hide
Frontpage.Show

End Sub

 

[Andrew Poulsom]

Duplicate:

http://www.mrexcel.com/forum/showthread.php?t=594489