Show result of query in form

[kentendresen]

Hi!

I've made a query which counts a defined type of records in the table, and it works all fine. But when I try to put this result in a form, I just get an error in the textfield: #name?

The SQL is something like this:

[Query]![My control name]

What is wrong?

 

[Jack Cowley]

If you have an unbound control on your form you can use DLookup() in the Control Source to get the results of the query. You cannot have the query put the results into a control on a form unless the query is the Record Source for the form.

hth,
Jack

 

[DreamGenius]

Similar problem

I'm encountering a similar problem to this one but the posted reply doesn't do it for me.

I've created a form which shows the Network Username and Computer Name using Environ variables into Unbound TextBoxes and these work fine.

Using the Network Username, I'd like to display the real person's name using a query to a linked table. In the Control Sources, I've put

=(select RTRIM (

![FNAME] ) + ' ' + RTRIM (

![LNAME] ) from

where

![EMPLOYEE] = [AssocID] )

but I get a #Name? result.

Answers phrased for a willing but clueless Access coder please. I know my way around Excel and SQL but have avoided Access until now!

Thanks in advance

 

[Rose412]

If EMPLOYEE is a text field:-

=DLookUp("RTrim([FNAME]) & ' ' & RTrim([LName])","

","[EMPLOYEE] ='" & [AssocID] & "'")


If EMPLOYEE is a numeric field:-

=DLookUp("RTrim([FNAME]) & ' ' & RTrim([LName])","

","[EMPLOYEE] =" & [AssocID])

 

[DreamGenius]

=DLookUp("RTrim([FNAME]) & ' ' & RTrim([LName])","

","[EMPLOYEE] =" & [AssocID])

That worked perfectly, thank you.

I hadn't previously known of DLookup but I understand the syntax of it and it does the job very simply.

Thanks again