By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Append query does not append recently added data.

jinalpatel
P: 68
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)
Feb 26 '09 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,287
What code are you using to run these queries? Seems you could just run an INSERT, and then a DELETE.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblWithdrawn..."
  2. DoCmd.RunSQL "DELETE FROM tblClass WHERE..."
Feb 26 '09 #2

missinglinq
Expert 2.5K+
P: 3,532
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)>
Feb 26 '09 #3

jinalpatel
P: 68
Thanks both of you for help.

Missinglinq,
that worked.
Can you please explain me what does that little code segment mean?
Expand|Select|Wrap|Line Numbers
  1. If me.dirty then
  2. me.dirty= false
  3. end if
  4.  
I appreciate your help
Feb 27 '09 #4

100+
P: 675
I notice that this code is always presented as:
Expand|Select|Wrap|Line Numbers
  1. If me.dirty then 
  2. me.dirty = false 
  3. end if
Why not:
Expand|Select|Wrap|Line Numbers
  1. 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?
Feb 27 '09 #5

missinglinq
Expert 2.5K+
P: 3,532
@OldBirdman
No reason, really, except that its intent is made clearer by adding 16 characters.

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

Linq ;0)>
Feb 27 '09 #6

100+
P: 675
OK. I just thought maybe there was a large overhead issue involved as the answers were so consistant.
Feb 27 '09 #7

missinglinq
Expert 2.5K+
P: 3,532
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)>
Feb 28 '09 #8

100+
P: 675
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.
Feb 28 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.