DLookup Help

X

xyba

Registered User.

Local time

Today, 21:46

Joined

Jan 28, 2016

Messages

189

• Aug 28, 2019

• #1

Hi, I need some help with a DLookup expression in the AfterUpdate event of a form text box, please.

I have a query (qryLocate with two fields, Box and Row

I want to look up the value of text box txtBoxID in the field Box in qryLocate and return the value in Row.

I tried the below but it didn't work, probably because I've not used Dlookup much in Access.

Code:

=DLookUp("[Row]","[qryLocate]","[qryLocate]![Box] = '" & [Me].[txtBoxID] & "'")

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Tomorrow, 04:46

Joined

May 7, 2009

Messages

20,339

• Aug 28, 2019

• #2

you don't add quote if the Box is numeric:

=DLookUp("[Row]","[qryLocate]","[Box] = " & [Me].[txtBoxID] )

 

X

xyba

Registered User.

Local time

Today, 21:46

Joined

Jan 28, 2016

Messages

189

• Aug 28, 2019

• #3

arnelgp said:

you don't add quote if the Box is numeric:

=DLookUp("[Row]","[qryLocate]","[Box] = " & [Me].[txtBoxID] )

Click to expand...

Thanks arnel, though that throws up a #Name? error. I've checked the form and there are no controls named Row or Box and there is definitely one called txtBoxID. Could there be another reason for this error?

 

Minty

AWF VIP

Local time

Today, 21:46

Joined

Jul 26, 2013

Messages

10,666

• Aug 28, 2019

• #4

The Me syntax isn't correct

Code:

=DLookUp("Row","qryLocate","Box = " & Me.txtBoxID )

You only need the square brackets for field names with spaces or that are reserved words.

 

X

xyba

Registered User.

Local time

Today, 21:46

Joined

Jan 28, 2016

Messages

189

• Aug 28, 2019

• #5

Minty said:

The Me syntax isn't correct

Code:

=DLookUp("Row","qryLocate","Box = " & Me.txtBoxID )

You only need the square brackets for field names with spaces or that are reserved words.

Click to expand...

Thanks Minty. However, Access didn't seem to like that idea as it kept adding the square brackets each time I completed the expression.

It got me to thinking, though, and I solved it by removing the [Me].

Thanks.

 

Minty

AWF VIP

Local time

Today, 21:46

Joined

Jul 26, 2013

Messages

10,666

• Aug 28, 2019

• #6

Is this on a form? In that case it's correct, I thought this was in a VBA module where the Me. prefix refers to the current form object.

 

X

xyba

Registered User.

Local time

Today, 21:46

Joined

Jan 28, 2016

Messages

189

• Aug 28, 2019

• #7

Minty said:

Is this on a form? In that case it's correct, I thought this was in a VBA module where the Me. prefix refers to the current form object.

Click to expand...

Yes, in a form. Thanks for your help.