Re: Update Query only works first time

Hi Jacine,

I don't know either -- but that is what you error message says -- perhaps you are not using an updateable recordset or records were locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not normalized

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

You should have a seperate table for BinLocation. It would be linked on PartID and could have 1, 6, or as many related records as you like.

"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."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:
No - I am the only one. I am still building the database. I went over all the fields in the table and there is nothing set up incorrectly - I don't know where the 35 records are coming from.

"strive4peace" <"strive4peace2006 at yaho" wrote:


Hi Jacine,

are other people using it? What would be causing the database to lock 35 records?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

I have a command button placed on the form to run the query. The form is built from a large parts table. This parts table is also used for several other forms for auto lookup and auto fill and another form as well. I'm not sure what you mean by another process.

Thank you.

"strive4peace" <"strive4peace2006 at yaho" wrote:



Hi Jacine,

how are you executing your queries?

your error message indicates that 35 records are locked, which is why they cannot be updated -- do you have another process which has these records tied up?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:


Hello,

Thank you for the answer, but I am not sure where to place this code. Could you provide more information?

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.

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.

Thank you.



"strive4peace" <"strive4peace2006 at yaho" wrote:




Hi Jacine,

after you run each action query that changes tables...

currentdb.tabledefs.refresh
doEvents

this will ensure that the changes show up right away so that the next query can work if it depends on the previous one


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:



Hello,

I am fairly new to Access and I am stuck on two issues.

I have an inventory form that has several bin locations with their own numerical field and another field for each bin for when they do a physical count.

I placed a command button to run an update query for each as follows:

1. To update the current bin quantities from the actual physical count field

2. Another to run another query to clear the actual physical count fields to 0.

It works the first time, but then there is a very long error message with several reasons why it does not work. When I close the form and open it again, it works again.

I tried the DoCmd.Requery but nothing happens. Another thing - when it does work the first time I get a message that it is about to update 4500 records. It only updates the current form, but I do have that many records.

Another query problem I'm am not able to figure out or know what to use is they have a minimum and maximum number for each item and want a query set up that alerts which items have reached that minimum / maximum number and am not sure how to go about it.

I tried searching for these answers but cannot find on-line.

Many Thanks,

.