Unanswered: Help! Updating multiple tables using ADO and Access
Hi Guys, I would be grateful for some help with this problem. I have an application which has a form with a datagrid which i am using as the transaction form to record receipt of a purchase order. The Form needs to be connected to 4 tables. I created the SQL through Access and it is correct. I have some questions:
1)How can i ensure that the Primary Key autonumber activates on the form
2) How can i ensure that all the other tables are updated.
3) For the stock table, how can i ensure that invoice Quantity is added to the current stock.
I am a novice so a good explanation with syntax would be appreciated. My code is below:
Private Sub Form_Load()
Dim rSql As String
Me.Height = 7000
Me.Width = 12000
Set stockCon = New ADODB.Connection
stockCon.ConnectionString = _
"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\My Documents\GP project\gpframings.mdb"
Set stockRS = New ADODB.Recordset
'Prepare the recordset.
rSql = "SELECT purchaseinvoice.purchaseinvoiceno, purchaseinvoice.purchaseinvoicedate, purchaseinvoice.purchaseorderno, stock.stockno, purchaseinvoicestock.quantity, purchaseinvoicestock.cost, purchaseinvoice.subtotal FROM ((purchaseinvoice INNER JOIN purchaseinvoicestock ON purchaseinvoice.purchaseinvoiceno = purchaseinvoicestock.purchaseinvoiceno) INNER JOIN purchasesupplier ON purchaseinvoice.purchaseinvoiceno = purchasesupplier.purchaseinvoiceno) INNER JOIN stock ON purchaseinvoicestock.stockno = stock.stockno"
.ActiveConnection = stockCon
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
If stockRS.BOF And stockRS.EOF Then
Set tranGrid.DataSource = stockRS