maybe not quite got it..

[action]

I thought I had it sorted but after following the example I have something wrong. I have not worked with recordsets as I am new to VBA.

the code given I have adjusted and ended up with this:


Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TblInventoryUpdateAudit", dbOpenDynaset)
rs.AddNew
'rs!sCurrentUser = CurrentUser 'a system function
rs!InventoryChange = Me![Txtupdateinventory]
rs!dtCreated = Now()
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

If I comment out this line:
rs!InventoryChange = Me![Txtupdateinventory]

It works, Where have I gone wrong?
I get the error"Item not found in this collection"

Help Please!

 

[pbaldy]

Odds are that either "InventoryChange" is not a field in the table or "Txtupdateinventory" is not a field on the form.

 

[action]

Thanks Paul but I have checked that out several times, does the code look right or does it have an obvious error in it?

 

[pbaldy]

I don't see anything wrong right off. Can you post a sample of the db?

 

[ghudson]

If all of your field names in the code are spelled correctly [are you sure?] and you have not exited the db since you started playing with your new function...

I suggest that you close the db, open it and then repair and compact it. I have had similar and weird things not work when I knew they should. If I closed the db and then reopened it the darn code would start working. This has only happened to me a few times I might add but a few times to many. Weird? Yea but so is Access.

HTH