Display a one to many... (1 Viewer)

[Lissa]

Hi Everyone -
I'm kind of stumped at the moment and I'm trying to think of more ideas to solve my problem. The database I built is used for tracking inspections on various hardware parts that we use to build our product. I have a form that displays inspection details and a subform to that form that displays results such as the serial number inspected, whether it was accepted or rejected after inspection, whether it was physically inspected by the inspector and in some cases if it was rejected, an issue is written up about it (i.e. 2009-NMR-XXXX) I have attached a jpg of the subform.
My problem is that I would like to have the issues ("NMR" column) to appear as a concatenated list in the NMR field (2009-NMR-XXXXX, 2009-NMR-XXXXX, 2009-NMR-XXXXX- etc). The way it's displayed now, the serial number is repeated for each issue number which creates unecessary rows since the serial number is the same - this also thows the number accepted/rejected.
In the underlying query, the issue number resides in a single table of another database which I link to. The remaining fields are another table in my database. The two tables are then joined by the serial number...
I know I will have to write some VB to create the concatenated list but I'm not sure where to put that code. In an event on the subform?


Any ideas are appreciated!
Thanks

 

[Kryst51]

What does your main form look like? How are they related?

Are you saying that you want only one line, and because of the NMR, you have 6 lines?

I am curious about your table structure, could you post your db with some dummy info in it? It would help to have a better idea of your structure.

 

[Lissa]

Hi Kryst51-
I can't post the DB but I'm just dealing with two tables. (tblPartStatus, tblNMR) The rest of the form is just a series of Yes/No dropdowns that the inspector answers. The dropdown fields are in the same table as serialnumber, accept, reject...

Yes I would like one line to appear... but NMR field causes 6 rows to appear becuase of the relation.

What I'm trying right now is removing NMR from the second table. In the event of the subform, I'm writing code to lookup the NMRs associated with the serial number, concatenating that list and then I'm going to update that list in a new field in the tblPartStatus. Maybe that will work.

 

[Kryst51]

I honestly don't know how to help, as I can't tell what your structure is....

What is the recordsource for your subform?

Is it a table? Is it showing you what your table would be showing you? If so, your structure might need some refining to actually reflect what you want, that's why it would be nice to see it, even if you cleared out all the data and just posted the empty tables, and forms.

Another thought that has occurred to me, is maybe your using the subform incorrectly, if the dropdowns are on the main form, but are in the same table as the rest of the information, the use of a subform is not the way to go. A subform should be used for related information, say from another table. You might want to use a list box.....

If your NMR number is from a different table, then that could go into a subform with the accept, reject and serial number on the main form. Then use some design aspects to break the information up on you main form, like a box around information, or something like that...