Text Box display record from table (1 Viewer)

Javalon

New member
Local time
Today, 06:57
Joined
Nov 10, 2021
Messages
16
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
1648052575807.png



Fig. 2 - Report view, #Name? error
1648052750572.png



Massively appreciate any help!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:57
Joined
Oct 29, 2018
Messages
18,987
Hi. Have you tried using the DLookup() function?
 

Javalon

New member
Local time
Today, 06:57
Joined
Nov 10, 2021
Messages
16
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])

1648053456014.png


It returned the same #Name? error.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:57
Joined
Jan 23, 2006
Messages
14,318
Try
I entered =DLookup("Month_Name","Reporting_Month")
 

Javalon

New member
Local time
Today, 06:57
Joined
Nov 10, 2021
Messages
16
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

Super Moderator
Staff member
Local time
Today, 01:57
Joined
Jan 23, 2006
Messages
14,318
Here's the related documentation and example from techonthenet
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:57
Joined
Oct 29, 2018
Messages
18,987
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

Enthusiastic Amateur
Local time
Today, 06:57
Joined
Sep 21, 2011
Messages
10,555
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

Super Moderator
Staff member
Local time
Today, 01:57
Joined
Feb 19, 2002
Messages
36,327
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.
 

Users who are viewing this thread

Top Bottom