Extract information from MsgBox

[DanKay1]

When running a query in Access it brings up this message box "You are about to update 50 records." is it possible to obtain that 50 and assign it to the variable. I am trying to run several queries in function and I want to turn off the warnings and make a report of all queries.

 

[RoyVidar]

Don't think so.

One way of obtaining it, would be to use ADO to execute the queries, here first concatenating a string:

[tt]dim lngRec as long
dim strSql as string
strSql="update sometable set somefield = somevalue"
currentproject.connection.execute strsql,,lngRec
msgbox lngRec[/tt]

If you're using stored queries, this approach should work, if the queries doesn't contain parameters from forms:

[tt]dim cmd as adodb.command
dim lngRec as long
set cmd=new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query")=True
.commandtext="NameOfTheQuery"
.execute lngRec
end with
msgbox lngRec[/tt]

Should it contain parameters from forms, something like this might perhaps be used (Note - version 2002+):

[tt]dim cmd as adodb.command
dim prm as adodb.parameter
dim lngRec as long
set cmd=new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query")=True
.commandtext="NameOfTheQuery"
for each prm in .parameters
prm.value=eval(prm.name)
next prm
.execute lngRec
end with
msgbox lngRec[/tt]

For Access 2000, see thread709-819033, just replace the

[tt]set rs=cmd.execute ' with
cmd.execute lngRec [/tt]

Roy-Vidar

 

[RickSpr]

If you execute an action query with DAO Database.Execute or QueryDef.Execute, the DAO object's RecordsAffected property will give you the number.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein