Append qry only works the second time

[Lori2836 via AccessMonster.com]

Good morning. I have a form where information is entered, and upon close of
the form, a command button that runs a macro to append the data to another
table. For some reason, it will not work the first time. The data won't
append until I go back into the form and hit the command button a second time.
Then it works great. Has anyone else ever come across this?

Thanks for your help!

Lori

 

[Darrell Childress]

Lori,
It might be that the data is not yet saved when you exit the form. You
probably need to put in a Command acCmdSaveRecord. Here's what I do with
a lot of my forms: I put a box there that saves the record. I actually
started doing it because users are just accustomed to having to save
"files" and some would get confused without a Save button.
Darrell

 

[Marshall Barton]

Good morning. I have a form where information is entered, and upon close of
the form, a command button that runs a macro to append the data to another
table. For some reason, it will not work the first time. The data won't
append until I go back into the form and hit the command button a second time.
Then it works great. Has anyone else ever come across this?

It sounds like your append is running before the data was
saved. Try saving the record before running the append. I
won't use macros so I can't suggest how you should do that.

 

[John Spencer]

Not enough detail to do more than make a wild guess.

I would bet that your current record has not been saved when you run the
macro. And the Append query is using that data.

In the command button, add some code to force the record to be saved.
Something as simple as

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

'Now run the macro.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Lori2836 via AccessMonster.com]

Thanks John. Everyone that responded suggested I save the data first.....I
do have a command in the macro to save, then append........but it still
doesn't work. I would be willing to try your code, but where in the command
button would something like that be entered? I'm not too familiar with
writing code.

Not enough detail to do more than make a wild guess.

I would bet that your current record has not been saved when you run the
macro. And the Append query is using that data.

In the command button, add some code to force the record to be saved.
Something as simple as

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

'Now run the macro.

Good morning. I have a form where information is entered, and upon close
of

[quoted text clipped - 8 lines]

Lori

 

[Lori2836 via AccessMonster.com]

Thanks Darrell.......in this case, the group I'm creating this for, doesn't
want a SAVE button......so have created it within a macro...to run when they
hit the CLOSE button...it is supposed to save the table, then append the data
to the next table, then go back and delete from the original table......but
its not doing any of it. When I go back into the form the 2nd time, the
data is still there.....I hit the CLOSE button and it all works perfectly.
Seems like it isn't saving, for some reason. I'm using the Save command in
the macro.....

Lori,
It might be that the data is not yet saved when you exit the form. You
probably need to put in a Command acCmdSaveRecord. Here's what I do with
a lot of my forms: I put a box there that saves the record. I actually
started doing it because users are just accustomed to having to save
"files" and some would get confused without a Save button.
Darrell

Good morning. I have a form where information is entered, and upon close of
the form, a command button that runs a macro to append the data to another

[quoted text clipped - 5 lines]

Lori

 

[John Spencer]

Save in a macro does not save data. It saves changes to the form design or
other object.

On your form in design view
-- Click on the button
-- Click on the click event for the button
-- Select [Event Procedure]
-- Click on the button at the end of the event ... that will open a VBA
window that will have code something like below

Private Sub BtnDoSomething_Click()


End Sub

Add the following to it

Private Sub BtnDoSomething_Click()
If Me.Dirty = True Then Me.Dirty = False

DoCmd.RunMacro "NameOfYourMacroInQuotes"

End Sub

I would learn some vba and get rid of the macro and replace it with VBA code
to execute the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John. Everyone that responded suggested I save the data
first.....I
do have a command in the macro to save, then append........but it still
doesn't work. I would be willing to try your code, but where in the
command
button would something like that be entered? I'm not too familiar with
writing code.

Not enough detail to do more than make a wild guess.

I would bet that your current record has not been saved when you run the
macro. And the Append query is using that data.

In the command button, add some code to force the record to be saved.
Something as simple as

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

'Now run the macro.

Good morning. I have a form where information is entered, and upon
close
of

[quoted text clipped - 8 lines]

Lori

 

[Darrell Childress]

Lori,
The Save command saves the FORM, not the record. You'd be better to use
code, but if you don't know know and really want to use a macro, instead
of using the SAVE command, use RunCommand as the Action and then
SaveRecord as the command. I hope that makes sense...if not post back.
Darrell

Thanks Darrell.......in this case, the group I'm creating this for, doesn't
want a SAVE button......so have created it within a macro...to run when they
hit the CLOSE button...it is supposed to save the table, then append the data
to the next table, then go back and delete from the original table......but
its not doing any of it. When I go back into the form the 2nd time, the
data is still there.....I hit the CLOSE button and it all works perfectly.
Seems like it isn't saving, for some reason. I'm using the Save command in
the macro.....

Lori,
It might be that the data is not yet saved when you exit the form. You
probably need to put in a Command acCmdSaveRecord. Here's what I do with
a lot of my forms: I put a box there that saves the record. I actually
started doing it because users are just accustomed to having to save
"files" and some would get confused without a Save button.
Darrell

Good morning. I have a form where information is entered, and upon close of
the form, a command button that runs a macro to append the data to another

[quoted text clipped - 5 lines]

Lori