What is wrong with this Form Control Dlookup command using string method? (1 Viewer)

[JGravesNBS]

What is wrong with this Form Control Dlookup command using string method?

=DLookup("[Description]", "[dues transaction codes master]", "[Tran Code] = '" & Forms]![frmMaster Dues Payments_WDOE_Sub_Form]![Tran Code] & "'")

Error Message:
The expression you entered contains invalid syntax
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks

' Strings
variable = DLookup("[FieldName]", "TableName", "[Criteria] = '" & Forms!FormName!ControlName & "'")

 

[sneuberg]

I think you are missing a brace ([) before Forms

Try

=DLookup("[Description]", "[dues transaction codes master]", "[Tran Code] = '" & [Forms]![frmMaster Dues Payments_WDOE_Sub_Form]![Tran Code] & "'")

 

[pbaldy]

And if that's a subform like the name implies, the reference to it is incorrect:

http://access.mvps.org/access/forms/frm0031.htm

 

[JGravesNBS]

Thanks for the bracket help and the Reference Chart

I'm referring to a control on Sub 1 and I am on Sub 1 using Me!ControlName

=DLookUp("[Description]","[dues transaction codes master]","[Trans Code] = '" & [Me]![Trans Code] & "'")

The above DLookup is returning #Name?

 

[sneuberg]

This is just a shot in the dark but if [Trans Code] in your table is numeric then you'd want

=DLookUp("[Description]","[dues transaction codes master]","[Trans Code] = " & [Me]![Trans Code] )

i.e., no single quotes

 

[pbaldy]

You can only use "Me" in VBA; it looks like that's a control source, where you can't use it. You'll need the full reference there.

 

[sneuberg]

If he can type Me. and intelisense shows Trans Code then it's ok. Is this not true?

 

[pbaldy]

If he can type Me. and intelisense shows Trans Code then it's ok. Is this not true?

Yes; in VBA. I don't see where that's been established though. With the = in front, it appears to be a control source, and I'm pretty certain Me won't work outside VBA.

By the way, good catch on the brackets. I looked at this question before you answered and didn't catch that.

 

[JGravesNBS]

This is a Control Source

This finally got it:

=DLookUp("[Description]","[dues transaction codes master]","[Trans Code] = " & [Forms]![frmMaster Dues Payments_WDOE_Sub_Form]![Trans Code])

Thanks for your help, I've been bitten by DLookup before

 

[Galaxiom]

And if that's a subform like the name implies, the reference to it is incorrect:

http://access.mvps.org/access/forms/frm0031.htm

That page omits a perfectly valid way to refer to controls on subforms as was used by the OP. Its obscurity is mainly due to it not being included on that page which pretty much every developer comes across early in their career and never looks further.

The more familiar reference:

Forms!formname!subformcontrolname.Form!controlname

can also be expressed, bypassing the Form property of the subformcontrol, as:

Forms!formname!subformcontrolname!controlname

While the former syntax works with dots or bangs, the latter (without Form property) requires that the bang be used before controlname. This works because the bang includes the default collection and will drill to find it. The default collection of a form is Controls.

The dot doesn't work without the .Form because the Controls Collection of the subformcontrol's SourceObject is not mapped as a pseusdo-property of the subformcontrol.

 

[pbaldy]

True, but unless I've gone blind there is only one form object specified; the one with sub_form in its name. If it is truly being used as a subform, that reference shouldn't work.

 

[Galaxiom]

True, but unless I've gone blind there is only one form object specified; the one with sub_form in its name. If it is truly being used as a subform, that reference shouldn't work.

You are right Paul.

I looked at this:

 Forms]![frmMaster Dues Payments_WDOE_Sub_Form]![Tran Code]

and saw:

[Forms]![frmMaster].[Dues Payments_WDOE_Sub_Form]![Tran Code]

because I only looked at the beginning and end of the name, not expecting it to be quite so wrong.

It is certainly long enough and with sufficient prefixes and suffixes to be a full subform reference.

 

[pbaldy]

Didn't help that the inadvisable spaces made it break to a second line when posted here