Text Box display record from table (1 Viewer)

[Javalon]

Hi All,

How can I have a Text Box control on a Form or Report display a record from a table? I've had trouble with this before and have had to create workarounds.

I want to be able to use the Control Source property, it seems like there shouldn't be any issue, yet I get a "#Name?" error. The workaround has been to write OnLoad/AfterUpdate form procedures to fill the box, which works, but I'd like to know once and for all why THIS doesn't work and what I should be doing.

Extra info: in the below example I am working on a Report. The table referenced contains one single record, in Short Text format. I used the 'Builder' to select the record, so no chance of typos.

Fig. 1 - Design view, showing the referenced table in properties

Fig. 2 - Report view, #Name? error

Massively appreciate any help!

 

[theDBguy]

Hi. Have you tried using the DLookup() function?

 

[Javalon]

Hi. Have you tried using the DLookup() function?

Hi, I just tried but I don't use the function very often; it didn't work. But, have I written it correctly?

The table name is "Reporting_Month"
The field name is "Month_Name"

I entered =DLookup([Month_Name],[Reporting_Month])

It returned the same #Name? error.

 

[jdraw]

Try
I entered =DLookup("Month_Name","Reporting_Month")

 

[Javalon]

Try
I entered =DLookup("Month_Name","Reporting_Month")

Huh, that worked!
Thanks!

Any idea why using what feels like the 'normal' way didn't work? My only guess is, although the table only contains one record, I was referencing the field as a whole. Which it didn't like. Other than that, no idea...

 

[jdraw]

Here's the related documentation and example from techonthenet

MS Access: DLookup Function

This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).

www.techonthenet.com

 

[theDBguy]

Huh, that worked!
Thanks!

Any idea why using what feels like the 'normal' way didn't work? My only guess is, although the table only contains one record, I was referencing the field as a whole. Which it didn't like. Other than that, no idea...

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

Huh, that worked!
Thanks!

Any idea why using what feels like the 'normal' way didn't work? My only guess is, although the table only contains one record, I was referencing the field as a whole. Which it didn't like. Other than that, no idea...

That is not the normal way though.

 

[Pat Hartman]

Domain functions need criteria. The only reason you are getting the "answer" is because there is only one row. A better solution is to refer to a control on the form that runs the report.

=Forms!yourformname!yourcontrolname

That way you don't need to update the table every month. You can default the value in the control if most of the time you are running the report for the "current" month or "prior" month but letting the user enter a date (year AND month) gives you a whole lot of flexibility.