Code to update related table

  • Thread starter Thread starter Don Reinken
  • Start date Start date
D

Don Reinken

I have a form for a table that records receipts in an
inventory application. I'm looking for sample code that
will update a value in a related inventory table with the
quantity entered in the receiving table, thus adding the
new receipt quantity to any current quantity.

The tables have a relationship linked on CatalogNumber.

I envision a command button to execute a function
(onClick) -- but my vb is so rusty that I don't know
where to start.

Many thanks for any help you may give!
 
Don Reinken said:
I have a form for a table that records receipts in an
inventory application. I'm looking for sample code that
will update a value in a related inventory table with the
quantity entered in the receiving table, thus adding the
new receipt quantity to any current quantity.

The tables have a relationship linked on CatalogNumber.

I envision a command button to execute a function
(onClick) -- but my vb is so rusty that I don't know
where to start.

Many thanks for any help you may give!
Click to expand...

It would be something like

'---- start of air code for button's Click event ----
Private Sub cmdUpdateOnHand_Click()

Dim strSQL As String

If IsNull(Me!QtyReceived) Then
MsgBox "Enter quantity received first!"
Exit Sub
End If

strSQL =
"UPDATE Inventory " & _
"SET QtyOnHand = QtyOnHand+" & Me!QtyReceived & _
" WHERE CatalogNumber=" & Me!CatalogNumber

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'---- end of code ----
 
-----Original Message-----


It would be something like

'---- start of air code for button's Click event ----
Private Sub cmdUpdateOnHand_Click()

Dim strSQL As String

If IsNull(Me!QtyReceived) Then
MsgBox "Enter quantity received first!"
Exit Sub
End If

strSQL =
"UPDATE Inventory " & _
"SET QtyOnHand = QtyOnHand+" & Me!QtyReceived & _
" WHERE CatalogNumber=" & Me!CatalogNumber

CurrentDb.Execute strSQL, dbFailOnError

End Sub
'---- end of code ----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(remove NOSPAM from address if replying by email)
Click to expand...
Dirk,

I've made the corrections to conform the code to your
sample given above and added Option Explicit. There is
still a problem: Run time error '3061' Too few
parameters. Expected 2.

Debug points to the line:

CurrentDb.Execute strSQL, dbFailOnError

Can you suggest what parameter might be missing?

Thanks again for your help!
 
Don Reinken said:
Dirk,

I've made the corrections to conform the code to your
sample given above and added Option Explicit. There is
still a problem: Run time error '3061' Too few
parameters. Expected 2.

Debug points to the line:

CurrentDb.Execute strSQL, dbFailOnError

Can you suggest what parameter might be missing?

Thanks again for your help!
Click to expand...

You'll get that message when you use a name in the SQL statement that
isn't recognized, because the database engine assumes any unrecognized
name is a parameter. So probably you haven't change the example table
name or field names I picked to conform to your actual table and field
names, or else maybe you've misspelled them. Check against the table
design and adjust the SQL as necessary. Also, I assumed CatalogNumber
is numeric. If that's a text field, the value must be enclosed in
quotes, or again you may get that message.
 
Back
Top