Inventory table slow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
A query with a GROUP BY clause is not updateable, indeed.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top