Multi-record form

[dougie]

I have a table containing a list of manufacturing cells, each cell responsible for a particular range of products. To record daily production I want the user to select a cell id from a drop-down, for a subform then to be displayed listing every product in that cell, with space beside to enter a quantity produced. These products/quantities are then posted to a production file for later reporting. What's the best way to do this? I know I could just hard code each list of products on to a different form, but I want the arrangement to be dynamic so that changes in the cell structure or allocation of products to cells does not affect the form design.



(I'm replacing an Excel spreadsheet with an Access database, and want the Access to be at least as quick and easy to use as the Excel).

[aytee111]

Table 1 - cells
Table 2 - products
Table 3 - cell#and product# (cross-reference link)
(this structure, although not required, will give the best long-term performance for "normalization" and determining which products are current and what cells make them. You can move products around, discontinue them, etc, without any hassle)

Table 4 - production: cell#, product#, date, quantity - the primary key will be cell#, product# and date

Form: record source would be the cell table
have a combo box with the cell information for them to select
they also need to select a date - the form can default to today's date

Subform: record source would be the table 4
link the subform using the cell# and date from the main form which will display the records for all products with today's date
if no records exist, you can have an append query to append them all with zero in the qty field (i.e. the first time they come to this data)

PS Access is not quicker or easier than Excel for the user. Where Access is so superior is in its record-keeping and ability to keep data forever and to report on it in many different ways. Management are the winners with Access, the users generally are not.

[dougie]

Many thanks - I'll try that out. I've been creating a temporary table containing the units produced by a cell and making that the record source for the subform. There is a 'post' button which then appends this to the main production table. This works quite well but the effects of a failure in mid-transaction are a bit unpredictable.

"PS Access is not quicker or easier than Excel for the user. Where Access is so superior is in its record-keeping and ability to keep data forever and to report on it in many different ways. Management are the winners with Access, the users generally are not. "

I know - the main gist of this development is to tidy up and extend performance reporting which at the moment is time-consuming and messy (and often just plain wrong). It's good PR, though, to bear the poor user in mind and make data entry as painless as possible. I'll be able to show a significant saving in their time as at present they have to manipulate many different spreadsheets and sources of data in order to get reports and they're not adequately skilled in Excel to do this quickly or safely. Access requires much lower skill levels for data entry and all routine reporting thereafter can just be picks from a menu.