Creating a Dlookup on a form

[Benwatson]

Hello everyone i am very new at using access. what I am trying to do is when i type in Someones User Id in this form i want 3 text boxes to auto populate with the users first name last name and shift from a table. if there is a easier way than dlookup please say so for one of the text boxes i have this function

=DLookUp([First Name],[Prime Users],"[Combo24]=" & [User ID])

it just come up with #name? and its really frustrating :banghead:

 

[Minty]

You have the syntax incorrect and criteria the wrong way around assuming UserId is in your Prime Users table. I would also strongly recommend removing the spaces from your table and field names, and avoid special characters.

For your DLookup to work it should read something like

=DLookup("[First Name]","[Prime Users],"[User ID] = " & Me.Combo24

Assuming User ID is a number and not text.

However if you pulled in the UserID, FirstName, LastName, and Shift into your combo box, you could update all the text boxes from the after update event of the combo box.

So assuming your First Name text box was called txtFirstName the code would be

Me.txtFirstName = Me.Combo24.Column(1)

Note that the column numbering for combo and list boxes starts at 0.

Also get into the habit of changing the default names - Combo24 won't mean anything to you in 2 months time if you come back to see how something works.

 

[Benwatson]

hello i tried both of what you suggested and i cant get them to work i must be doing something wrong

 

[Gasman]

This syntax works for me
Notice the quote positions

=DLookUp("[TransferDate]","tblTransfers","[SVSAccount]=" & [SVS_Account])

 

[Minty]

hello i tried both of what you suggested and i cant get them to work i must be doing something wrong

Copy and paste the code you have tried - don't edit it just a straight copy.

 

[Benwatson]

Copy and paste the code you have tried - don't edit it just a straight copy.

=DLookUp("[firstname]","[primeusers]","[userid] = " & [Me].[Combo36])

so took your advice took out the spaces out of the fields and used no special characters when trying out the combo box where it auto updates the other text boxes i had to delete the combo box so the combo box has changed

 

[Benwatson]

thanks minty i managed to sort it out through macros and it worked using your code Me.txtFirstName = Me.Combo24.Column(1) im thinking using macros is easier than using functions right now haha

 

[Minty]

Is this in the control source of your display text box for FirstName? If it is (and apologies if I mis-read your earlier post) the syntax for the DLlookup is slightly different on a form , as opposed to VBA code attached to a form.
I personally would still go the combo box route as it's more efficient, and you'll learn more .
Set your combo Box row source to your PrimeUser table, click on the builder button to create a query that brings in only the fields you need making the UserID the first column, followed by FirstName, LastName and Shift. You can sort by first names ascending to make selection easier. Make sure you change the column count to 4.

Then after your combo box is displaying the correct information open it's Event properties and click the builder button on the After Update property - select Event Property.

You will get the VBA window open up. Put the following code in;

Private Sub Combo36_AfterUpdate()

  Me.[COLOR="Red"]YourFirstNameControl [/COLOR]= Me.Combo36.Column(1)
  Me.[COLOR="red"]YourLastNameControl [/COLOR]= Me.Combo36.Column(2)
  Me.[COLOR="red"]yourShiftControlName [/COLOR]= Me.Me.Combo36.Column(3)
 
 [COLOR="Green"] 'For added bonus points you can add the two strings together with a space and create a new text box call txtFirstLast and do this;[/COLOR]
  Me.txtFirstLast = Me.Combo36.Column(1) & " " & Me.Combo36.Column(2)
  

End Sub

Change the names in red to your control names (not the field names unless they are the same).
You have just written your first bit of VBA - pretty simple and clear hopefully?

 

[Benwatson]

cheers minty been a huge help