Populating combo box with check box captions

[intergnat1]

Hi All,

My knowledge of VBA and Access 2003 is fairly limited so please bear with me:

I am creating a repair invoice database with a form that I will use to record cameras taken in and accessories that came in with the camera. Right now I have an array of 13 check boxes representing the different accessories and the user will check the box of each accessory that arrived with the camera. Once this portion is completed, I would like to populate a combo box on a separate form with a list of all of the accessories that were received with the camera.

I thought of creating a recordset from a query checking the values of the 13 check boxes but I'm not quite sure where to go from there. Also, since each check box is a yes/no value, I thought about possibly referencing the caption of each control to actually populate the combo box... am I over-thinking this?

Thanks for your help!

Nick

[ADezii]

1. Create a Query that consists of the Camera Make/Model along with the 13 Yes/No Fields representing the Bound Check Boxes on your Form.
2. Using the Combo Box Wizard, create a Combo Box based on this Query.
3. Set the Format Property of the Combo Box to Yes/No.
4. Set the Column Heads Property = Yes to identify the Accessory Fields.
5. When you now Open the Combo Box, the Camera Make/Model along with the 13 Accessory Options will be displayed. If a Camera contains an Accessory a Yes will appear, otherwise a No.

[intergnat1]

Thanks ADezii for the quick response. I tried your suggestion but it didn't quite give me the results I was looking for. I thought I might try something like:

SELECT qryAccessories WHERE [qryAccessories].[???] = true

Which would look at those 13 controls and only pick the ones that were true. Then I wanted to reference the captions for each of the controls that were true and actually populate the combobox with the names of those accessories.

So each camera would have it's own associated accessory combobox that listed the actual names of the accessories that it came in with.

[ADezii]

So you want a Combo Box for each Camera and this Combo Box will display only the Accessories that are selected for that Camera? This is a rather odd request, and I want to be absolutely sure that I am crystal clear on it. How many Cameras are we talking about? Why a single Combo fora each Camera?

[intergnat1]

The database tracks repair invoices for the company. I have a main table called "Invoices" which stores basic info like date and number of invoice, notes, totals, etc. and another table called "Invoice Details" which contains specific cameras and their accessories taken in and associated with the invoice (related by Invoice Number). "Invoice Details" also contains pricing for repairs, condition of camera, etc. So, in essence, each Invoice can have any number of cameras associated with it and each camera can potentially come with up to 13 different accessories.

To display all of this mess, I've got a main form called "New Invoice" and a subform called "Invoice Details" which displays, in datasheet view, each camera associated with the invoice along with it's specific accessories, repair cost, etc. When I add a camera to this invoice, i open a third form called "Add Equipment" which uses the "Invoice Details" table as form source, and has text boxes for entering camera make/model, repair cost, and the array of 13 check boxes representing the accessories.

So the reason i would like each camera entry to have a combobox is that it would make for easy review of accessories in the subform "Invoice Details."

I hope this helps explain my goal. Thanks for your patience!

Nick

[ADezii]

You are quite welcome. Download the following Attachment and let me know if the Logic is correct, forget the formatting for now. A Combo Box will be designated for each Camera Make/Model and only those Accessories that are 'Checked' in the underlying Table will be displayed. It assumes, of course, that each Camera's Make/Model is unique.

[intergnat1]

Thanks again for your help ADezii! The logic looks correct to me (i say "looks" because I'm still a newbie and still have a tough time deciphering some code). I'll play with it some more using your code as a guide and see what I come up with.

Much appreciated,

Nick