Error when replacing a date field with a new date value

I am trying to make changes to existing date entries in an Access table.  The new value is taken from a textbox in a form.  The new value is entered in the textbox in the date format used in creating the table.  

When I execute the code it causes runtime error 3105 "Update or CancelUpdate without Addnew or Edit."

Code involved is:

Set rst = CurrentDb.OpenRecordset(strSQL)
rst.Edit
 ...
Application.Forms!SoftwareEditRecord.Controls!dateAcquired.SetFocus
If dateAcquired <> rst(4) Then
    rst(4) = Application.Forms!SoftwareEditRecord.Controls!dateAcquired
    rst.Update
End If
...

In the VBA Sub of which the code above is a part there are several successful updates to text and integer fields before the error occurs.

The values shown for the Recordset field rst(4) and the form control dateAcquired are correct when printed to the immediate window.

Is there a way to replace the value in a date field?

Thanks for your help.

crmurr
crm-servAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Is there a loop structure included somewhere in the "..." between the rst.Edit and the actual setting of rst(4)?  Try placing the rst.Edit Statement immediately before the actual setting of that field:

Set rst = CurrentDb.OpenRecordset(strSQL)

 ...
Application.Forms!SoftwareEditRecord.Controls!dateAcquired.SetFocus
If dateAcquired <> rst(4) Then
    rst.Edit                     '**** Move the Edit statement here
    rst(4) = Application.Forms!SoftwareEditRecord.Controls!dateAcquired
    rst.Update
End If
...
0
 
rockiroadsCommented:
u have to do

rst.AddNew

then your code

rst(4) = Application.Forms!SoftwareEditRecord.Controls!dateAcquired
    rst.Update
0
 
rockiroadsCommented:
hang on, ignore that
Italy just won, wot a game, Im not concentrating, sorry
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
rockiroadsCommented:
do u know the fieldname?

Set rst = CurrentDb.OpenRecordset(strSQL)

'You should check for EOF if u have specified a SELECT as the record may not be there
rst.Edit
 ...

'Why are u setting focus here?
Application.Forms!SoftwareEditRecord.Controls!dateAcquired.SetFocus

'What is the fieldname, why dont u use that? rst!fieldname
'Is dateAcquired a date field on the current form?
If dateAcquired <> rst(4) Then

'If on current form, why dont u just do Me.dateAcquired
    rst(4) = Application.Forms!SoftwareEditRecord.Controls!dateAcquired
    rst.Update
End If
0
 
mbizupCommented:
That honestly was a wild guess, but I'm glad it helped!
0
 
crm-servAuthor Commented:
Thanks for the rapid response.  

I was depending on the Edit method to apply continuously once invoked.  Obviously that is not the case.
0
 
mbizupCommented:
Right... The Edit statement applies on a record-by- record basis.  So each time you move to a new record, you have to explicitly use .Edit and .Update.
0
 
rockiroadsCommented:
I thought that was already done which is why I didnt say it, the error message tells u what to do anyway
doh!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.