Inventory table slow

[swas]

Hi,

I have an inventory table with each record having ProductID, OpenStock,
Adjustment, Reason, ClosingStock.

To find the current stock level for a product I am using a query to return
the last entry for each pruduct, called qryCurrentStock. Because this query
is info only, it is marked as snapshot.

My problem is that whenever I want to use this query for a form etc... it
renders the form query uneditable, even if I only want to edit data in an
associated table only. Inconsistent updates makes no difference. Remove
qryCurrentStock and use Dlookup solves the problem but is really really slow.

Can anyone suggest how this is best handled? I'm trying to do a physical
stocktake form. Calculations for outers etc... are so slow. I need current
outers calculated from stock, then editable to enter new value.

Any help appreciated.


swas

 

[Michel Walsh]

A snapshot is generally done by making a copy the data of the whole table,
at that moment, and then, you play, as if you were the only user, with that
copy. What can be slow is to make the copy. Do you really need a snapshot?
Can you test with something else, instead, just for testing, and see if
there is room for improvement there?


Vanderghast, Access MVP

 

[swas]

Michel,

The current stock level query needed to retrieve the last entry to the
inventory table is a totals / group by query. Include this query as part of
another query for a form, and the recordset is not editable, even though I
only want the stock level as information only.

Because it is a totals query I have assumed it would make no difference it
is a snapshot. I will look at this.


Thanks

swas

 

[Michel Walsh]

A query with a GROUP BY clause is not updateable, indeed.


Hoping it may help,
Vanderghast, Access MVP