Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2018, 10:31 AM   #1
Moore71
DEVELOPER
 
Join Date: Jul 2012
Posts: 123
Thanks: 23
Thanked 0 Times in 0 Posts
Moore71 is on a distinguished road
Sample VBA code to update Multiple tables/Querries from

Hi all,
can someone here give me sample VBA code to UPDATE Multiple tables/Querries at the same time from one FORM?
I have the following code which I don't know the reason why it is not working after series of trials:


Private Sub cmdSales_Click()
On Error Resume Next
Dim SQL As String
Dim SQL2 As String


SQL = "UPDATE tblOutbound INNER JOIN tblItem ON tblOutbound.ItemID = tblItem.ItemID SET tblItem.QtyAvail = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], tblItem.UnitSale = [tblOutbound].[ExtendedPrice] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![InvoiceNo]));"
SQL2 = "UPDATE tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID SET tblClient.ClientAcc = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![OutboundInv]));"
'SQL = "UPDATE SalesQRY SET ClientAcc = ClientAcc + AmountRecorded, QtyAvail = QtyAvail - QtyOut WHERE InvoiceNo = forms!frmPoS!OutboundInv;"
'CurrentDb.Execute "UPDATE SalesQRY SET UnitSale = ExtendedPrice, QtyAvail = QtyAvail - QtyOut WHERE InvoiceNo = forms!frmPoS!OutboundInv;"

DoCmd.SetWarnings False

DoCmd.RefreshRecord

DoCmd.RunSQL SQL

DoCmd.RunSQL SQL2

DoCmd.SetWarnings True

Me.cmdSales.Enabled = False
Me.cmdDelete.Enabled = False
Me.cmdPrint.Enabled = True
Me.cboClient.Enabled = False

DoCmd.OpenForm "frmCustomerPay2", acNormal, , , acFormEdit, acDialog

End Sub
-------------------------------------------------------------------------------------------------
Thanks in advance

Moore71 is offline   Reply With Quote
Old 11-10-2018, 11:50 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,089
Thanks: 10
Thanked 3,874 Times in 3,817 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Sample VBA code to update Multiple tables/Querries from

You haven't said why/how it's not working, so hard to debug. Perhaps the greater issue is why you're doing it. Most of us would not try to keep track of quantity on hand, total amount sold, etc. You calculate that type of thing as needed. There's a discussion of why here:

http://allenbrowne.com/AppInventory.html
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-10-2018, 05:54 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,431
Thanks: 2
Thanked 1,993 Times in 1,949 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Sample VBA code to update Multiple tables/Querries from

Comment out the "On Error Resume Next" and "DoCmd.SetWarnings False" then run the code.
If you get any error, write in which code line + error number and description.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
update multiple querries , update multiple tables , update querry , update vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Querries In One ddrew Queries 4 08-25-2005 01:11 AM
Found sample code on mail merge, but sample not working Ravenray General 1 01-07-2004 09:19 PM
Newbie needs help! Summary querries from multiple queries. mpaulic Queries 1 09-14-2003 06:34 PM
Forms won't update table or querries billmorr Forms 1 01-26-2000 08:45 PM




All times are GMT -8. The time now is 08:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World