List Box double click to goto record

[jeremy0028]

I have a list box which looks up ProviderID(PK), First Name, Last Name, MI from the tbl called tbladdprovider

i'm trying to create a double click event whereby when I double clik providers name it will take me to the form called frmaddprovider which will display provider information.

I have the following code

Private Sub List0_DblClick(Cancel As Integer)
stDocName = "frmaddprovider"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord
End Sub

Every time i double click it takes me to last record on the form and not the actual provider any ideas

 

[HandsOnAccess]

Here is the correct code

Private Sub List0_DblClick(Cancel As Integer)
stDocName = "frmaddprovider"
stLinkCriteria = "ProviderID = " & me.List0
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

NOTE : It is assumed me.List0 is bound to ProviderID,
In case it is bound to some other field, you may have to use me.List0.Column(<<Index>>) to refer proper value.

Hope this helps you...

Regards,

 

[jeremy0028]

If i wanted to add a text search on top and link it to list box 0 to search by providers in the list by last name how would i do it?

 

[HandsOnAccess]

Here is the code concept on how to implement it...

Private Sub Text1_AfterUpdate()
   If Not IsNull(me.Text1) = True and Len(Trim(me.Text1)) > 0 Then
      If Not IsNull(Dlookup("ProviderID","tbladdprovider","[Last Name] = '" & me.Text1 & "'")) = True Then
         me.List0 = Dlookup("ProviderID","tbladdprovider","[Last Name] = '" & me.Text1 & "'")
      Else
         Msgbox "Last Name Not Found.", VbCritical,"User Alert"
         Exit Sub
      Endif
   Endif
End Sub

Hope it helps you...
Regards