Type Mismatch problem

[dgmdvm]

I am using a simple form (form1) with a single combo box (CmboSearch) in it to perform a search of client names. When the user clicks on the correct name from the combo box, a new form (form2) should open with that client's data populating the fields. My code correctly brings up all the names in the combo box but when I click on the name I want, I get a Type mismatch error. When I click on ok in the error msgbox, form2 does open and it has the proper data in its fields. I have tried all sorts of variations in the code but I have not been able to get the error to go away. The combo box is based on a query of 2 tables, tlbClients, and tblpersons. The combo box row consists of [CLid] the primary key for the tblclients, [CL_last_name] from tblclients, [Cl_firstName] from tblPersons, and [primaryContact] from tblpersons.

here is the code:

Option Compare DatabaseOption Explicit


Private Sub CmboSearch_AfterUpdate()


    ' Find the record that matches the control.
    Dim rs As Object
On Error GoTo Err_CmboSearch_AfterUpdate


    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ClID] = " & str(Nz(Me![Cl_LastName], 0))
   
    
    If Not rs.EOF Then
      Me.Bookmark = rs.Bookmark
    
    
    End If
 
    Me.Refresh
    
    Set rs = Nothing
 
Exit_CmboSearch_AfterUpdate:
    Exit Sub


Err_CmboSearch_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_CmboSearch_AfterUpdate
        
End Sub




Private Sub CmboSearch_Click()
DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal
End Sub

What am I doing wrong?

[ssanfu]

The combo box row consists of [CLid] the primary key for the tblclients

Is "CLid" a number or Text???

[ssanfu]

After looking at your code, I see some errors on the CmboSearch_AfterUpdate() code.

Delete the sub "CmboSearch_Click()".
Then change the after update code to

Private Sub CmboSearch_AfterUpdate()
   DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal
End Sub

[dgmdvm]

ClID is an autonumber and the primary key (sorry I forgot to mention that)

So I dumped all the code in the after update sub and placed the open form statement in and it works fine. Now I am wondering why I had all that code in the after update field anyway? I have always used that code in forms to search for records. Doesn't seem to be necessary, at least not here.

Thanks for the help.

[June7]

It's useful if you want to 'go to' record on form the code is behind.