creating an inventory application

[Stella Pieters via AccessMonster.com]

Let me start by saying that I'm a newbie in VBA.

I've created an application in access where the user enters the quantity of
the items sold in a form. I would like to have these records created in a
table, mentioning the qty sold, the transaction date etc. this table I'm
planning to use afterward to calculate the quantity on hand.

I have created the following code, but I keep on receiving the error, that
it does not recognize the fields mentioned in the code.

Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset
Dim mdbLocal As DAO.Database
Set mdbLocal = DBEngine(0)(0)
Set rst = mdbLocal.OpenRecordset("visits", dbOpenDynaset, dbAppendOnly)

If Me![Owndepomys] = False Then
[Inventory.Storeroom] = "FP02"
[Inventory.TransactionDate] = Me![Datevisit]
[Inventory.Artcode] = DLookup("[ArtCode]", "Contraceptives", "
[Description]=" & Forms![visits]!Description)
[Inventory.Description] = Me![Contraceptives]
[Inventory.TransactionDescription] = "Over the counter"
[Inventory.UnitsSold] = Me![Qty]
[Inventory.Qtyonhand] = [Inventory.Qtyonhand] - Me![Qty]
[Inventory.AmountQty] = [Inventory.AmountQty] * Me!
[Contraceptives.Price]
rst.AddNew

End If

End Sub

--
Message posted via http://www.accessmonster.com

[pietlinden]

you need to prefix the field names with the name of the recordset...

If Not Me![MyBoolean] Then
'--ADD A NEW RECORD
rst.AddNew
'--Set the values for individual fields
rst.Fields("Inventory Storeroom")="FP02"
rst.fields("ArtCode")="Whatever"
'--Save the record
rst.UPDATE
End If

[Stella Pieters via AccessMonster.com]

Thanks for your reaction, I've changed my code as per your suggestion and I
still get the same error code 2465, "can't find field "|" referred to in
your expression."

This is the changed code. Could you have a look at it again? Thanks again
for your help.

Private Sub Form_AfterUpdate()
'If Me.NewRecord Then

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

If Not Me![Owndepomys] Then
rst.AddNew
rst.Fields([Transaction Table].[Storeroom]) = "FP02"
[Transaction Table.TransactionDate] = Me![Datevisit]
[Transaction Table.Artcode] = DLookup ("[ArtCode]",
"Contraceptives", "[Description]=" & Forms![visits]!Description)
[Transaction Table.TransactionDescription] = "Over the counter"
[Transaction Table.UnitsSold] = Me![Qty]
[Transaction Table.AmountQty] = [Transaction Table.AmountQty] * Me!
[Contraceptives.Price]
rst.Update

End If
'End If

'err_Form_AFterUpdate:
Exit Sub
End Sub

--
Message posted via http://www.accessmonster.com