merge update queries?

[Guest]

I have 3 update queries that I would like to merge into one to be able to add
the one to the form for one single overall update rather than pushing 3
buttons. I have tried mucking about with SQL statements (know little) for a
Union Query but it keeps giving me various errors.... help, please??

Also I have a calculating field in the queries - will that make a
difference? (difference in days from first date to today)

 

[tina]

why have a separate button on your form for each Update query? just run all
three queries from one macro or VBA procedure, called by a single button on
the form. it should be transparent to the user.

hth

 

[Guest]

THANK YOU... my mind is mush - have been working on this DB for far too long
today. Macro worked perfectly.

However, is there anyway to keep it from asking "Are you Sure" 3 times?

Cheers
L.

 

[John Spencer]

If you are using a macro, then you could set warnings off at the start of the
macro and then set warning on at the end of the macro.

THIS can be dangerous since if you encounter an error in the macro, the warnings
will remain off until you open the database again. It is much better to either
handle this with VBA where you can do error trapping and turn warnings back on
if an error occurs.

 

[Guest]

I am not entirely sure how to do this with VBA code. (I'm really green at
coding) I know I can turn off confirmation all together in settings, but
would prefer to have a single confirmation question asked.

 

[John Spencer]

I can' test this right now. Don't have Access available on this computer. Code
would look something like the following.

Private Sub btnPrint_Click()
Dim dbAny As DAO.Database

On Error GoTo btnPrint_Error
Set dbAny = CurrentDb()
If MsgBox("Run three queries?", vbOKCancel) = vbOK Then
DoCmd.SetWarnings False
dbAny.Execute "FirstQueryName", dbFailOnError

dbAny.Execute "SecondQueryName", dbFailOnError

dbAny.Execute "ThirdQueryName", dbFailOnError
DoCmd.SetWarnings True
End If
Exit Sub

btnPrint_Error:
DoCmd.SetWarnings True
End Sub

 

[Guest]

ohhhhhh it works! thank you thank you thank you!