Update query only works the first time

[Guest]

I am having a problem with an update query for an inventory control form.

There are several bin locations with a numeric field for how many there are
in each bin. Then there is another field for each bin for when they do a
physical count.

I have two update queries. One to update the amount in each bin from the
actual physical count and another update query to clear or reset the actual
physical count to zero.

They both work the first time, but then you have to close the form so that
it will work again. I tried the DoCmd.requery but that does not seem to work.

I also have another problem with another query. There is a maximum and a
minimum number for each item and they want to know when it reaches the min
and max for each item. I am not sure what to use in this query either.

I've searched for answers on-line but cannot seem to find the answer.

Thank you.

 

[Jeff Boyce]

Jacine

Queries are based on tables (and on other queries). Without an idea of how
your tables are structured, it will be a little tough to offer specific
suggestions.

Generally, when you say the updates don't work, what happens? Nothing, or
an error message, or wrong data being appended, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Here is the error message I receive:

Microsoft Access did not update 0 field(s) due to a type conversion failure,
0 record(s) due to key violations, 35 record(s) due to lock violations, and 0
Record(s) due to validation rule violations.

This occurs after I have run the Query the first time and try to run it again.

Here is the SQL text of the query as well:

UPDATE [Parts and Supplies] SET [Parts and Supplies].UnitsBinLocation1 =
([ActualQuantityBin1]), [Parts and Supplies].UnitsBinLocation2 =
([ActualQuantityBin2]), [Parts and Supplies].UnitsBinLocation3 =
([ActualQuantityBin3]), [Parts and Supplies].UnitsBinLocation4 =
([ActualQuantityBin4]), [Parts and Supplies].UnitsBinLocation5 =
([ActualQuantityBin5]), [Parts and Supplies].UnitsBinLocation6 =
([ActualQuantityBin6]);

When it does work, it updates the information only after you go to the next
record, or takes some time. It also give me a message that you are about to
update 4500 records which I don't understand since it only updates that
particular part or supply.

I indexed these fields and it runs a little quicker, but I still get the
same error message. I have to close the form and reopen to run the query
again. I tried the DoCmd.Requery but nothing happens.

I have another update query that clears or sets the Actual Quantity fields
back to 0 and the same thing happens.

This Parts and supplies table is a large table with over 4500 items. It is
also used as an auto-lookup for four other forms.

Thank you.

 

[Jeff Boyce]

I still don't have a good sense of how your data is structured/organized
into tables.

From the UPDATE SQL statement, it appears you have ... a spreadsheet! It is
not particularly good (nor efficient) relational database design to use
"repeating fields" (e.g., ActualQuantityBin1, ActualQuantityBin2, ...).

In fact, with a data design like this, every time your facility changes the
number of "bins", you'll have to modify your table structure, your queries,
your reports, your forms, and any code that refers to the data in the
"bins".

I'll strongly recommend that you spend some time looking into
"normalization" and get the data in a form that better lets you use Access'
features/functions. Or, if you don't need them, consider migrating the data
to Excel.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the error message I receive:

Microsoft Access did not update 0 field(s) due to a type conversion
failure,
0 record(s) due to key violations, 35 record(s) due to lock violations,
and 0
Record(s) due to validation rule violations.

This occurs after I have run the Query the first time and try to run it
again.

Here is the SQL text of the query as well:

UPDATE [Parts and Supplies] SET [Parts and Supplies].UnitsBinLocation1 =
([ActualQuantityBin1]), [Parts and Supplies].UnitsBinLocation2 =
([ActualQuantityBin2]), [Parts and Supplies].UnitsBinLocation3 =
([ActualQuantityBin3]), [Parts and Supplies].UnitsBinLocation4 =
([ActualQuantityBin4]), [Parts and Supplies].UnitsBinLocation5 =
([ActualQuantityBin5]), [Parts and Supplies].UnitsBinLocation6 =
([ActualQuantityBin6]);

When it does work, it updates the information only after you go to the
next
record, or takes some time. It also give me a message that you are about
to
update 4500 records which I don't understand since it only updates that
particular part or supply.

I indexed these fields and it runs a little quicker, but I still get the
same error message. I have to close the form and reopen to run the query
again. I tried the DoCmd.Requery but nothing happens.

I have another update query that clears or sets the Actual Quantity fields
back to 0 and the same thing happens.

This Parts and supplies table is a large table with over 4500 items. It
is
also used as an auto-lookup for four other forms.

Thank you.

Jacine

Queries are based on tables (and on other queries). Without an idea of
how
your tables are structured, it will be a little tough to offer specific
suggestions.

Generally, when you say the updates don't work, what happens? Nothing,
or
an error message, or wrong data being appended, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

Here is the error message I receive:

Microsoft Access did not update 0 field(s) due to a type conversion failure,
0 record(s) due to key violations, 35 record(s) due to lock violations, and 0
Record(s) due to validation rule violations.

This occurs after I have run the Query the first time and try to run it again.

Here is the SQL text of the query as well:

UPDATE [Parts and Supplies] SET [Parts and Supplies].UnitsBinLocation1 =
([ActualQuantityBin1]), [Parts and Supplies].UnitsBinLocation2 =
([ActualQuantityBin2]), [Parts and Supplies].UnitsBinLocation3 =
([ActualQuantityBin3]), [Parts and Supplies].UnitsBinLocation4 =
([ActualQuantityBin4]), [Parts and Supplies].UnitsBinLocation5 =
([ActualQuantityBin5]), [Parts and Supplies].UnitsBinLocation6 =
([ActualQuantityBin6]);

When it does work, it updates the information only after you go to the next
record, or takes some time. It also give me a message that you are about to
update 4500 records which I don't understand since it only updates that
particular part or supply.

I indexed these fields and it runs a little quicker, but I still get the
same error message. I have to close the form and reopen to run the query
again. I tried the DoCmd.Requery but nothing happens.

I have another update query that clears or sets the Actual Quantity fields
back to 0 and the same thing happens.

This Parts and supplies table is a large table with over 4500 items. It is
also used as an auto-lookup for four other forms.

Thank you.

Your Query does not have any criteria, so it's doing exactly what
you're asking it to do: updating all 4500 records, storing information
from six improperly normalized fields into six more improperly
normalized redundant fields. The fact that you're running the query
from a Form does NOT mean that the query will use only that form's
data, unless you *tell it to do so* by including a criterion
referencing the form!

In short - the main reason you're having trouble is that your table is
incorrectly designed! If you have multiple Bin Locations, you should
certainly have at least THREE tables: [PartsAndSupplies], [Bins], and
[BinLocations], so that if an item is in five bins, there would be
five RECORDS in the BinLocations table for that item. I'm not sure I
understand the functions of the ActualQuantityBinX and
UnitsBinLocationX fields so I can't comment other than to say that
storing the same information in two different fields is very likely to
be simply *wrong*!

John W. Vinson[MVP]