Code to force SQL to save a record

  • Thread starter Thread starter LisaB
  • Start date Start date
L

LisaB

I have an Access 2000 front-end connected to a SQL 2000 backend
I have a form with a subform
when a new record is created in the subform I would like to use the value in
the autonumber field to run some code
-- for example
After the user enters a value in fldGrantNum I would like to execute
the expression me.fldGrantID = "ABC" + AutoNum

THE PROBLEM
The autonumber is not generated until the record is saved/updated in SQL
When I try to force a save by using
--- DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
I get the runtime error 2046
---- The Command or Action 'SaveRecord' isn't available now

How do I force a record to be saved so that the autonumber field will
generate a new number
 
hi.
autonumber is pretty fixed in access.
1.you may have to creat your own autonumber by having a
table with the your own autonumber in it. call the number
to the form on enter new record and on save have the
autonumnber updated the autonumber +1.
2.have a hiden textbox on the form. populate it with the
autonumber on enter new record. do your calculations on
the hidden number.
 
Just force a disk write, and then you should be able to grab the "idenitry"
field.

So, try:

me.Refresh ' force disk write..and update autonumber
me.fldGrantID = "ABC" + AutoNum
 
Back
Top