0

I have a form InventoryForm and a table StockRecords. I'm trying to get the records in the form to save to the table AfterUpdate. Both my form and table have fields EmployeeName, PartNo, Quantity, BinNo, Supplier, Shelf, Description, Price & TargetStockLevel.

The form AfterUpdate's VBA code (that I've seen working for others online but not for me) is:

Private Sub Form_AfterUpdate()
    CurrentDb.Execute "INSERT INTO StockRecord (EmployeeName, PartNo, Quantity, BinNo, Supplier, Shelf, Discription, Price, TargetStockLevel) VALUES ('" & Me.SelectName & "', '" & Me.PartNo & "', '" & Me.Quantity & "', '" & Me.BinNo & "', '" & Me.Supplier & "','" & Me.Shelf & "', '" & Me.Discription & "', '" & Me.Price & "', '" & Me.TargetStockLevel & "' )"
End Sub

Edit :

Private Sub Form_AfterUpdate() 
    'add data to table
    CurrentDb.Execute "INSERT INTO StockRecord(EmployeeName, PartNo, Quantity, BinNo, Supplier, Shelf, Discription, Price, TargetStockLevel) " & _
    " VALUES ('" & Me.cboSelectName & "', '" & Me.cboPartNo & "','" & _
    Me.txtQuantity_Entered & "', '" & Me.txtBinNo & "', '" & Me.txtSupplier & "','" & Me.txtShelf & "', '" & Me.txtDiscription & "', '" & Me.txtPrice & "', '" & Me.txtTargetStockLevel & "' )"
End Sub

This new VBA code worked once but now won't (even when I made no changes). I get a message saying :

Compile Error : method or data member not found.

All fields are text boxes, except two combo boxes (SelectName & PartNo). How to fix this?

user4157124
  • 2,809
  • 13
  • 27
  • 42
MarkMc
  • 1
  • 1
  • I should also mention, the form is already updating another table (to which it is bound to) to change quantity levels. So i cant use DoCmd.RunCommand acCmdSaveRecord DoCmd.GoToRecord , , acNewRec – MarkMc Nov 03 '20 at 09:15
  • 2
    _Bind_ your form to the table. That's the magic of Access. And go get a tutorial: _Beginning with Microsoft Access_ or similar. – Gustav Nov 03 '20 at 09:22
  • Am I able to bind it to two tables though ? My form is updating quantities of stock in one table called 'Inventory', but i'm trying to add the records of the form to another table 'StockRercord' so that i can see a transaction log of parts being used (by who and when). – MarkMc Nov 03 '20 at 09:33
  • There are many ways to maintain a log table. I would use VBA to update a recordset holding the logtable. With SQL - as you currently use - start by using the correct syntax - not all values are text. See my function [CSql](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error/43591778#43591778). – Gustav Nov 03 '20 at 10:23
  • Is something in your code highlighted when you get the compile error? If so, what? Me.txtDiscription or something else? – HansUp Nov 03 '20 at 23:03
  • Thank Gustav. HansUp, yes thats exactly what happened. I removed the 'cbo' and 'txt' before each field name in the code and it seems to have fixed it. It is working well for me now :) – MarkMc Nov 04 '20 at 12:25

0 Answers0