Dlookup returning #Name?

[bd528]

Hi all,

i am trying to set a dlookup on a query to a text box on a report

In the On Load event of the report, I have tried the following code :-

Me.Text0.ControlSource = DLookup("Count", "qryReportQuotesByStatus", "Quote_Status = 'Accepted'")

and

Me.Text0.ControlSource = DLookup("[Count]", "[qryReportQuotesByStatus]", "[Quote_Status]='Accepted'")

both of which return #Name?

Can anyone suggest what the issue may be. Count is a number.

Thanks!

 

[Minty]

Count is a reserved word in access - try calling it something else.
Or use DCount("*", "qryReportQuotesByStatus", "[Quote_Status] = 'Accepted'")

 

[JHB]

And "Count" is a field name in the query?
Count is a reserved word in MS-Access and shouldn't be used as a field name!

 

[bd528]

Count is a reserved word in access - try calling it something else.
Or use DCount("*", "qryReportQuotesByStatus", "[Quote_Status] = 'Accepted'")

Hi, Thanks for your suggestions.

I've now tried the following :-

Me.Text6.ControlSource = DCount("*", "qryReportQuotesByStatus", "[Quote_Status] = 'Accepted'")

Me.Text0.ControlSource = DLookup("xCount", "qryReportQuotesByStatus", "Quote_Status = 'Accepted'")

Me.Text4.ControlSource = DLookup("[xCount]", "[qryReportQuotesByStatus]", "[Quote_Status]='Accepted'")

and unfortunately, each one is still giving me #Name?

For reference, the SQL of my query is

SELECT tblQuotes.Quote_Status, Count(tblQuotes.Quote_Status) AS xCount
FROM tblQuotes
GROUP BY tblQuotes.Quote_Status;

 

[Minty]

If the query is only doing the count then miss out the middle man. And setting the control source won't update the value of the control. Try this which will also prove it's working;

Dim iCount as long

iCount = DCount("QuoteStatus","tblQuotes","[QuoteStatus] = 'Accepted'")
Debug.print iCount
Me.Text6 = iCount

 

[JHB]

Sorry you can't have that as a controlsource - it is giving you a value so:

Me.Text0 = DLookup("xCount", "qryReportQuotesByStatus", "Quote_Status = 'Accepted'")

 

[bd528]

JHB and Minty - I tried both your suggestions, and gave the following errors

JHB
The expression you entered as a query parameter produced this error: 'xCount'

Minty
The expression you entered as a query parameter produced this error: 'QuoteStatus'

 

[JHB]

Post your database with some sample data.

 

[Minty]

JHB and Minty - I tried both your suggestions, and gave the following errors

JHB
The expression you entered as a query parameter produced this error: 'xCount'

Minty
The expression you entered as a query parameter produced this error: 'QuoteStatus'

Typo - which you should have spotted

Dim iCount as long

iCount = DCount("Quote[COLOR="Red"]_[/COLOR]Status","tblQuotes","[Quote[COLOR="Red"]_[/COLOR]Status] = 'Accepted'")
Debug.print iCount
Me.Text6 = iCount

 

[bd528]

Typo - which you should have spotted

I did actually spot that while I was awaiting your reply and amended the code.

Must be close now because when I msgbox iCount I see the value I want. But,
on

Me.Text6 = iCount

I get :-

"You can't assign a value to this object"

Me.Text6 is showing as Unbound

 

[Minty]

Are you sure it has nothing in it's control source?
As a simple test simply create a new unbound and change the code to set the value of that instead?

 

[bd528]

Are you sure it has nothing in it's control source?
As a simple test simply create a new unbound and change the code to set the value of that instead?

In design view text6 had the word Unbound in it. I created another text box (text5) also has Unbound showing in design view - same problem. However, when I create a label and set iCount to it's caption, the value displays fine.
This is okay temporarily, but I would prefer a text box if possible as it's easier for the user to copy and paste from.

 

[Minty]

That's very weird. This is OnLoad event yes ? Try a compact and repair. If that doesn't work do you fancy uploading the database? - strip out any sensitive info.

 

[bd528]

That's very weird. This is OnLoad event yes ? Try a compact and repair. If that doesn't work do you fancy uploading the database? - strip out any sensitive info.

Yes - OnLoad

Compact and repair didn't help.

My tables are SharePoint lists. Obviously, I'll have to remove these, meaning the query won't work. Would it still be worth uploading?

 

[Minty]

If you copy a limited dataset of the Sharepoint lists into local tables in another copy of your db you would have a simple off-line test environment - could be useful for development anyway?

 

[bd528]

If you copy a limited dataset of the Sharepoint lists into local tables in another copy of your db you would have a simple off-line test environment - could be useful for development anyway?

Okay - copy attached. Thanks!!

 

[Minty]

Ah - it's a report. I had assumed this was a form. You can't set a unbound control value via vba on a report (I don't think so anyway).

I've re-written your report - have a look and you may see a better approach.

 

[bd528]

Ah - it's a report. I had assumed this was a form. You can't set a unbound control value via vba on a report (I don't think so anyway).

I've re-written your report - have a look and you may see a better approach.

I have around 10 queries checking for different issues. In order to give an overview of the issues, I had one big form, which had 10 subforms relating to each of the queries. I thought I could tidy this up by pulling the values from the queries into text boxes (or labels) into one report.

Whilst your rewrite looks great, as far as I can tell, it's "locking" the report to just one query, which stops me having my "overview" on one page. Guess I need to decide which approach to take. I have learnt that you can't set a unbound control value via vba on a report though. Thanks a lot for your help (again) Minty.

 

[Minty]

You may be able to do that with a report based on a union query - they are a little interesting to write, you can't use the query designer you have to write them in the SQL view.

 

[bd528]

You may be able to do that with a report based on a union query - they are a little interesting to write, you can't use the query designer you have to write them in the SQL view.

Yes, I'm using a union for another report. With this one though, the various queries have differing numbers of columns, which makes a union a bit of a pain. Something I hadn't though of, which I can consider though! Have another Thanks