VBA - Using DAO to add record(s) to a table
Posted on 2004-11-03
I'm helping a buddy out with some coding for a small app he's building, and could use some help with this part.
The code that I started with is pasted below.
The idea is to add record(s) to the Inventory table. What he would like is use an Unbound field (txtAddRecord) on a form to enter that number of items to add to the Inventory table.
So if he enters "10" into the Unbound field (txtAddRecord) - when he clicks the cmdButton he wants to add 10 items (records) to inventory (table).
AND he wants the Field "InventoryNumber" to be increased by 1 for each record added. If the Unbound Field (txtAddRecord) is "NULL" he doesn't want any records added. Use a msgbox asking User to enter the number of records to add.
Here's some table info:
tblInventory (Child Table)
InventoryID (PK - autonum)
ProductID (FK - num)
InventoryNumber (Text) This can be changed to Number if needed
I guess the idea would be to run through (Loop?) this type of code as many times as listed in the Unbound Field on the Form.
Private Sub lblInventory_Click()
' Used to add Inventory Item to the tblInventory table
On Error GoTo Err_lblInventory_Click
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
'This If statement needs to be improved so it will check for NULL or changes to the number entered in the field
If IsNull(Me.txtAddRecord) Then
MsgBox "You Must Enter the Number of Items to ADD to Inventory.", vbInformation + vbOKOnly, "Inventory Update"
Set dbs = currentdb
Set rst = dbs.OpenRecordset("tblInventory", dbOpenDynaset)
![ProductID] = Me.ProductID
![InventoryNumber] = 'This Number would be automatically added incrementally by 1
![InventorytDateIn] = date
MsgBox "Inventory Items added successfully.", vbInformation + vbOKOnly, "Inventory Update"
I'm not sure if my question is clear enough so let me know if I need to explain further.
Thanks in advance