How to make one button to update 3 queries

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello,

Here I have 2 buttons to run 2 append queries. My question is, I will still
have another one. How can we make it only 1 button to run 3 append queries:

This is my 2 append queries:
1.
Private Sub UpdateMemberData_Click()
On Error GoTo Err_UpdateMemberData_Click

Dim stDocName As String

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

Exit_UpdateMemberData_Click:
Exit Sub

Err_UpdateMemberData_Click:
MsgBox Err.Description
Resume Exit_UpdateMemberData_Click

End Sub

2.

Private Sub MemberAddressUpdate_Click()
On Error GoTo Err_MemberAddressUpdate_Click

Dim stDocName As String

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

Exit_MemberAddressUpdate_Click:
Exit Sub

Err_MemberAddressUpdate_Click:
MsgBox Err.Description
Resume Exit_MemberAddressUpdate_Click

End Sub


Thanks in Advance,

Frank
 
Frank Situmorang,

Here you go...

Not cmdYourButton is the name of your new button OR you could Copy/Paste the
middle 5 lines on an existing button.

Private Sub cmdYourButton_Click()
On Error GoTo Err_cmdYourButton_Click

DoCmd.SetWarnings False 'Turn messages back OFF
DoCmd.OpenQuery "Qry_UpdateMembershipdata", acNormal, acEdit
DoCmd.OpenQuery "Qry_updateMemberAddressData", acNormal, acEdit
'Add your other query here
DoCmd.SetWarnings True 'Turn messages back ON

Exit_cmdYourButton_Click:
Exit Sub

Err_cmdYourButton_Click:
MsgBox Err.Description
Resume Exit_cmdYourButton_Click

End Sub


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Thanks Gina...you are awesome...

Frank

Gina Whipp said:
Frank Situmorang,

Here you go...

Not cmdYourButton is the name of your new button OR you could Copy/Paste the
middle 5 lines on an existing button.

Private Sub cmdYourButton_Click()
On Error GoTo Err_cmdYourButton_Click

DoCmd.SetWarnings False 'Turn messages back OFF
DoCmd.OpenQuery "Qry_UpdateMembershipdata", acNormal, acEdit
DoCmd.OpenQuery "Qry_updateMemberAddressData", acNormal, acEdit
'Add your other query here
DoCmd.SetWarnings True 'Turn messages back ON

Exit_cmdYourButton_Click:
Exit Sub

Err_cmdYourButton_Click:
MsgBox Err.Description
Resume Exit_cmdYourButton_Click

End Sub


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
 
Sorry Gina to ask you again, I forgot to ask you, why you turn off the
message, this:
DoCmd.SetWarnings False 'Turn messages back OFF
Is n't good to let us know how many records will be updated...and other
messsage as usual when we run the append query?

Thanks for your help.

Frank
 
Frank said:
Sorry Gina to ask you again, I forgot to ask you, why you turn off the
message, this:
DoCmd.SetWarnings False 'Turn messages back OFF
Is n't good to let us know how many records will be updated...and other
messsage as usual when we run the append query?
Click to expand...


PMFJI, but it is usually better to use the Execute method
instead of OpenQuery. OpenQuery is asynchonous so you can't
tell when it has finished updating the records. That's
because OpenQuery is intended to display a SELECT type
quet's datasheet, not for running action queries.

The Execute method is synchronous so your code will not
proceed until all the records have been updated. In
addition, Execute will not generate any messages, but it
will set the RcordsAffected property so you can tell how
many records were updated.

Private Sub cmdYourButton_Click()
Dim db As DAO.Database
On Error GoTo Err_cmdYourButton_Click

Set db = CurrentDb()
db.Execute "Qry_UpdateMembershipdata", dbFailOnError
MsgBox db.RecordsAffected & " members were updated"
db.Execute "Qry_updateMemberAddressData", dbFailOnError
MsgBox db.RecordsAffected & " addresses were updated"
. . .
 
Frank said:
Sorry Gina to ask you again, I forgot to ask you, why you turn off the
message, this:
DoCmd.SetWarnings False 'Turn messages back OFF
Is n't good to let us know how many records will be updated...and
other messsage as usual when we run the append query?

Thanks for your help.
Click to expand...
That depends on who is running the application. Most people don't want to be
asked a second time if they want to run the application and don't care how
many records are updated since they know that all the records they do want
updated get updated.

It tends to make some people nervous saince those messages can be construed
as warnings.
 
Frank,

Pardon the delay, was enjoying the holiday. I turn them off because my end
users don't really care how many records get updated/appened/etc... as long
as they get updated/appended/etc... I turn them off then back on after my
queries have run.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Thanks Gina... you are very helpful.

Frank

Gina Whipp said:
Frank,

Pardon the delay, was enjoying the holiday. I turn them off because my end
users don't really care how many records get updated/appened/etc... as long
as they get updated/appended/etc... I turn them off then back on after my
queries have run.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
Click to expand...
 
Back
Top