Name qualification in a Combo box

[wricoasolutions]

The code below works for an AfterUpdate combo box lookup that I am using currently. The query used by the current code uses only one copy of the table referenced by this lookup, the “Client” table.

Private Sub ClientNameLookUp_AfterUpdate()
Dim cnl As Object
Set cnl = Me.Recordset.Clone
cnl.FindFirst "[clisha_ClientID] = " & Str(Me![ClientNameLookUp])
Me.Bookmark = cnl.Bookmark
End Sub

My problem is that when I change the query to add a second copy of the “Client” table. I now have duplicate (2 copies) of the “Client” table named “Client” and “Client_1”. Access takes care of changing the table names so that they are not duplicated, but, I now have two “clisha_ClientID” which is causing a problem with the code above. I get an error indicating that there are duplicate names (clash_ClientID), which is understandable because the are two fields with the same name. I’m stuck because I do not know how to qualify the name so that it will look at the “Client_1” table.

I have tried to change the reference to [clisha_ClientID] by adding “Client_1_” to the name, but to no avail. I have tried other variations of this but again to no avail. I know that there is a way to qualify the specific name but, because of my lack of knowledge of Access VB, I am having trouble coming up with the right method.

Need some help.

Thanks, Bill

 

[Bat17]

"[Client].[clisha_ClientID] =" & Str(Me![ClientNameLookUp])

Though the last bit looks supect as well!
If clisha_ClientID is a number why wrap ClientNameLookUp in the Str() if it is text then you need to wrap the last bit in quotes.


"[Client].[clisha_ClientID] ='" & Str(Me![ClientNameLookUp]) &"'"

HTH

Peter