Solved

Error when replacing a date field with a new date value

Posted on 2006-07-04
8
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

717 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