?
Solved

Error when replacing a date field with a new date value

Posted on 2006-07-04
8
Medium Priority
?
244 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:crm-serv
  • 4
  • 3
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17038959
u have to do

rst.AddNew

then your code

rst(4) = Application.Forms!SoftwareEditRecord.Controls!dateAcquired
    rst.Update
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17038960
hang on, ignore that
Italy just won, wot a game, Im not concentrating, sorry
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17038970
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 17039026
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
 
LVL 61

Expert Comment

by:mbizup
ID: 17039117
That honestly was a wild guess, but I'm glad it helped!
0
 

Author Comment

by:crm-serv
ID: 17039119
Thanks for the rapid response.  

I was depending on the Edit method to apply continuously once invoked.  Obviously that is not the case.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17039127
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040393
I thought that was already done which is why I didnt say it, the error message tells u what to do anyway
doh!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question