I have a query that produces about 30 records. One of the fields is BOMID and shows up as either the needed BOMID, or Null. Is there a way to change all of the records BOMID's to the correct BOMID (listed in BOMID textbox on the form) either AFTER the query has been run, or when an individual record from the query is updated?
Example: My query is based off of bills of materials that all match an ID (BOMID) listed on the form. But when I run the query I have it show all of those items that match, as well as those that are null. Once these are shown on the query, I need to either change the records that have a null BOMID field to match the current BOMID, or have it update to the correct BOMID when each record is edited.
How does one go about updateing a specific field in all of the records AFter the query has run it's course?
If you do not want the Null values, include the expression "Is Not Null" under the BOMID field.
If you want to update the null values to a specific value, use a DoCmd.RunSQL in code behind the form and then requery the combo box. To get help on the context of the SQL statement, create a new a query in design view, make it an "Update Query", set it up the way you want it and then switch to SQL view.
The help files provide a good resource for the RunSQL command, the Requery action and Update Queries.
Hope this helps - if not, please try to explain what you want again (I'm a little slow this evening!)
I think you might ahve it with the DoCmd.runSQL thing, I've never used it before, so I will have to check into that.
Basically, I have a full list of my items in my databse, preset with null, so that i can pull up my entire inventory when i'm preparing a new order.
My idea was that I could pull up the inventory using the null field, then when I fill in the order, the BOMID field will change from null to the order number and I add this new line to the database. This way I can keep opening new orders with a full list of items, and also keep track of, and edit the orders I've entered into the system.
I'm hoping this is the right idea to follow.