Invoking parameter queries from VBA

[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?

 

[Roger Carlson]

If you run the query from a form and have the query reference controls on
the form, it won't give pop-ups asking for the parameters. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"TooFewParameters.mdb" which illustrates how to do this.

--
--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

 

[Guest]

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

 

[Joan Wild]

See http://www.mvps.org/access/queries/qry0013.htm
http://www.mvps.org/access/queries/qry0003.htm

 

[Guest]

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

 

[Roger Carlson]

Thanks, Hunter. It pleases me that my samples are of some use to you.

 

[Guest]

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

 

[Joan Wild]

Change
DoCmd.RunSQL strSQL
to
Db.Execute strSQL, dbFailOnError

--
Joan Wild
Microsoft Access MVP

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

 

[Roger Carlson]

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.

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

 

[David W. Fenton]

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?

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?

 

[Susie DBA [MSFT]]

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'"

 

[Susie DBA [MSFT]]

Hunter

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

stfu and learn SQL Server, newbie

 

[Susie DBA [MSFT]]

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

Seehttp://www.mvps.org/access/queries/qry0013.htm
http://www.mvps.org/access/queries/qry0003.htm

- Show quoted text -

 

[Susie DBA [MSFT]]

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

 

[Susie DBA [MSFT]]

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.

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

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?

- Show quoted text -

 

[Tony Toews [MVP]]

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'"

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

 

[Susie DBA [MSFT]]

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