DLookup in VBA

[Badeye]

Hi Guys,

I have a user form with 2 textboxes. the first is to enter a 5 digit site code and the second is for the sitename. What i am trying to do is in the afterupdate of the first textbox in VBA is do a DLookup to pull through the site name from the sites table and populate the second textbox.

My textboxes are named
1st textbox = nscode
2nd textbox = nsitename

my DLookup looks like this.

Me.nsitename.Value = DLookup("[Site Name]", "Sites", [SiteCode] = Me.nscode)

Now it appears to kind of work but its not looking at the criteria and its just pulling through a random site name rather that the one i want.

Any help would be greatly appreciated.

Si

 

[pr2-eugin]

Even the criteria should be placed inside doubl quotes.. so it should be something like..

Me.nsitename.Value = DLookup("[Site Name]", "Sites", "[SiteCode] = '" & Me.nscode & "'")

Use the single qoues only if it a string.. else it should be something like this,

Me.nsitename.Value = DLookup("[Site Name]", "Sites", "[SiteCode] = " & Me.nscode & "")

 

[Badeye]

Thanks so much, thats works a treat. I get a little confused with the criteria and the quotes.

Si

 

[pr2-eugin]

You are welcome..

 

[Pat Hartman]

Since this is connected to a combo, just include the extra column in the combo's RowSource. Then in the AfterUpdate event of the combo use:
Me.nsitename = Me.cboSite.Column(2)
Adjust the column number as necessary. Remember this is a zero-based array I suggest this because efficiency has been ingrained in my psyche. I don't go out of my way to look for more efficient ways but I know from years of experience that one read is better than two so take advantage of the first read to get the additional piece of data rather than doing a second read with the DLookup(). It's a very small thing but ultimally, they add up.

Neither method will show the value for existing rows as you scroll through them so I usually use the no-code method if I can join to the lookup table in my RecordSource query. Then I can actually bind the display field to the form's RecordSource. Of course, for safety reasons, you may want to lock display only controls so that they are not accidentally updated.

If you stick with the DLookup() or Column() reference, you need to call this code from the Form's Current event to give the field a value when the record gets focus.

And finally, if you are working with a continuous form, go with the modified RecordSource so you can actually bind the lookup field. You'll see why.

 

[Badeye]

Cheers Guys,

 

[Bigmo2u]

Awesome, you guys helped me with this issue as well. Search featured helped as intended!