Can I use a DLOOKUP inside of a text box?

[Access_Novice]

I have a form with a field called 'parent' which takes a number from the 'parentID' field of the table called 'tblParents.' On this form, I have a field called 'Street' which is a text box. In this textbox, I want to use a DLOOKUP as a way to import the street address from 'tblParents'. The lookup value would be the field on the current form called 'parent.'



Here is the code as it appears inside of the textbox field, which is bound to a field called 'Street.' So I am saying, the value in this textbox named 'Street' should be equal to the value of the DLOOKUP. What is wrong with my code?


=[Street]=DLookUp("street1","tblParents","parentID=" & [parent])

[June7]

A DLookup() and other domain aggregate functions can be used in expression in textbox. However, populating a field with the value will require code (macro or VBA). So the syntax you show is not valid.

Why are you saving the street into another table? That is duplication of data. Just save the ParentID.

[Access_Novice]

Why are you saving the street into another table? That is duplication of data. Just save the ParentID.

I was just testing the use of a DLOOKUP. In reality, I wouldn't store data this way. I see what you are saying.

As for populating the DLOOKUP in the expression box, I did that, but I got a ?Name error. If you can use the expression builder, why would you also need VBA?

Also, the control source was a field called 'Street.' Now the control source uses the DLOOKUP function that I set up from expression builder. Even if this DLOOKUP didn't give me an error, would this textbox still be bound to the 'Street' field like it originally was?

[June7]

Any expression in a textbox results in an UNBOUND control. A BOUND control simply has a field name in its ControlSource. The expression builder won't prevent building invalid expressions.

Expression in textbox would be:

=DLookup("street1","tblParents","parentID=" & [parent])

The VBA code to save into field would be:

Me!street = DLookup("street1","tblParents","parentID=" & [parent])

or

Me!street = Me.tbxStreet

A macro would use the SetValue action to populate bound textbox with value retrieved by the DLookup()

[Access_Novice]

Me!street = Me.tbxStreet

Good information. Thank you. The only part I don't understand is the code above. Me!street refers to the textbox called 'street' on the current form. But what does Me.tbxStreet refer to? I noticed after the first Me you have an ! but after the 2nd Me you have a period.

[June7]

I always name controls that will be referenced in expressions, like: tbxSomething, cbxSomething, etc.

I always give bound controls a name different from the field to avoid circular reference errors.

So bound or not, the Street textbox would be tbxStreet.

Use naming convention you prefer.

The ! (bang) and dot are often interchangeable in VBA. The dot will provoke intellisense popup tips.