Solved

Error when replacing a date field with a new date value

Posted on 2006-07-04
8
238 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 61

Accepted Solution

by:
mbizup earned 250 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 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