Append query does not append recently added data.

[jinalpatel]

I have two tables. tblClass and tblWithdrawn. On my main form(bound to tblClass) I have several data entry fields like
Date withdrawn,
Status (active or withdrawn)
Date Classified etc.

Also there is one command button called cmdAppendDelete . On click this command button two queries are run .(append and delete)

When user selects Status = Withdrawn and enter the "Date Withdrawn", he has to click on this command button.

Append query appends recently eneterd withdrwan data to tblWithdrawn and

Delete query that deletes the same record from class table.

I got that all working.
When user enter the data and click the cmdAppendDelete it also runs both the queries simultanelouly.
Those messages usually access shows when it is running append and delete query says " 0 rows are copied to tblWithdrawn and 0 rows are deleted from tblClass"

Ok. after two seconds if I click on the cmdAppendDelete , it does its job and appends row to tblWithdrwan and delete the same row from tblClass. And now the messages are " 1 row are copied to tblWithdrawn and 1 rows are deleted from tblClass"

Why is that? Is there any way around it?

(The user should immedieately be able to append and delete the record. That is my concern)

[ChipR]

What code are you using to run these queries? Seems you could just run an INSERT, and then a DELETE.

DoCmd.RunSQL "INSERT INTO tblWithdrawn..."
DoCmd.RunSQL "DELETE FROM tblClass WHERE..."

[missinglinq]

It sounds like this is a new record; is that correct? If so, you'll need to save the record first. The first time you click the button (I'm guessing) focus goes to the queries, leaving the form, temporarily, thus saving the record. The next time you click it the record, having now been saved, is available for your queries.

Use something in your button code like

If Me.Dirty Then Me.Dirty = False

before running the queries.

Linq ;0)>

[jinalpatel]

Thanks both of you for help.

Missinglinq,
that worked.
Can you please explain me what does that little code segment mean?

If me.dirty then
me.dirty= false
end if

I appreciate your help

[OldBirdman]

I notice that this code is always presented as:

If me.dirty then 
me.dirty = false 
end if

Why not:

me.dirty= false

In other words, does Access do a Save if there is nothing to save? Is this also true with RunCommand acCmdSaveRecord ? What is wrong with using RunCommand?

[missinglinq]

I notice that this code is always presented as:

If me.dirty then me.dirty = false

Why not:

me.dirty = false

No reason, really, except that its intent is made clearer by adding 16 characters.

What is wrong with using RunCommand acCmdSaveRecord ?

Ask NeoPa about that! From a post earlier this week, he has found some problems using this command in some circumstances.

Linq ;0)>

[OldBirdman]

OK. I just thought maybe there was a large overhead issue involved as the answers were so consistant.

[missinglinq]

Not really. My first PC had a huge, for the time, hard drive with 20 mb of memory! And we coveted every single character we used! But as memory has gotten cheaper and systems have gotten faster, we have the ability to not only write terse, to the point code, but code that explains itself! Especially here, where we're usually helping newbies with their problems!

Have a great weekend!

Linq ;0)>

[OldBirdman]

I certainly wasn't trying to be difficult. I had noticed that many MS programs do not set the save button.enabled= false, but clicking it does not do the save again. So some Microsoft code checks for dirty=true, and decides not to save if dirty=false. I wasn't sure for saving records in a table.
Hard drives are mechanical devices, and do crash. Some new computers are now trumpeting 8Gb solid state hard drives, but for now, I worry about the mechanical drive. I certainly don't worry about space any more.