Passing Variable from Form to Query using Click on Label Caption (1 Viewer)

[Senior System Engineer]

Hello,

I have a Form (Page_Scan_Index) with many Labels (ex. Label1, Label2, etc.).

I have a Query (Stamps_on_Page) with 2 fields (Front_Page_Scan_Name and Scott_Number)

I want to CLICK on a Label in the Form and Pass the Label Caption to the Query as a variable.

I created this code:

Private Sub Label1_Click()
Dim pagename As String
pagename = [Forms]![Page_Scan_Index]![Label19].[Caption] + ".jpg"
DoCmd.OpenQuery ("Stamps_on_Page")
End Sub

So, the variable I’m trying to pass to the Query is “pagename”.

pagename is populating perfectly but the query never gets the string.

Within the Query, I am setting the Variable name as the criteria for Front_Page_Scan_Name like this:

[pagename]

I’m assuming I don’t know the correct syntax to pass a variable to the query?

Can someone please help?

Thanks,
Scott

 

[theDBguy]

Hi. Welcome to AWF!

Just wondering why you're trying to open the query? Is it an action query? If it's a select query, why not use a form to view the result?

 

[Senior System Engineer]

Hello theDBguy,

Nice to be here...Thanks.
I'm a "Hobby" coder and use pretty basic layouts.
I am wide open to any suggestions you have.
The key here is that there are actually about 100 Labels...
Clicking on each one will result in a different query result.
This way seemed like it would be easy if I could just pass the variable "pagename" to the query.
I'm so close...Query just won't accept the variable.

Thanks,
Scott

 

[theDBguy]

Hello theDBguy,

Nice to be here...Thanks.
I'm a "Hobby" coder and use pretty basic layouts.
I am wide open to any suggestions you have.
The key here is that there are actually about 100 Labels...
Clicking on each one will result in a different query result.
This way seemed like it would be easy if I could just pass the variable "pagename" to the query.
I'm so close...Query just won't accept the variable.

Thanks,
Scott

Just for clarification, can you please post the SQL statement of the query in question? Thanks.

 

[Senior System Engineer]

SELECT Stamp_Collection.Scott_Number, Stamp_Collection.Front_Page_Scan_Name
FROM Stamp_Collection
WHERE (((Stamp_Collection.Front_Page_Scan_Name)=[pagename]))
ORDER BY Stamp_Collection.Sequence_Number;

 

[CJ_London]

use a tempvar - something like

tempvars!pagename=[Forms]![Page_Scan_Index]![Label19].[Caption] + ".jpg"

and in your query

WHERE (((Stamp_Collection.Front_Page_Scan_Name)=tempvars!pagename))

 

[theDBguy]

SELECT Stamp_Collection.Scott_Number, Stamp_Collection.Front_Page_Scan_Name
FROM Stamp_Collection
WHERE (((Stamp_Collection.Front_Page_Scan_Name)=[pagename]))
ORDER BY Stamp_Collection.Sequence_Number;

Okay, that is a select query. Which brings up my other question, why open it and not just use a form to view it? Just curious...

 

[Senior System Engineer]

First off...It worked!
You are magnificent!
Thank You very much.
I never knew there were tempvars...Looks like a new tool in my holster.

Second, To answer your question...I am probably going to do that next.
My simple mind needs to do things in baby steps...It helps me in debugging when things aren't too complicated.

Anyway, if I'm ever in your area, I'll wash your car for you!

Again, thank you very much...I would have never found the tempvar solution.

Scott

 

[MsAccessNL]

if you are going to use the pagename for your tempvars, you probaly can use:
Tempvars!pagename = Label19.Caption & ".jpg"
or just
..pagename= "xxxx.jpg"
or put the name in the tag property of the label:
...pagename = Label19.Tag
or replace the labels with buttons now you can use:
...pagename = screen.activecontrol.caption &. "jpg"
copy past it to all button events or make it a function
or you can keep the Label and put a transparent Button with transparent outline over the Label, now you also can set a mouse hover hand.....
Just a few tips...

 

[Senior System Engineer]

Hello MsAccessNL,
Thanks for the great list of tips/options...
I really like the transparent button idea to get the mouse hover functionality.
Have a nice evening,
Scott