Two record pointers to same table with DAO recordset

[dhstein]

I'll try to make this clear without going into tremendous detail. The
database maintains items in a warehouse. When items are sold, it may be that
a product of large quantity will be divided into many units of a product of a
smaller quantity. This is happening on items that are being read from a
file. So as we read the input file and process the warehouse table we have a
pointer to the current record for the "large-quantity-item" in the warehouse
table. Now, I'll write a function that will search for the location of the
"Small-quantity-item" in the warehouse table. So that function is changing
the pointer to the warehouse table. So I need to maintain two pointers to
the same table at the same time (plus the pointer to the input file - but
this is not a problem). Can I do that? My table was opened with Set
rsWarehouse = CurrentDb.OpenRecordset("tblWarehouse"). Can I just do another
statement like Set rsWarehouse2 = CurrentDb.OpenRecordset("tblWarehouse") ?
Thanks for any help you can provide.

 

[Allen Browne]

Yes: you can open 2 recordsets into the same table.

It would be better to declare a Database variable, rather than 2 instances
of Currentdb, i.e.:
dim db As DAO.database
dim rs1 As DAO.Recordset
dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblWarehouse", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblWarehouse", dbOpenDynaset)

(I am suggesting dbOpenDynaset so the code does not break if you split the
database so that tblWarehouse is an attached table.)

The product-purchased-and-split-in-various-quantities issue might justify an
extra table, so you can specify the product itself, and the QuantityInPack
when buying/selling the product.

 

[dhstein]

Yes: you can open 2 recordsets into the same table.

It would be better to declare a Database variable, rather than 2 instances
of Currentdb, i.e.:
dim db As DAO.database
dim rs1 As DAO.Recordset
dim rs2 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("tblWarehouse", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tblWarehouse", dbOpenDynaset)

(I am suggesting dbOpenDynaset so the code does not break if you split the
database so that tblWarehouse is an attached table.)

The product-purchased-and-split-in-various-quantities issue might justify an
extra table, so you can specify the product itself, and the QuantityInPack
when buying/selling the product.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



Allen,

Thanks for your very clear and straighforward response. I do have another
table, the product table, which just defines product specific issues such as
Vendor. Each product is sold as either large quantities or smaller
quantities and as such they have specific warehouse locations and for all
intents and purposes they are separate products. So the warehouse table
keeps track of where they are. Eventually we'll have a mechanism that will
define the process of "splitting" the large quantities into the small
quantities, but for now we need to break them apart based on sales data and
just figure out the most likely scenario of how the split occurred. I also
need to check out your tips - there's probably lots of good stuff there.
G'day Mate.

David