Append query record count off by 1

[Guest]

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,

 

[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,

 

[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,

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]

 

[Guest]

Actually it's an append query that I'm using to add records to the empty
table. Records get selected on the form via check boxes and the append query
was written in design view, called from code, but not coded to actually add
records other than doing an OpenQuery call. The append query selects records
that have been checked (equal to -1 in the check box field) but always pick
up one less than the number actually selected.

 

[Guest]

Actually it's an append query that I'm using to add records to the empty
table. Records get selected on the form via check boxes and the append query
was written in design view, called from code, but not coded to actually add
records other than doing an OpenQuery call. The append query selects records
that have been checked (equal to -1 in the check box field) but always pick
up one less than the number actually selected.

 

[John Vinson]

Actually it's an append query that I'm using to add records to the empty
table. Records get selected on the form via check boxes and the append query
was written in design view, called from code, but not coded to actually add
records other than doing an OpenQuery call. The append query selects records
that have been checked (equal to -1 in the check box field) but always pick
up one less than the number actually selected.

Please post your code and the SQL of the query. It still sounds like
the last record that you checked might not have yet been saved to
disk; if that is the case it wouldn't be included in the query.

John W. Vinson[MVP]

 

[Guest]

Here's the SQL followed by the VBA code that calls it:

INSERT INTO tblNewWorkOrdersVault ( TYPE, SER_NBR, NETWORK, VAULT, CIRCUIT,
DATE_GEN, DATE_COMP, STATUS, LOCATION, Foreman1, MaintenanceDate, CrewLeader,
Foreman, CrewHours, MaintenanceType, Water, Depth, Pumped, PumpedHours,
Cleaned, CleanHours, Grating, Hatch, Ladder, CableBrackets, Unistrut,
RoofSlab, Walls, Floor, DuctBay, SumpPump, VaultLights, BussFuses,
ServiceFuses, RTU, SecondarySize1, SecondarySize2, SecondarySize3,
LeadDamaged, InsulationCondition, CableTagged, LeadCableBondedAtDuctBay,
RepairRemarks, TripHazard, MaintReq, Foreman2 )
SELECT tblNewWorkOrdersVaultSelect.TYPE,
tblNewWorkOrdersVaultSelect.SER_NBR, tblNewWorkOrdersVaultSelect.NETWORK,
tblNewWorkOrdersVaultSelect.VAULT, tblNewWorkOrdersVaultSelect.CIRCUIT,
tblNewWorkOrdersVaultSelect.DATE_GEN, tblNewWorkOrdersVaultSelect.DATE_COMP,
tblNewWorkOrdersVaultSelect.STATUS, tblNewWorkOrdersVaultSelect.LOCATION,
tblNewWorkOrdersVaultSelect.Foreman1,
tblNewWorkOrdersVaultSelect.MaintenanceDate,
tblNewWorkOrdersVaultSelect.CrewLeader, tblNewWorkOrdersVaultSelect.Foreman,
tblNewWorkOrdersVaultSelect.CrewHours,
tblNewWorkOrdersVaultSelect.MaintenanceType,
tblNewWorkOrdersVaultSelect.Water, tblNewWorkOrdersVaultSelect.Depth,
tblNewWorkOrdersVaultSelect.Pumped, tblNewWorkOrdersVaultSelect.PumpedHours,
tblNewWorkOrdersVaultSelect.Cleaned, tblNewWorkOrdersVaultSelect.CleanHours,
tblNewWorkOrdersVaultSelect.Grating, tblNewWorkOrdersVaultSelect.Hatch,
tblNewWorkOrdersVaultSelect.Ladder,
tblNewWorkOrdersVaultSelect.CableBrackets,
tblNewWorkOrdersVaultSelect.Unistrut, tblNewWorkOrdersVaultSelect.RoofSlab,
tblNewWorkOrdersVaultSelect.Walls, tblNewWorkOrdersVaultSelect.Floor,
tblNewWorkOrdersVaultSelect.DuctBay, tblNewWorkOrdersVaultSelect.SumpPump,
tblNewWorkOrdersVaultSelect.VaultLights,
tblNewWorkOrdersVaultSelect.BussFuses,
tblNewWorkOrdersVaultSelect.ServiceFuses, tblNewWorkOrdersVaultSelect.RTU,
tblNewWorkOrdersVaultSelect.SecondarySize1,
tblNewWorkOrdersVaultSelect.SecondarySize2,
tblNewWorkOrdersVaultSelect.SecondarySize3,
tblNewWorkOrdersVaultSelect.LeadDamaged,
tblNewWorkOrdersVaultSelect.InsulationCondition,
tblNewWorkOrdersVaultSelect.CableTagged,
tblNewWorkOrdersVaultSelect.LeadCableBondedAtDuctBay,
tblNewWorkOrdersVaultSelect.RepairRemarks,
tblNewWorkOrdersVaultSelect.TripHazard, tblNewWorkOrdersVaultSelect.MaintReq,
tblNewWorkOrdersVaultSelect.Foreman2
FROM tblNewWorkOrdersVaultSelect
WHERE (((tblNewWorkOrdersVaultSelect.Selected)=-1))
WITH OWNERACCESS OPTION;

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