Form Recordset - Add New Record?

[gray]

Hi All

Access2002/2007
WunXPPro

In order to permit users to cancel their updates to records on Forms (subForms actually), I've adopted the DAO SQL Transaction technique: i.e. BeginTrans/RollBack/Commit etc.

I create a DAO recordset from the appropriate table and set the Form's recordset to it. Custom Save and Cancel buttons call CommitTrans or Rollback as reqd.

This works great for editing but I unable to add new records to the recordset??.

Here's how I open the rs:-

Private Edit_Wrk As Workspace
Private Edit_Db As Database
Private Edit_rstDAO As DAO.Recordset
 
'Set up Transaction resources
Set Edit_Wrk = Workspaces(0)
Set Edit_Db = CurrentDb
 
SQLLine = "SELECT * FROM MyTable"
Set Edit_rstDAO = Edit_Db.OpenRecordset(SQLLine, dbOpenDynaset, adLockPessimistic)
 
Set Me.Form.Recordset = Edit_rstDAO
Edit_Wrk.BeginTrans

I then attempt to add a new record with:-

Edit_rstDAO.AddNew
Edit_rstDAO!Date_Record_Added = "'" & Date & " " & Time & "'"
... blah blah..
Edit_rstDAO.Update

This fails at the AddNew with :-
3246 - This action was cancelled by an associated object.

I also tried with Me.Form.Recordset.AddNew..etc but his too fails with err3246.

I also tried with...

'Create New Record within Transction
SQLLine = "INSERT INTO MyTable.... blah blah"
Edit_Db.Execute SQLLine
 
'Query the RS to move to new record
Me.Form.Requery

But this fails in the Current Event during the Requery with:-

'Operation Not Supported in Transactions'

Where am I going wrong please?? Thanks

P.S. I don't use the more normal BeforeUpdate / Cancel=True technique cos' all my custom buttons are on the Header of the mainForm and they can't be reached whilst there are oustanding updates on the subForm....

 

[boblarson]

Well, if Date Record Added is a date/time field you need to change this line:

Edit_rstDAO!Date_Record_Added = "'" & Date & " " & Time & "'"

to this

Edit_rstDAO!Date_Record_Added = #" & Now "#"

 

[Khalid_Afridi]

The bob is great, in Access you must use # # in SQL statement before and after any date entry

 

[vbaInet]

A little typo:

Edit_rstDAO!Date_Record_Added = "#" & Now & "#"

 

[Khalid_Afridi]

Good Eyes

 

[gray]

Hi Gents...

Thanks for looking at this... Actually, the proc was bombing out at 'AddNew' before I attempted to add the dat/time in and I think that failure was probably due to the order of BeginTrans and setting the recordset up

I've done some juggling around and I thnk I may have resolved the issue... so

SQLLine = "SELECT * blah blah" 
Set Edit_rstDAO = Edit_Db.OpenRecordset(SQLLine, dbOpenDynaset, adLockPessimistic)
Edit_Wrk.BeginTrans
 
Edit_rstDAO.AddNew
Edit_rstDAO!Added_By_Unique_No = Curr_User_Unique_No
Edit_rstDAO.Update
 
Set Me.Form.Recordset = Edit_rstDAO

I was reluctant to juggle this before because the consensus seemed to be to do the BeginTrans after setting the form recordset.... in my tests the above does seem to work though... and in terms of rolling back, both due to errors and/or to deliberate cancellations, all appears to work correctly.

Hope this is of use to others.

Thanks again as always.....