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
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