Append query record count off by 1

G

Glenn Suggs

I have an append query that I run from code within a form. The query adds
records to an empty table and then another code module processes the records.
However, every time I run the procedure from the form, I get one less record
than what was selected on the form. (Check boxes mark records for selection)
If I select 1 record, I get zero. If I select 4 records, I get 3. But when
I run the append query "stand-alone", the record count is correct and then
running the code module by itself processes correctly. Does anyone have any
ideas as to what might be causing this? I've exhausted all the debugging
techniques I know of.

Thanks in advance,
 
D

David Lloyd

Glenn:

You may want to post the procedure that updates the table. Without seeing
the code it is difficult to make an accurate assessment.

That being said, if you are using a loop to perform the append for each
record, you may want to set a break point inside the loop and see how many
times it executes, and for which records.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have an append query that I run from code within a form. The query adds
records to an empty table and then another code module processes the
records.
However, every time I run the procedure from the form, I get one less
record
than what was selected on the form. (Check boxes mark records for
selection)
If I select 1 record, I get zero. If I select 4 records, I get 3. But
when
I run the append query "stand-alone", the record count is correct and then
running the code module by itself processes correctly. Does anyone have any
ideas as to what might be causing this? I've exhausted all the debugging
techniques I know of.

Thanks in advance,
 
J

John Vinson

I have an append query that I run from code within a form. The query adds
records to an empty table and then another code module processes the records.
However, every time I run the procedure from the form, I get one less record
than what was selected on the form. (Check boxes mark records for selection)
If I select 1 record, I get zero. If I select 4 records, I get 3. But when
I run the append query "stand-alone", the record count is correct and then
running the code module by itself processes correctly. Does anyone have any
ideas as to what might be causing this? I've exhausted all the debugging
techniques I know of.

Thanks in advance,
Click to expand...

David's suggestions are just what I would have said - but in addition,
note that if you're editing a record on the form, that record will NOT
have been saved to the table when you run the query, unless you take
specific action to do so. You can force a save of the record using
either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

John W. Vinson[MVP]
 
J

John Vinson

VBA Code
' Append selected locations to work order table
DoCmd.OpenQuery "qryGetNewWorkOrdersScheduled", acViewNormal, acEdit
Click to expand...

If the Form which executes this code is updating
tblNewWorkOrdersVaultSelect, add one line before the OpenQuery line:

DoCmd.RunCommand acCmdSaveRecord

As I've said a couple of times, the most likely reason the query is
returning one too few records is that you have one record *in the
process of being changed* - the one that's currently onscreen. It's
still on the screen; it's not stored in your table, so it won't show
up on the report.

John W. Vinson[MVP]
 
Top