Combo Box To Populate Subform?

[DerekK]

I have a form which is used to add measurements taken from various instruments.

Because some instruments have different parameters and even a different number of parameters, I figured a subform was the way to go. As the parameters are specific, it is important to ensure that these are entered correctly.

E.g.:



(Main Form, Record 1):

• Instrument A
• [Other information about Instrument A]

(Subform):

1. Temperature
2. Relative Humidity

(Main Form, Record 2):

• Instrument B
• [Other information about Instrument B]

(Subform):

1. Voltage
2. Current
3. Electrical Resistance

Therefore, what is the best way to go about populating the subform from, for instance, a combo box? Or, if there's another way altogether, I'm all ears.

Hope you can help!

 

[Micron]

Many ways you could approach this but I'm assuming you don't want to use free form typing as that would allow spelling mistakes. I might use a listbox on the main or subform (sf) but not on sf if its continuous or datasheet. Its rowsource would be a query that returns parameters for the main form Instrument. The listbox allows for multi-select and looping over selected so as to populate sf. That solution would require code.

A combobox will only permit you to make one selection at a time. Either way, preventing multiple records with the same parameter values for any one Instrument could be handled by creating a 2 (?) field index on your table - Instrument and Parameter, but not sure how you'd handle that interruption in the code as well. A better way might be that the listbox query omits anything already found in the subform.

 

[DerekK]

Thank you kindly for your response @Micron , I'll take a crack at it.

The listbox allows for multi-select and looping over selected so as to populate sf. That solution would require code.

Please can you let me know what kind of (VBA) code or where I should start looking? This isn't my cheap way of trying to get someone to do my work for me, I'm just a bit new to Access.

 

[Micron]

ms access vba loop through selected items in listbox

using this term in google I got 57 results, which is a lot less than I expected. One of them is M$ forums, which I looked at. Simple and short but to my surprise, uses what I'd call reserved word (item). So I'll include a link for that.

To my big surprise, "item" is not in the list for the link I gave, so I checked the object browser and "item" is a member of so many Access classes that I stopped counting at 50. All I can say is that I wouldn't use it for anything that I created. Let us know how you get on. Some options I imagine:
- as you loop over selected items you'll be either running an update query using each selected item
- or you can execute update sql in your code
- or you can create a recordset based on your table and update the recordset (don't forget to use .Update on it after .Edit)
At the end of whatever that process is, you'd Requery the subform.