execute a query with parameter in a vba code

[D-Zyl]

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

[Allen Browne]

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

[Trevor Best]

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

[Allen Browne]

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