Updating Multiple Records at Once Using a Form?

[CaffeinatedOfficeWorker]

Hi, I'm wondering if there is a simple way to pick and choose a batch of records from a table and then update them all to the same status using a form?




For example, I have a database that tracks incoming and outgoing widgets and the dates we receive or send them. These parts show up around 20 or 30 at a time. Each one of the parts has a separate serial number in this database which is on the upwards of a few thousand items. I want users to use a form in order to pick these 20 or 30 serial numbers and change their statuses to "received" without having to go one by one. I have heard that an update query may be best approach to solving this issue.

Please advise on the best way to approach this.


Thanks!

[June7]

Each serial number has a few thousand related 'items' that need to be updated?

VBA code could run UPDATE sql on those serial numbers that are selected by a check in a bound field. Then run an UPDATE to reset the check field as unchecked for all records.

[CaffeinatedOfficeWorker]

No, there are a few thousand parts/items TOTAL in the database. We only ship/receive the parts in batches of about 20 or 30 each day.

However, after I made this post I was messing around with Access and discovered a somewhat easy way to do it using this SQL code:

UPDATE [TemporaryTble], MasterTble SET MasterTble.[FieldNeedingUpdate] = [TemporaryTble].[FieldYouWantTheUpdateMadeFrom] WHERE (((MasterTble.[Serial Number])=[TemporaryTble].[Serial Number]);


Basically, this code is an update query that takes the serial numbers of the parts that I input into a "temporary table" and finds them in a "master table" and then updates each item's ship/receive date. I put this temporary table into a subform for end users to input serial numbers of the parts they want updated. Then the users just click an "update" button that runs a macro triggering this update query and also a macro to delete the data in the "temporary table" after their done.

However, if someone has another idea on doing this in a faster manner then I'd still like to hear it.


Thanks!

[June7]

What is this 'master' table? Just a list of all available parts/items? Are you maintaining a history of all shipments? I am not understanding why you are updating a 'master' table? An alternative is instead of inputting serial numbers into a temp table, check a yes/no field of the 'master' table to indicate which records need date updated.

Want to provide db for analysis? Follow instructions at bottom of my post.

[CaffeinatedOfficeWorker]

Yes, the master table contains all the parts (serial numbers, dates to and from each shop, comments, etc.).

I actually thought about using the check box idea but I wanted users to be able to manipulate the dates received or shipped. If I used the check box idea then I assume that I'd have to set-up a timestamp of some sort which would cause users to be doing twice the work if they had to change to an earlier date. Currently, the date is automatically "today's date" but they have the option to alter it if they get to work on Monday or something and need to indicate that a part was received on Saturday. So all they have to do is just enter each parts serial number and they're good to go.

I would like to keep a comprehensive history of all transactions of the parts. However, after each part makes its round through our shops the program starts overwriting the old data. So the only thing we know is the current state of the part. That suffices but it'd be nice to know all the history each part has gone through. Is there a way for the parts to keep their ID's (AutoNumber) but create a new line within the record so that a history of each part could be kept?

[June7]

The update action would use the check field as criteria to determine which records to revise date, using whatever date is specified.

To maintain history you need more tables, possibly:

1. a 'transactions' table that will record shipment ID, shipment date, supplier/customer ID, supplier/customer document reference etc.

2. a junction table that will associate the shipment with serial numbers