Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

use variable in Docmd.openreport

Status
Not open for further replies.

pdtit

Technical User
Nov 4, 2001
206
BE
Hey,

I've been searching already quiet some time to do the following :

I want to open a somehow same report for 3 different locations. The location is chosen from a selection list on a form.

I have the following code behind my "show report"-button

Docmd.openreport "total sales & me!sellocation",...
where me!sellocation is the selection list on my form.
The issue I have is that everything between "..." is interpreted as text, and the variable is not read.
I also tried something like ""total sales " & "me!sellocation"", but then only "total sales " is read.

I was wondering if anyone knows if I can use variables at all using the docmd.openreport.

Regards,

Peter
 
Assuming that "Total Sales" is the name of the Report then it would look something like this

Docmd.openreport "total sales",acPreview,, "[SellLocation]= '" & Me!sellocation & "'"


Paul
 
pdtit:

Your command string should be:

Docmd.openreport "total sales" & me!sellocation.value,...

me!sellocation.value is a variable, by placing it inside the quotes (along with the concatenation operator &) you have created the string that you are seeing. Since you don't have a report named [total sales & me!sellocation], the command fails.

I always like to identify to the system that I want the value being held by the control and not the control itself. Code often works without the .value, but I've found it safer to use it.

Also, the concatenation operator, &, should never (well almost never) be placed inside of quotations marks.

You can test if the created report name is identical to the real report name by doing the following:

strName = "total sales" & me!sellocation
msgbox strName

If your location is Location101, then this contruct will produce: total salesLocation101.

Remember that the string you create must be identical in spelling, spacing and case, to the real name of the report.

Hope this helps,

Vic
 
Vic,

I tested it out and it works great.

Damn that stupid .value word costs me one hour of time.

Many thx for the info, and the speed in replying.

Regards,

Peter
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top