Code to query and return values???

[jmbcreative]

Here's a difficult one.

I have a table that has controlID's with just numbers llike "325". I also have a table that has the controlID's that have the same numbers only each each of those have a letter at the end like "325a", "325b" and so on. Basically that is how the records in the two tables are associated with each other.

I have created a form that has the controlID (with the numbers only) field in it. I need a command button on that form to get the value that is populating that controlID field "325", query the second table that has the controlID's with the letters at the end (ie."325a", "325b") and return all of those values into a field on my form titled "component".

Kinda like below if it helps:
System ControlID Components controlID's
325 325a, 325b

I'm not sure where to start with this one. Anybody have any ideas?

 

[stix4t2]

You could try building the query to select the control ID as the first field and set the second field to a public function. Pass the function the controlID so it can build a SQL statement to pull the associated records and concat their values. If you search this site you will find reference to concating fields.

Keep in mind that if the result query is returning a lot of System Control ID's then performance may suffer. Alternates at this point is using code to build a temp table and using the temp table as the source for the form.

 

[IanAble]

If "System ControlID" is the record identifier on table 1 and "Components ControlID" is the record identifier on table 2, the both those fields should be primary keys in their respective tables and "System ControlID" should appear in table 2 as a foreign key field. The you can create a one to many relationship between "System ControlID" fields in tables 1 and 2.

With this relationship you can show table 2 records in a linked subform. This can be shown/hidden using a command button.