automatic lookup fails

[YvesX54]

Hello,

I made a form (Access2000) that is bound to a query and put a combobox on it where the user can select a certain (existing) projectnumber from the list. The initial projectdata (e.g. projectnumber, client, startdate) is allready stored in a table and these fields are made available in the query bound to the form.

So when a certain projectID (= Autonumber in the related table) is chosen from the combobox, the other 'known' fields should be looked up and filled in on the form instantaneously, but they are not.

I made similiar combobox structures to lookup data and fill in other fields on the form, and these are working correctly.
e.g. PersonId chosen from a combo (data retrieved from the 'Personnel' table) results in adding ManagerId and ManagerAbbreviation.

What is the trigger that access uses to perform the actual lookup and how can I correct the 'lookup failure' error ? I checked my relationships and these seem to be ok.

Regards,
Yves

 

[Elana]

Are you using an autolookup query as the source of your form, or are you using code behind the combobox?

If you use the combobox wizard on your form, and your form's record source is not blank, the wizard will give you an option to "find a record on my form based on the value selected in the combo box." It then dumps code behind the After Update event of the combo box that looks something like this:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MemberID] = " & Str(Me![cboMemberName])
Me.Bookmark = rs.Bookmark

You need to replace the words MemberID and cboMemberName with the name of your Indexed field in recordset behind your form, and the actual name of your combobox.

HTH