By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

execute a query with parameter in a vba code

P: n/a
I wanna execute a query with one parameter from MS VBA.
the trouble is that the parameter is a control in a form.
and I still don't know what is the code I need...
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Simplest way it to just create the query string on the fly and execute it:

strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!MyTextBox & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError

Alternatively, you can supply the Parameter for the QueryDef, and execute
that:

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("Forms!MyForm!MyTextBox") = Forms!MyForm!MyTextBox
....

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"D-Zyl" <d_*****@hotmail.com> wrote in message
news:ef**************************@posting.google.c om...
I wanna execute a query with one parameter from MS VBA.
the trouble is that the parameter is a control in a form.
and I still don't know what is the code I need...

Nov 12 '05 #2

P: n/a
Allen Browne wrote:
Simplest way it to just create the query string on the fly and execute it:

strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!MyTextBox & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError

Alternatively, you can supply the Parameter for the QueryDef, and execute
that:

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("Forms!MyForm!MyTextBox") = Forms!MyForm!MyTextBox
...

If all your parameters are form controls, then you can:

For Each prm In qdf.Paramters
prm.value = eval(prm.name)
Next

I use that in a generic procedure, my latest domain function
replacements
(http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) use this
technique.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #3

P: n/a
Good trick, Trevor.

You would need to check that the control was not Null first, as Eval() does
not handle nulls.

As Trevor would know, it is also a *very* good idea to declare these
parameters. In query design, choose Parameters on the Query menu, and enter
the name and data type. Something like:
Forms!MyForm!MyTextBox Long

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Trevor Best" <nospam@localhost> wrote in message
news:40**********************@auth.uk.news.easynet .net...
Allen Browne wrote:
Simplest way it to just create the query string on the fly and execute it:
strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!MyTextBox & ";"
dbEngine(0)(0).Execute strSQL, dbFailOnError

Alternatively, you can supply the Parameter for the QueryDef, and execute that:

Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("MyQuery")
qdf.Parameters("Forms!MyForm!MyTextBox") = Forms!MyForm!MyTextBox
...

If all your parameters are form controls, then you can:

For Each prm In qdf.Paramters
prm.value = eval(prm.name)
Next

I use that in a generic procedure, my latest domain function
replacements
(http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) use this
technique.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.