Dlookup - I think

[RitaG]

I have two tables ClientNames and Sample. ClientNames has two fields called ClientID and FirstName that I want to reference on the Sample form I am creating.

On the Sample form I have a combo list of client IDs based on the ClientName table. When I enter an ID in the Sample form's ClientID field I want the corresponding name from the ClientNames table to automatically be inputed into a field called FirstName so on the Sample form. The only control I am using is on the field FirstName in the Properties Control Source. I am using the following:

=DLookUp("[FirstName]","ClientNames"," & [ClientID] = '" & [ClientID] & "'")

The criteria is text. When I use this all I get in the FirstName field is #Error.

Is part of the problem having both Tables using the same field names?

Anyone have any suggestions??? Is the way I'm going the best way? I am a real newbie to Access so please keep that in mind.

Thanks to anyone that can help.

Rita

 

[Jack Cowley]

Rita -

In the After Update event of your Combo box select [Event Procedure] from the little drop-down arrow to the right of the field. Then click the button to the right (it has three dots on it) to take you to the code page. In between the Private Sub... and End Sub lines put a line of code similar to this:

Me![FirstName] = Me![ComboBoxName].Column(1)

I have assumed that your combo box has the ClientID in the first column and the corresponding name in the second column. Combo boxes are Zero base so the first column (ClientID) is actually column(0) and that is why the code above refers to column 1. And replace 'ComboBoxName' with the actual name of your Combo box.

HTH,
Jack

 

[Talismanic]

As far as the Dlook up goes, there shouldn't be a problem with using the same names but I think you do have to add ME to the second client ID

=DLookUp("[FirstName]","ClientNames"," & [ClientID] = '" & Me![ClientID] & "'")

I can't check it from where I am at now but I will get the proper syntax and post it back here tomorrow.

 

[karatelung]

I tend to use DLookup a lot for "snapshots" of records quite often, and this always works for me.

Here is my actual code for the "AfterUpdate" event of the combo box. My combo box lists case managers' names. The "Department" field is automatically updated after I select a name in the combo box. "tblCaseManagers" is the lookup table. "tblPlacement" is the underlying table for my form.

Me.Department = DLookup("Department", "[tblCaseManagers]", "[tblCaseManagers].[casemanager] = [tblPlacement].[casemanager]")

I hope this helps.

Richie

 

[AccessUser]

Try renaming your text control. If you have modified the control source (used =), Access gets confused if the text control's name is the same as a reference in the control source. You said the text control is named FirstName and you have a reference to FirstName in the control source.