Can't emulate in code steps I can do manually

[Billy]

I have a form where I am entering some data and then clicking a button
to run some SQL statements. Sporadically, I am getting an error
message.

The error message is "Microsoft Office Access can't append all the
records in the append query".

If I move to the previous record on the form and then back to the
current record before I click on the button, there is no error with
the SQL statements. If I hit Ctrl-S before I click on the button,
there is no error. But, if I do neither of these steps, I get the
error listed above.

I have attempted to band-aid the problem by just putting code in at
before the SQL code to emulate the Ctrl-S action. However, I either
haven't figured out how to write that code properly or the code is
correct and it is not the same as manually hitting Ctrl-S while in the
form.

The code I was using to emulate the Ctrl-S was DoCmd.Save.

Regardless whether that is the proper code for the Ctrl-S action, I
don't understand why Access is seeing a difference.

I have also considered that I need to do some sort of Refresh Method,
but haven't figured that out either.

Anyone have any suggestions?

________________
I am using Microsoft Access 2007

 

[Tom Wickerath]

Hi Billy,

The code I was using to emulate the Ctrl-S was DoCmd.Save.

Try this instead:

If Me.Dirty = True Then
Me.Dirty = False
End If


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[George Nicholson]

When adding data to a new record, that record doesn't exist in the
underlying table *yet*. Moving to a different record, closing the form or
specifically saving the record will "commit" the new record. The behaviour
you describe indicate that your SQL statements require that this record to
exist in a table before they can execute properly.

Before you run your SQL statements, try:
DoCmd.RunCommand acCmdSaveRecord

DoCmd.Save is used to save database objects (forms, reports, queries, etc.),
not data. With no argument specified it will save the active object
(presumably your form). It won't save the data on the form into a table (it
might save the data in the Value property of each control, but thats not
what you want...)

HTH,

 

[Billy]

Thanks. It works.

Hi Billy,


Try this instead:

If Me.Dirty = True Then
Me.Dirty = False
End If

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________