Invoking parameter queries from VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to invoke a parameter query from VBA where variables are sent
as the parameters so it won't show the pop-ups asking for them?
 
There sure is and it can be done by using code, qrydefs, or even Recordsets.

1. Using DoCmd.RunSQL

(Note: Open your query in SQL view, copy it, and paste it at strSQL =
"________". You can use the line continueation characters " & _ as shown
below. If it doesn't work try removing the ; at the end of the SQL)

Here is an example from one of my databases:

Dim strSQL As String

strSQL = "SELECT * FROM tblSSAttendance WHERE [txtSSClassDate] = '" & _
SSClassDate & "';"

DoCmd.RunSQL strSQL


2. OR by using a Recordset:


Private Sub cmdNewMinSalary_Click()
Dim rstEmployees As ADODB.Recordset
Dim strSQL As String

Set conDatabase = CurrentProject.Connection
strSQL = "SELECT * FROM Employees WHERE Salary < " & txtNewMinSalary

Set rstEmployees = New ADODB.Recordset
rstEmployees.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

With rstEmployees
Do While Not .EOF
!Salary = txtNewMinSalary
.Update
.MoveNext
Loop
End With

MsgBox "The minimum salary of all employees has been set to " &
txtNewMinSalary

Me.txtNewMinSalary = Null 'My code

rstEmployees.Close
conDatabase.Close
Set rstEmployees = Nothing
Set conDatabase = Nothing
End Sub

(I would love to give credit for this sample which is from a website, but I
have forgotten which site it is.)


3. Or you can Google QueryDef and get instructions how to use that method.

Hunter57
Just huntin' for some data.
http://churchmanagementsoftware.googlepages.com
 
I use that method often and I want to take this opportunity to say thanks to
Roger Carlson for the service his website is providing. It is so much easier
to learn Access when I have an example from Roger's library.

Hunter57
 
Thank you all for your answers.

I guess I missed one point in my original post.
The parameter query is also an action query (Make-Table Query). I was able
to enter the parameter, but then I didn't know how to run it. Finally I was
able to get the SQL code out of the querydef and replace the parameter with a
variable. This works, but I still want to know how you can run the query
using the querydef object.

This is what I have:

Set Db = CurrentDb
Set qdf = Db.QueryDefs("qryMyQuery")
strSQL = Replace(Trim(qdf.Sql), "[Parameter?]", strVariable)
DoCmd.RunSQL strSQL
 
Change
DoCmd.RunSQL strSQL
to
Db.Execute strSQL, dbFailOnError

--
Joan Wild
Microsoft Access MVP
Orlando said:
Thank you all for your answers.

I guess I missed one point in my original post.
The parameter query is also an action query (Make-Table Query). I was able
to enter the parameter, but then I didn't know how to run it. Finally I was
able to get the SQL code out of the querydef and replace the parameter with a
variable. This works, but I still want to know how you can run the query
using the querydef object.

This is what I have:

Set Db = CurrentDb
Set qdf = Db.QueryDefs("qryMyQuery")
strSQL = Replace(Trim(qdf.Sql), "[Parameter?]", strVariable)
DoCmd.RunSQL strSQL
Click to expand...
 
Two ways: Direct Reference or Parameter Collection

Direct Reference:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf![Forms!form1!Text1] = [Forms]![form1]![Text1]
qdf.Execute "Query1"

Parameters Collection:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute "Query1"


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Orlando said:
Thank you all for your answers.

I guess I missed one point in my original post.
The parameter query is also an action query (Make-Table Query). I was able
to enter the parameter, but then I didn't know how to run it. Finally I
was
able to get the SQL code out of the querydef and replace the parameter
with a
variable. This works, but I still want to know how you can run the query
using the querydef object.

This is what I have:

Set Db = CurrentDb
Set qdf = Db.QueryDefs("qryMyQuery")
strSQL = Replace(Trim(qdf.Sql), "[Parameter?]", strVariable)
DoCmd.RunSQL strSQL
Click to expand...
 
Is there a way to invoke a parameter query from VBA where
variables are sent as the parameters so it won't show the pop-ups
asking for them?
Click to expand...

Why would you want to do this? Why not just use a SQL string with
appropriate WHERE clause? What do you think you're gaining by using
a stored parameter query?
 
you should be using Access Data Projects if you're at this point

it's much much much simpler


Docmd.RunSql "MySproc 1,12,'Aaron'"
 
Hunter

I'm not GRATEFUL that monkey turned into an Access crybaby

stfu and learn SQL Server, newbie
 
Don't use DAO you newbie retard



Thank you all for your answers.

I guess I missed one point in my original post.
The parameter query is also an action query (Make-Table Query). I was able
to enter the parameter, but then I didn't know how to run it. Finally I was
able to get the SQL code out of the querydef and replace the parameter with a
variable. This works, but I still want to know how you can run the query
using the querydef object.

This is what I have:

Set Db = CurrentDb
Set qdf = Db.QueryDefs("qryMyQuery")
strSQL = Replace(Trim(qdf.Sql), "[Parameter?]", strVariable)
DoCmd.RunSQL strSQL

--
Orlando



Joan Wild said:
Seehttp://www.mvps.org/access/queries/qry0013.htm
http://www.mvps.org/access/queries/qry0003.htm
Click to expand...

- Show quoted text -
Click to expand...
 
David;

if you knew how to spell SQL Server, you'd send him to Access Data
Projects

YES parameters are a total pain in the butt using MDB

that is why I use ADP; and that is why I've used ADP for the past
decade

and that is why I'll use ADP for the NEXT DECADE
 
seriuosly dipshit

you should think before you push people into this DAO crap

DAO hasn't been included with Office, MDAC or Windows for the past
decade.

THERE _IS_ a new version of ADO in Vista.
THERE IS NOT a new version of DAO in Vista


stfu, loser-- and promote Access Data Projects

DOcmd.Runsql "MySproc 12,13, 'Aaron'"

Please, diptard-- please explain to me how you're helping him become
MORE EFFICIENT when he has to write a dozen lines of code to do the
SAME THING?

ADP UBER ALLES BITCHES





Two ways: Direct Reference or Parameter Collection

Direct Reference:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
qdf![Forms!form1!Text1] = [Forms]![form1]![Text1]
qdf.Execute "Query1"

Parameters Collection:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute "Query1"

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




Thank you all for your answers.
Click to expand...
I guess I missed one point in my original post.
The parameter query is also an action query (Make-Table Query). I was able
to enter the parameter, but then I didn't know how to run it. Finally I
was
able to get the SQL code out of the querydef and replace the parameter
with a
variable. This works, but I still want to know how you can run the query
using the querydef object.
Click to expand...
This is what I have:
Click to expand...
Set Db = CurrentDb
Set qdf = Db.QueryDefs("qryMyQuery")
strSQL = Replace(Trim(qdf.Sql), "[Parameter?]", strVariable)
DoCmd.RunSQL strSQL
Seehttp://www.mvps.org/access/queries/qry0013.htm
http://www.mvps.org/access/queries/qry0003.htm
--
Joan Wild
Microsoft Access MVP
Is there a way to invoke a parameter query from VBA where variables are
sent
as the parameters so it won't show the pop-ups asking for them?
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
S u s i e D B A said:
you should be using Access Data Projects if you're at this point

it's much much much simpler


Docmd.RunSql "MySproc 1,12,'Aaron'"
Click to expand...

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
note that Tony is neither MOST, VALUABLE -OR- A PROFESSIONAL

he is a MDB cry baby


'oh, but it's too hard to use sql server'


rofl


your crack addict loser MDB development is obsolete, kid
 
Back
Top