DLookup displaying #Name? when used in a form

[PCarra]

I am using a DLookup function to look up a value in a table and put that value in a text box. The Table BudgetTable has the following fields:

My DLookup code is placed in the control source of a text box and is : =DLookUp("[ActualMRC]","[BudgetTable]","[MonthNum]=12" And "[CircuitID]=" & [Forms]![subfrmBudgetInfo]![CircuitID])

This produces a value when in a subform but when that subform is used with a parent form the textbox displays #Name?


Any ideas?!?

[PCarra]

Jeez!!! figured it out I need to add the Mainform to the string =DLookUp("[ActualMRC]","[BudgetTable]","[MonthNum]=12" And "[CircuitID]=" & [Forms]![frmSearchCircuitInfoNew]![subfrmBudgetInfo]![CircuitID])

[PCarra]

Now that it displays data in the field it will not change when the record changes. Any ideas on how to get this to update?

[June7]

I am surprised the expression returns anything with that incorrect syntax. There are too many quote marks. Remove the red ones.

=DLookUp("[ActualMRC]","[BudgetTable]","[MonthNum]=12" And "[CircuitID]=" & [Forms]![frmSearchCircuitInfoNew]![subfrmBudgetInfo]![CircuitID])

If this expression is in textbox on frmSearchCircuitInfoNew, then the forms qualifier prefix should not be needed but shouldn't hurt either.

[PCarra]

I am surprised the expression returns anything with that incorrect syntax. There are too many quote marks. Remove the red ones.

=DLookUp("[ActualMRC]","[BudgetTable]","[MonthNum]=12" And "[CircuitID]=" & [Forms]![frmSearchCircuitInfoNew]![subfrmBudgetInfo]![CircuitID])

If this expression is in textbox on frmSearchCircuitInfoNew, then the forms qualifier prefix should not be needed but shouldn't hurt either.

Thank you for your help June7 after making the change suggested the field now shows #Error

[June7]

CircuitID is a number field? If it is text, need apostrophe delimiters for the parameter.

If you want to provide db for analysis, follow instructions at bottom of my post.

[PCarra]

CircuitID is a number field? If it is text, need apostrophe delimiters for the parameter.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks June7! I did need the apostrophe delimiters for that parameter. Do you have a resource that explains the use of ' and " that I could look at to gain a better understanding?

[June7]

No, I don't.

When used as special character (not just normal text in field as in O'Reilly or 1.0") they must be in pairs. And alternate their use. Use quote to define begin and end of a string. Then if you need to embed delimited parameters in that string, use apostrophes (or a doubled quote - which is called 'escaping' a special character - but I find that harder to read).

Text fields require apostrophe (or the doubled up quote) as delimiter for parameter.

Date fields require # character.

Number fields do not need delimiter.

Use of apostrophe and quote characters in data can cause issue with building SQL statements in code. So if a parameter needs to use 'O'Reilly' - the SQL will choke on that extra apostrophe. Use Replace function to double the apostrophe so it will be treated as a single normal text apostrophe, not a special character, if there is a chance data can have apostrophe.

CurrentDb.Execute "UPDATE tablename SET LastName='" & Replace(Me.tbxLName, "'", "''") & "' WHERE ID=" & Me.tbxID

Quote in data is even trickier. I have a database where I refuse to allow entries like found at depth 4.6" - not allowed to use quote character - spell out inches or convert to decimal feet. Somebody gets their hand slapped if they don't comply.

[PCarra]

Thank you for taking the time to explain it! I appreciate all your help.