 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Update query only works the first time
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. 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 >I am having a problem with an update query for an inventory control form. > [quoted text clipped - 19 lines] > > Thank you. 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 > [quoted text clipped - 33 lines] > > > > Thank you. 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: > [quoted text clipped - 83 lines] >> > >> > Thank you. >Here is the error message I receive: > [quoted text clipped - 30 lines] > >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] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.