VBA - Prompt from SQL Statement?

  • Thread starter Thread starter Jason Gyetko
  • Start date Start date
J

Jason Gyetko

How do I prompt the user from an SQL statement in VBA? It works if I put it
in an actual access query. Given the example below, I want an "Enter PO"
prompt to pop up so a PO# can be entered, can this be done or do I have to
pop up a form and pull the data from a text box on it? Thanks in advance.

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
"GROUP BY tblBase.ProdID, tblBase.MSDS;")
 
Jason,

When you have a question like this, create the actual query and then switch to
SQL view to see the SQL for the actual query. There you will see the answer to
your question.

How does that go .... Give a man a fish and .... but Teach a man to fish ...
 
That's what I tried to do and I get the following message:

Run-time error '2465':
Command Center can't find the field '|' referred to in your expression.

I guess all I really need to know is how you can specify a variable in your
code then when you want to set it's value, prompt for it. Example:

dim myVariable as String
myVariable = [Prompt for Variable Data Here]

When I use the brackets is when I get the above mentioned error. Is there
another character I have to use instead of the brackets?

PC Datasheet said:
Jason,

When you have a question like this, create the actual query and then switch to
SQL view to see the SQL for the actual query. There you will see the answer to
your question.

How does that go .... Give a man a fish and .... but Teach a man to fish ....


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Jason Gyetko said:
How do I prompt the user from an SQL statement in VBA? It works if I put it
in an actual access query. Given the example below, I want an "Enter PO"
prompt to pop up so a PO# can be entered, can this be done or do I have to
pop up a form and pull the data from a text box on it? Thanks in advance.

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
"GROUP BY tblBase.ProdID, tblBase.MSDS;")
Click to expand...
Click to expand...
 
Hi Jason,

You can get your user's input using the InputBox() function. Perhaps like
this:

Dim MyVar as String

MyVar = InputBox("Enter the PO Here:")

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE tblPickList.PIpo) = " & Chr(34) & MyVar & Chr(34) & _
" GROUP BY tblBase.ProdID, tblBase.MSDS;")

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Jason Gyetko said:
That's what I tried to do and I get the following message:

Run-time error '2465':
Command Center can't find the field '|' referred to in your expression.

I guess all I really need to know is how you can specify a variable in your
code then when you want to set it's value, prompt for it. Example:

dim myVariable as String
myVariable = [Prompt for Variable Data Here]

When I use the brackets is when I get the above mentioned error. Is there
another character I have to use instead of the brackets?

PC Datasheet said:
Jason,

When you have a question like this, create the actual query and then switch to
SQL view to see the SQL for the actual query. There you will see the answer to
your question.

How does that go .... Give a man a fish and .... but Teach a man to fish ...


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
have
to
pop up a form and pull the data from a text box on it? Thanks in advance.

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
"GROUP BY tblBase.ProdID, tblBase.MSDS;")
Click to expand...
Click to expand...
Click to expand...
 
Thanks, that's exactly what I was looking for.

Cheryl Fischer said:
Hi Jason,

You can get your user's input using the InputBox() function. Perhaps like
this:

Dim MyVar as String

MyVar = InputBox("Enter the PO Here:")

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE tblPickList.PIpo) = " & Chr(34) & MyVar & Chr(34) & _
" GROUP BY tblBase.ProdID, tblBase.MSDS;")

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Jason Gyetko said:
That's what I tried to do and I get the following message:

Run-time error '2465':
Command Center can't find the field '|' referred to in your expression.

I guess all I really need to know is how you can specify a variable in your
code then when you want to set it's value, prompt for it. Example:

dim myVariable as String
myVariable = [Prompt for Variable Data Here]

When I use the brackets is when I get the above mentioned error. Is there
another character I have to use instead of the brackets?

PC Datasheet said:
Jason,

When you have a question like this, create the actual query and then switch to
SQL view to see the SQL for the actual query. There you will see the answer to
your question.

How does that go .... Give a man a fish and .... but Teach a man to
Click to expand...
fish
...
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


How do I prompt the user from an SQL statement in VBA? It works if
Click to expand...
I
put it
in an actual access query. Given the example below, I want an
Click to expand...
"Enter
PO"
prompt to pop up so a PO# can be entered, can this be done or do I
Click to expand...
have
to
pop up a form and pull the data from a text box on it? Thanks in advance.

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
"GROUP BY tblBase.ProdID, tblBase.MSDS;")
Click to expand...
Click to expand...
Click to expand...
 
Jason said:
How do I prompt the user from an SQL statement in VBA? It works if I put it
in an actual access query. Given the example below, I want an "Enter PO"
prompt to pop up so a PO# can be entered, can this be done or do I have to
pop up a form and pull the data from a text box on it? Thanks in advance.

Set rs = db.OpenRecordset _
("SELECT tblBase.ProdID, tblBase.MSDS " & _
"FROM tblPickList INNER JOIN tblBase ON tblPickList.PIitem =
tblBase.ProdID " & _
"WHERE (((tblPickList.PIpo) = """ & [Enter PO] & """)) " & _
"GROUP BY tblBase.ProdID, tblBase.MSDS;")
Click to expand...


When you run a query in the VBA environment, you have to
handle the parameters/prompts yourself. If all you want is
a popup box to get the value as it would if you ran the
query from the query window or a form/ report RecordSource,
then just use the InputBox function.

WHERE (((tblPickList.PIpo)=""" & InputBox([Enter PO]) & ...

That's a little crude (in the sense of no validation), but I
think that's all you wanted.
 
Back
Top