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

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/ex...tributors.html
__________________________________________

"Billy" wrote:

> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

Thanks. It works.

On May 18, 5:19 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> 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 MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
> __________________________________________
>
> "Billy" wrote:
> > 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