Is it possible for a form to be updated based on data in a field?

[Perimeter]

This is purely a cosmetic, preference type thought and would like to know what the experts here would say.

I have an "Update form" that Upon searching for and selecting a unique Serial Number it displays the data of the record allowing only edits to certain fields.
Works great for 99% of our records.

In this form there is a section displaying "Fail Date Fail Code Reason and QTY"

We do have a few products from time to time that have multiple failures.

Is there a way upon selecting the serial number and Access populating the fields listed to check if there is data in the 2nd failure, 3rd failure, etc etc and have it auto-magically expand the form to include that data?

I threw together a pic of the standard form listing a single failure (like i mentioned this is 99% of my database)
and another showing what i'd like to see after selecting a serial number that has 3 failures.
Obviously all these fields would have data in them..




Any insight would be great.
It should also be noted that I am fairly new to Access, so a minor breakdown of the process, if there is one, would be appreciated.

[Bob Fitz]

Yes it is possible. It is usually done using tables in a one to many relationship. In tis case you would have two tables. The first, a table with data about Serial Numbers would represent the "one" part of the relationship. You might call this tblSerialNums. It might have fields called:
SerialID (Auto-number, Primary Key)
SerialNum (short text)
ModNum (short text)

The other table might be called tblFails. It might have fields called:
FailID (Auto-number, Primary Key)
SerialID (number) (foreign key to link to tblSerialNums)
FailDate (date)
Qty (Number)
FailCode (short text)
Reason (short text)

Read up on relationships. Post back if you need further help.

[Gicu]

I assume you already have a table structure similar to the one described by Bob and not a table containing multiple fields for the same entity (Failure1,Failure2, etc.). So to make the form behave like you want you need to remove Fail Date, Fail Code, Reason and Quantity fields from the form and move them to a new one that you embed as a subform linked to the main form via the Serial Number. Set the form to continuous or datasheet and now it will show you all the failure records for the selected serial #.

Cheers,

[moke123]

Another option is using a listbox.