Inventory table slow

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

"swas" wrote in message
...
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,

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" wrote:

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


Hoping it may help,
Vanderghast, Access MVP


"swas" wrote in message
...

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" wrote:

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" wrote in message
...
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