DLookup Syntax Question

[Matt]

In a subform in datasheet view, I want a text box to return a value based
upon a previous selection in a combo box. I am getting a invalid syntax
error with the following information in the control source property of the
text box

=DLookup("WorkCode",[Work Codes]",[WorkCodeID]=" Forms![Time Cards
Subform]!WorkcodeID)

where the field, WorkCode, is the desired result from the table, Work Codes,
with the criteria being that the primary key (WorkCodeID) of the field
"WorkCode" is the same as that selected in the current subform.

It seems like a straightforward issue, but am getting a bit lost in syntax.
Hope the issue is understandable, and thank you for any likely solutions.

 

[Tom van Stiphout]

On Sun, 28 Dec 2008 21:00:01 -0800, Matt

DLookup takes 3 string arguments. You have yours messed up.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

=DLookup("WorkCode",[Work Codes]",[WorkCodeID]=" Forms![Time Cards
Subform]!WorkcodeID)

where the field, WorkCode, is the desired result from the table, Work Codes,
with the criteria being that the primary key (WorkCodeID) of the field
"WorkCode" is the same as that selected in the current subform.

You just need an & character to concatenate two strings, and you have two
missing quotemarks. Each of the three arguments needs to be a text string -
either a string constant enclosed in " marks; or a value which evaluates to a
string; or an expression which evaluaates to a string. The first argument
needs to be the field you're searching for:

"[WorkCode]"

The second argument needs to be the table you're searching:

"[Work Codes]"

The third argument needs to be a valid SQL WHERE clause searching that table;
in this case it needs to be built up by concatenating two pieces, a fieldname
and a value from a form control:

"[WorkCodeID] =" as a string constant, and

Forms![Time Cards Subform]!WorkcodeID

as a value. If, as I suspect, there is a mainform with a subform, then the
syntax is a bit different - you need to reference the name of the *main* form,
and the Subform control on that form:

Forms![MainFormName]![Time Cards Subform].Form!WorkcodeID

So the DLookup should be

=DLookup("WorkCode", "[Work Codes]", "[WorkCodeID]=" &
Forms![MainFormName]![Time Cards Subform].Form!WorkcodeID)

 

[Matt]

Thank you for the help. The solution worked perfectly.

=DLookup("WorkCode",[Work Codes]",[WorkCodeID]=" Forms![Time Cards
Subform]!WorkcodeID)

where the field, WorkCode, is the desired result from the table, Work Codes,
with the criteria being that the primary key (WorkCodeID) of the field
"WorkCode" is the same as that selected in the current subform.

You just need an & character to concatenate two strings, and you have two
missing quotemarks. Each of the three arguments needs to be a text string -
either a string constant enclosed in " marks; or a value which evaluates to a
string; or an expression which evaluaates to a string. The first argument
needs to be the field you're searching for:

"[WorkCode]"

The second argument needs to be the table you're searching:

"[Work Codes]"

The third argument needs to be a valid SQL WHERE clause searching that table;
in this case it needs to be built up by concatenating two pieces, a fieldname
and a value from a form control:

"[WorkCodeID] =" as a string constant, and

Forms![Time Cards Subform]!WorkcodeID

as a value. If, as I suspect, there is a mainform with a subform, then the
syntax is a bit different - you need to reference the name of the *main* form,
and the Subform control on that form:

Forms![MainFormName]![Time Cards Subform].Form!WorkcodeID

So the DLookup should be

=DLookup("WorkCode", "[Work Codes]", "[WorkCodeID]=" &
Forms![MainFormName]![Time Cards Subform].Form!WorkcodeID)