SQL with variable, no prompts... (1 Viewer)

Grandchester

Registered User.
Local time
Today, 03:31
Joined
Jan 26, 2001
Messages
20
Hi all. The best way to explain this is to show an example:

Me.RecordSource = "SELECT tblMain.ProductCode, tblMain.Name, tblMain.[NLGI Grade], tblMain.Color FROM tblMain WHERE (((tblMain.ProductCode)=" & varSearchData1 & "));"

The key to this is the "varSearchData1" variable. I have this set on my "Load Form" action, but it still prompts me for a parameter. I already established the value of "varSearchData1", but it still prompts me for it. Any ideas how to do this without getting promted? Thanks. Peace

Grandy
 

Opengrave

Registered User.
Local time
Yesterday, 21:31
Joined
Sep 6, 2001
Messages
70
I'm no expert but IIRC unless varSearchData1 is defined as a global variable or its value is set within the sub routine it is not available within the module. Have you gotten this to work by dummying varSearchData1 with something you know works? If it works that way you know it is a problem setting the variable. If it does not work you might have a syntax problem in the SQL - those nasty quotes always tend to give me trouble.
 

Grandchester

Registered User.
Local time
Today, 03:31
Joined
Jan 26, 2001
Messages
20
Continued

Thanks for the help Opengrave. varSearchData1 is defined as a Global Variable. Actually, when I run the sql (with the code I have presented), it prompts me for a parameter, however in the message box prompt, it gives the value of varSearchData1, instead of the variable name. What I need it to do is to run the sql with that value, instead of prompting me for it.
 

Opengrave

Registered User.
Local time
Yesterday, 21:31
Joined
Sep 6, 2001
Messages
70
I checked some of my old code using variables in SQL and it looked exactly like yours. I can only offer a few more suggestions. Is the global variable defined correctly (string for text or single or double for numneric)? If the var in varSearchData stands for variant this may be causing some of your trouble. Also try and play with the quotes, try:

"SELECT tblMain.ProductCode, tblMain.Name, tblMain.[NLGI Grade], tblMain.Color FROM tblMain WHERE (((tblMain.ProductCode)='" & varSearchData1 & "'));"
single quotes around the string

or

"SELECT tblMain.ProductCode, tblMain.Name, tblMain.[NLGI Grade], tblMain.Color FROM tblMain WHERE (((tblMain.ProductCode)=""" & varSearchData1 & """));"
triple quotes that are converted to single quotes
 

Grandchester

Registered User.
Local time
Today, 03:31
Joined
Jan 26, 2001
Messages
20
Alrighty Then

Thanks OpenGrave. It was the single quotes that did it. Appreciate the help!!

Shine On,

Grandy
 

Users who are viewing this thread

Top Bottom