Help with responding to questions posed by update query

[bweldon]

I have a series of update queries that I have to run on my data set. These queries set a series of different values. I have it set up for the queries to run one after another in VBA but I still am being asked if I want to update the data.

Here is the command to run the query

stDocName = "qry6mrfFlag"
DoCmd.OpenQuery stDocName, acNormal, acEdit

I know how to force a yes response in Excel but cannot seem to get it right in access.

Thanks

 

[thisisanemergency]

Not sure if I understand you correctly. Are you looking to turn off the warning message you get before something is updated/appended? If so you could go to Access Options > Client Settings > Confirm and untick the boxes.

 

[bweldon]

Not sure if I understand you correctly. Are you looking to turn off the warning message you get before something is updated/appended? If so you could go to Access Options > Client Settings > Confirm and untick the boxes.

Ok that worked, next question is that setting on the database or will other users need to set that also.

Thanks

 

[VilaRestal]

Just use

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry6mrfFlag"
DoCmd.SetWarnings True

Does what you were thinking of I expect.

 

[thisisanemergency]

Ok that worked, next question is that setting on the database or will other users need to set that also.

Thanks

I'm pretty sure it applies to users as well - I've used it in a couple of databases I've deployed and never had anyone come back with questions about warning messages. Though I'm sure the above response will provide a failsafe.

 

[RainLover]

It would need to be set on all machines.

Try using CurrentDB.Execete QueryName dbfailOnError

Do not turn warnings off.

Do a Google search for "CurrentDb.Execute" if you run into problems.

 

[VilaRestal]

For a query with parameters, to ask the user for the parameters, I don't think CurrentDb.Execute will work. I think it has to be DoCmd.OpenQuery.

I would certainly advise being a bit careful about turning off warnings but it's not a bad thing to do so long as you turn them straight back on after.

--------------
Edit: I had got it into my head that the query did ask for parameters but re-reading I see that you don't say that. Nevertheless it's useful to know that DoCmd.OpenQuery is needed in those cases and DoCmd.SetWarnings would be how to switch off warnings in those case.

And I see Rain was referring to turning warnings off in the options, which I agree is not a good idea.