Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error when replacing a date field with a new date value

Posted on 2006-07-04
8
Medium Priority
?
242 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

609 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