Recordsets and updating fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am fairly new to VBA and Access but have some knowledge of SQL and DB
theory so please bear with me...

I am making a database for my company's gift shop. I have the following
tables set up (Only listing tables which have bearing on my question):
Item (Item_ID [PK], Item_Code, Item_Description, Item_Price, Item_TotalStock)
Consignment (Consignment_ID [PK], Consignment_Amount, Item_ID [FK],
ConsignmentDetails_ID [FK])
ConsignmentDetails (ConsignmentDetails_ID [PK], ConsignmentDetails_Date,
ConsignmentDetails_Notes)

As you can see the Consignment table is a bridging table between Item and
ConsignmentDetails.

I have a form called frmConsignmentDetails where the user will enter a date
and notes and that form has a subform called frmConsignmentItems which has a
combobox called cboItem and a textbox called txtAmount where the user will
select the different items and the amounts which we receive in a consignment
of new stock.

I am trying to code a button which runs through all the new consignment
items and adds their amounts to the Item_TotalStock field in the Items table.

Currently my code for the button is as follows:
Private Sub btnCaptureConsignment_Click()

Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim itemID As Integer
Dim addAmount As Integer
Dim strSQL As String

strSQL = "UPDATE Item SET Item.Item_TotalStock = Item.Item_TotalStock +
" & addAmount & " WHERE Item.Item_ID = " & itemID & ";"

Set rs = Me.Recordset

Do While Not rs.EOF
itemID = rs.Fields![Item_ID]
addAmount = rs.Fields![Consignment_Amount]
DoCmd.RunSQL (strSQL)
Loop



However this code does not work, apparently it can not find the [Item_ID]
field within the recordset.

I am clueless as to whether I should even be using a recordset which is
based on a master and subform which is in turn based on a query.

All I want to do is to run through all the items in the consignment (listed
on the subform) and add the amount of stock received for each item to the
Item_TotalStock field in the Items table but I don't know how to loop through
all the comboboxes and textboxes on the subform so that I can capture and add
the values to the necessary fields.

Any help or suggestions on how to do it differently would be greatly
appreciated.

Thanks
 
I am trying to code a button which runs through all the new
consignment
items and adds their amounts to the Item_TotalStock field in the Items
table.

Why can't you do this with a query. You should probably look up Allen
Browne's Inventory/Quantity on Hand solution at his website. Might
save you a LOT of headaches later on.
www.allenbrowne.com
 
Back
Top