Multiple Records on One Form

[brharrii]

I have an idea in my head of what I want to do, I'm hoping I can effectively communicate what that is. My terminology may not be perfect, so I appreciate your patience

I'm making a database that will store all of our micro bio testing results. In a given day multiple tests may be run. What I'd like to do is setup a form that allows me to input the results of each test run for the day into a single form.

I imagine the form to look something like this:

FrmMicroTesting
- Date
- Time
- Invoice Number

- LotNumber (1st instance)
- Grade (1st instance)
- Test (1st instance)
- Results (1st instance)

- LotNumber (2nd instance)
- Grade (2nd instance)
- Test (2nd instance)
- Results (2nd instance)

- LotNumber (3rd instance)
- Grade (3rd instance)
- Test (3rd instance)
- Results (3rd instance)
etc

I may have up to 5-10 instances on a single form that need to be reported. Is it possible to create a form that carries multiple values for a single field like that (multiple lot numbers, with their respective tests and results)?

Thanks

 

[boblarson]

Don't design the tables to fit the form. Design the PROPER data structure first and then worry about the form. In your case it would be something like this (remember this is a simple example and I don't know all of your business requirements):

tblMicroTestingHeader
TestHeaderID - Autonumber (PK)
TestDateTime
InvoiceNumber

tblMicroTestingDetails
TestDetailID - Autonumber(PK)
TestHeaderID - Long Integer (FK - from tblMicroTestingHeader table)
LotNumber
Grade
TestID

tblTests
TestID - Autonumber (PK)
TestDescription - Text


And that way you would have a main form based on the tblMicroTestingHeader table and then a SUBFORM based on the details table. It would be linked via the subform's Master / Child properties on the TestHeaderID and then you can have as few or as many tests as necessary.

 

[brharrii]

Thanks for your response.

I have my tables setup like what you are suggesting. I have the relationships setup as you suggested as well. I'm at the point where I need to build the form / subform.

I've never done anything with Subforms before. I wonder if you could give me a brief idea on how to put it together?

Thank you again!

 

[boblarson]

1. First select the table for the main form and click the CREATE FORM and let the wizard do it.

2. Next select the details table and do Create Form selecting Datasheet or Tab view as the type.

3. After both of those are created, open the main form in design view and drag and drop the details form onto the main form. It should automatically link them up.

And there you go. You will probably need to change the text box for the TestID on the details form to a combo (you can do that by right-clicking on it and selecting CHANGE TO > Combo box) and then you need to click on the Row Source property in the properties dialog and click the ellipsis (...) that appears. Select the tblTests table and both fields (the ID field first). Then change the combo's Column Count property to 2 and the Column Widths property to 0";2" so the ID field won't show but that will be what gets stored in that TestID field in the details table.