Suggestions wanted: easy workflow for multiple related tables

[Richmilnix]

For an inventory management database, I'd like my main screen to be able to accept pulls from three related databases:

- Serialized (valuable tools, which I track and want back - the paintbrush);
- Replenishable (cheap tools that I buy by the yard and if one gets left behind I don't care - the paint-mix sticks);
- Perishable  (disposable tools that are left on the job site - the paint).

These will handle their related requests differently. The serialized I'll be tracking properly to make sure we keep track; the latter two I'm looking for more like a system of 'tick marks' to help me know what we use on job sites.

We print our barcodes. For ease of use, I'd like staff to scan into a single field in an interface that is smart enough to do the right thing based on a key digit in the barcode. So when I scan barcode 'S1234', my db knows to move it to the Serialized relation field; when I scan 'P1234,' it knows we're in Perishables.

Looking for the elegant approach. My thought is that that central hot field (Scan_Here) will, when entered, store a variable and trigger a script built within the context of the related table, but I'm sometimes prone to reinventing wheels and doing stuff the long way, so I'm asking for suggestions / examples.

[the Otter]

Since it’s been a few weeks since you posted this, perhaps you’ve already solved the problem, but I would personally use a transparent slide control with four panels: a blank panel, visible by default; and a panel for each request type. Make sure each panel has a name, then have the script switch to the appropriate panel based on which type of barcode is being scanned. The fields will appear and disappear as appropriate. (You can also use hiding, but panels are a lot easier to manage.)