1 form to multiple tables

[anissw]

Hi- I currently have 1 form linked to 1 table which data from another table is appended to for editing, re-appended to original table and then rows are deleted. However, this process will need to be done by 4 other users soon and I needed to know if I need to create 4 separate tables and 4 separate forms or is there another way to implement this? The append query has clauses that will display only a specific user data for editing, however, I do believe that if 1 user is in the editing table, then when 1 of the other 4 users attempt to run the query to append their data from the original table, they will get an error message that this table is already in use. I just need to know if there is a way to set up an alternative to re-creating 4 tables/4 forms again.

Thanks-

anissw

 

[David R]

Can you explain the purpose of the interim table? Do you need some sort of record of the original data?

 

[anissw]

Hi David- Thanks for replying. I do need the original record for conducting invoicing/billing. The original table is updated from the raw imports that runs on a task scheduler every night importing data from another data source. Orders from those raw tables are imported for previous day, calculated, and appended to the original table and are stored to the original table for up to 40 days. The original table also have special orders that are not captured from the raw tables that requires the editing for appending the updates to separate fields in the original table. The table for appending to the original table I refer to as exception table (flags of order that require reviewing by a manager). This table will be generated by 4 different users in 4 different sites (1 per site) and if the exception table is currently in use by 1 user, this could give an error that the table is in use (the exception table is what I refer to as a temp table since the records are deleted once they have completed reviewing and updating). Sorry if I am too chatty.

 

[David R]

Okay thanks, that clarifies a great deal about your process.

What I would probably do is this:

1. Set up your import table with a blank 'Editor' field.
2. Have all 4 users access the same table. However force your form to only show them *1* "special order" at a time by using a button to 'assign' it to them (via Update Query on that 'Editor' field). You'll have to figure out a way to error-trap or unassign it if they hit a snag or cannot finish it!
3. Once they have 'picked up' the oldest unassigned special order, have a Complete Special Order button that appends this record into your 'real' orders table, AND deletes/checks off the special order.
4. Then they can load a new special order, or close it out, or whatever...

Make sense? I may be missing a few steps of your process but that should be enough to get you started...

 

[anissw]

Hi David- I apologize for getting back to you so late. I will give this a try tonight and let you know how it goes.