A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Archived records copying multiple times



 
 
Thread Tools Display Modes
  #1  
Old August 25th, 2009, 01:01 PM posted to microsoft.public.access.gettingstarted
deb
external usenet poster
 
Posts: 898
Default Archived records copying multiple times

Why would the following query copy one record into the history file anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial, [Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC, ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!

  #2  
Old August 25th, 2009, 04:33 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Archived records copying multiple times

You've described "how" you are trying to do something (using an "INSERT
INTO"). Now, why?! What's the underlying business need driving you to make
a copy of a record in another table?

I ask, not out of idle curiosity, but because there may be other ways to
accomplish what is needed, if we only knew what it was!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Deb" wrote in message
...
Why would the following query copy one record into the history file
anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial,
[Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC, ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!



  #3  
Old August 25th, 2009, 04:56 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Archived records copying multiple times

If you just use the SELECT part of the query, how many records are returned?
I would guess several based on your criteria:
tbl_InventoryListing.NHA=Yes
OR
tbl_InventoryListing.Archive=Yes

Unless you have only one record with one or both those values checked, you are
going to get multiple records returned - any records with NHA = Yes plus any
records with Archive = Yes.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
Why would the following query copy one record into the history file anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial, [Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC, ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!

  #4  
Old August 25th, 2009, 06:41 PM posted to microsoft.public.access.gettingstarted
deb
external usenet poster
 
Posts: 898
Default Archived records copying multiple times

Hi Jeff:

Thanks for trying to help!

The client wants to be able to pull up records on every part, after it has
been shipped (sometimes the parts come back). NHA is "Next Higher Assembly".
Copying the records to a history file is purely a tracking thing, and
another query removes the copied records from the Inventory table.

"Jeff Boyce" wrote:

You've described "how" you are trying to do something (using an "INSERT
INTO"). Now, why?! What's the underlying business need driving you to make
a copy of a record in another table?

I ask, not out of idle curiosity, but because there may be other ways to
accomplish what is needed, if we only knew what it was!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Deb" wrote in message
...
Why would the following query copy one record into the history file
anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial,
[Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC, ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!




  #5  
Old August 25th, 2009, 06:49 PM posted to microsoft.public.access.gettingstarted
deb
external usenet poster
 
Posts: 898
Default Archived records copying multiple times

Hi John:

Thanks for responding!

If you select one record, the message box may come back and say "10 records
are being copied". I think the problem is in the Macro that runs the query.
Macros are not one of my strong points.

"John Spencer" wrote:

If you just use the SELECT part of the query, how many records are returned?
I would guess several based on your criteria:
tbl_InventoryListing.NHA=Yes
OR
tbl_InventoryListing.Archive=Yes

Unless you have only one record with one or both those values checked, you are
going to get multiple records returned - any records with NHA = Yes plus any
records with Archive = Yes.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
Why would the following query copy one record into the history file anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial, [Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC, ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!


  #6  
Old August 25th, 2009, 07:43 PM posted to microsoft.public.access.gettingstarted
deb
external usenet poster
 
Posts: 898
Default Archived records copying multiple times

Here is the deal. On my computers, the program works fine. However, on the
client's system, I split the database (tables separated from program). Don't
know if that has anything to do with the issue or not.

I went to the client's office this morning, and selected a record to copy to
the archive table. The message box came up and indicated that 10 records
would be copied to the archive table. I checked the archive table, and every
record in there was duplicated at least once, most were duplicated three,
four and five times.

You've seen the query to copy the records into the archive file. I
developed a macro (very simple macro) which runs the query to copy the
records and another query to delete the records from the inventory table.

Breakdown of the macro:

Hourglass
SetWarnings
Echo
OpenQuery - qapp_HistoryQuery
OpenQuery - qapp_HistoryDelete
MsgBox - Records archived to History File

I know very little about macros - but don't know if that's the problem or not.
  #7  
Old August 25th, 2009, 09:49 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Archived records copying multiple times

Just to confirm, if you run this query it only returns one record

SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Is that right?

As far as the macro goes, it should run the queries one time when you execute
the macro. One problem is that if you have an error occur then you won't know
about it and you should have at least another SetWarnings call to turn warning
back on.

One problem I can guess at is that it is possible that qapp_HistoryDelete is
not cleaning up the way you think it is and you are appending the same records
over and over into tbl_history.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Deb wrote:
Here is the deal. On my computers, the program works fine. However, on the
client's system, I split the database (tables separated from program). Don't
know if that has anything to do with the issue or not.

I went to the client's office this morning, and selected a record to copy to
the archive table. The message box came up and indicated that 10 records
would be copied to the archive table. I checked the archive table, and every
record in there was duplicated at least once, most were duplicated three,
four and five times.

You've seen the query to copy the records into the archive file. I
developed a macro (very simple macro) which runs the query to copy the
records and another query to delete the records from the inventory table.

Breakdown of the macro:

Hourglass
SetWarnings
Echo
OpenQuery - qapp_HistoryQuery
OpenQuery - qapp_HistoryDelete
MsgBox - Records archived to History File

I know very little about macros - but don't know if that's the problem or not.

  #8  
Old August 25th, 2009, 11:36 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Archived records copying multiple times

Adding records to one table and removing them from another is very much like
how you'd need to do something like this ... if you were limited to using a
spreadsheet.

Since Access is a relational database, it not only isn't necessary, it's not
even a very good idea.

Instead, consider adding a single field to that table that will hold a
date/time value. As in "when did it ship"...

Now, if you want to see those that haven't shipped, change your query to
find those without a (past) date in the "when did it ship" field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Deb" wrote in message
...
Hi Jeff:

Thanks for trying to help!

The client wants to be able to pull up records on every part, after it has
been shipped (sometimes the parts come back). NHA is "Next Higher
Assembly".
Copying the records to a history file is purely a tracking thing, and
another query removes the copied records from the Inventory table.

"Jeff Boyce" wrote:

You've described "how" you are trying to do something (using an "INSERT
INTO"). Now, why?! What's the underlying business need driving you to
make
a copy of a record in another table?

I ask, not out of idle curiosity, but because there may be other ways to
accomplish what is needed, if we only knew what it was!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Deb" wrote in message
...
Why would the following query copy one record into the history file
anywhere
from six to ten times?

INSERT INTO tbl_History ( ID, ADJTrans, DRMOTrans, RECTrans, SHPTrans,
ReceivedDate, ReceiptDoc, TransactionDate, QtyOnHand, UpdatedQty,
UnitofIssue, PartNo, CageCode, SupplySource, NIIN, UnitCost, Serial,
[Index],
ConditionCode, Program, Division, Purpose, EMR, Requisition, Remarks,
DateModified, EnteredBy, ShippingPriority, DestinationUIC,
ShippingMethod,
TrackingNumber, NHA, Archive )
SELECT tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC,
tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

Please help!






  #9  
Old August 26th, 2009, 01:52 AM posted to microsoft.public.access.gettingstarted
deb
external usenet poster
 
Posts: 898
Default Archived records copying multiple times

Hi John:

The "delete query" code is below. Additional questions and information are
below the delete query code.

DELETE tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

The macro returns one record for each record with either NHA or Archive
checked.

You may be right about the delete query (code above). When I glanced down
through the inventory table, I saw some records with those blocks checked,
and that might be the problem -- the copy to history query is working, but
the delete query is not. That may explain why one record was only duplicated
once (probably the last record archived), but most were duplicated multiple
times. I copied the database and split it on my computer to simulate the way
the client uses it. Checked a few boxes and ran the history query. The
records did not delete until I closed the form. Perhaps you can figure out
from the code above what is going wrong.

As far as the macro goes, where would I set additional SetWarnings? I'm
REALLY struggling with trying to understand macros!! Can you recommend a
macro training source?




  #10  
Old August 26th, 2009, 02:17 AM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 2,364
Default Archived records copying multiple times

Try this

DELETE DistinctRow *
FROM tbl_InventoryListing
WHERE tbl_InventoryListing.NHA=Yes OR
tbl_InventoryListing.Archive=Yes

Macros do the job, but you are usually better off with VBA code.

I don't know of any training source for macros.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Deb wrote:
Hi John:

The "delete query" code is below. Additional questions and information are
below the delete query code.

DELETE tbl_InventoryListing.ID, tbl_InventoryListing.ADJTrans,
tbl_InventoryListing.DRMOTrans, tbl_InventoryListing.RECTrans,
tbl_InventoryListing.SHPTrans, tbl_InventoryListing.ReceivedDate,
tbl_InventoryListing.ReceiptDoc, tbl_InventoryListing.TransactionDate,
tbl_InventoryListing.QtyOnHand, tbl_InventoryListing.UpdatedQty,
tbl_InventoryListing.UnitofIssue, tbl_InventoryListing.PartNo,
tbl_InventoryListing.CageCode, tbl_InventoryListing.SupplySource,
tbl_InventoryListing.NIIN, tbl_InventoryListing.UnitCost,
tbl_InventoryListing.Serial, tbl_InventoryListing.Index,
tbl_InventoryListing.ConditionCode, tbl_InventoryListing.Program,
tbl_InventoryListing.Division, tbl_InventoryListing.Purpose,
tbl_InventoryListing.EMR, tbl_InventoryListing.Requisition,
tbl_InventoryListing.Remarks, tbl_InventoryListing.DateModified,
tbl_InventoryListing.EnteredBy, tbl_InventoryListing.ShippingPriority,
tbl_InventoryListing.DestinationUIC, tbl_InventoryListing.ShippingMethod,
tbl_InventoryListing.TrackingNumber, tbl_InventoryListing.NHA,
tbl_InventoryListing.Archive
FROM tbl_InventoryListing
WHERE (((tbl_InventoryListing.NHA)=Yes)) OR
(((tbl_InventoryListing.Archive)=Yes));

The macro returns one record for each record with either NHA or Archive
checked.

You may be right about the delete query (code above). When I glanced down
through the inventory table, I saw some records with those blocks checked,
and that might be the problem -- the copy to history query is working, but
the delete query is not. That may explain why one record was only duplicated
once (probably the last record archived), but most were duplicated multiple
times. I copied the database and split it on my computer to simulate the way
the client uses it. Checked a few boxes and ran the history query. The
records did not delete until I closed the form. Perhaps you can figure out
from the code above what is going wrong.

As far as the macro goes, where would I set additional SetWarnings? I'm
REALLY struggling with trying to understand macros!! Can you recommend a
macro training source?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.