I am using a form called 'Search Cl

[shaunacol]

I am using a form called 'Search Client" which has a listbox called 'List45' in it. I want t double click on list box and for another form called 'Frm_Client' to open with the record that was double clicked. My unique ID is ClientID. I have used the code below found on this site but obviously misinterpretted it as am getting 'data type mismatch' error. Can anyone see what ive done?


Private Sub List45_DblClick(Cancel As Integer)
Dim ctl As Control
Set ctl = Forms![Search Client]!List45
Dim varItm As Variant
For Each varItm In ctl.ItemsSelected
DoCmd.OpenForm "frm_client", acNormal, , "[ClientID]= '" & ctl.ItemData(varItm) & "'"
Next

 

[Golom]

Looks OK. Try changing it to the following. Set a breakpoint on DoCmd and look at the immediate window (ctrl-G) to see what values are being retrieved.

Dim Filter As String
Filter = "[ClientID]= '" & ctl.ItemData(varItm) & "'"
Debug.Print varItm, ctl.ItemData(varItm)
Debug.Print Filter


DoCmd.OpenForm "frm_client", acNormal, , Filter

 

[dhookom]

Data Type mismatch suggests ClientID is numeric and you are treating it as a string. You also shouldn't need to loop through the ItemsSelected since I expect this is not a multi-select listbox. Just use the default property.

My standard code for this would be (after changing the name from "list45" to "lboClientID" to be more explicit):

    Dim strWhere As String
    strWhere = "[ClientID]= " & me.lboClientID
    Debug.Print strWhere 
    DoCmd.OpenForm "frm_client", acNormal, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[shaunacol]

dhookam that code works perfectly! I think I was trying to over complicate it! many thanks