MultiSelect ListBox or CheckListBox to display/update a table

[GEAK]

I figured this would be a common problem but after searching/browsing a bit I was unable to find anything - sorry if this has already been answered elsewhere.

Ideally, I want a checklist box for this but couldn't find a built in implementation in Access 2007 so I've opted for a MultiSelect ListBox. However, I'm still unable to wrap my head around how to make this work.

I've got a form that'll display/enter data contained in one table (e.g. used cars), and a ListBox on the form that'll display the contents of another table (options: power windows, ABS, air, cruise...). A third, intermediate table that links the two together contains only 2 remote keys: VIN & option ID - one record for each option that a particular car has.

When navigating from one car to the next I'd like to see the options that each vehicle has pre-selected. Also, when entering a new vehicle (or editing an existing one), the user should be able to Check/Ctrl+Click options to create/remove records in the intermediate table.

For what it's worth, I'd consider limiting the listbox to just the options a vehicle has (or doing a comma-separated list in a label) with a button that pops up another form to add/edit/remove entries. When trying that however, I've been unable to remember how to get the listbox to display ONLY values associated with the current record.

Any direction would be greatly appreciated.

 

[MajP]

I assume you have an "auto" table and "options" table and a linking table "auto_options" something like:

tblAuto
pk_autoID
other automobile fields

tblOptions
pk_optionID
other option fields

tblAutptions
fk_AutoID
fk_OptionID

your option lst box's recordsource is likely based on a join between your tblOptions and tblAutptions joined on option ID.

On your main form you could have a hidden field with the pk_AutoID "txtBxAutoID". Then in the query for the listbox used this field in the criteria. Something like

"where fk_AutoID = " & [forms]![frmAutos].[txtBxAutoID]

 

[GEAK]

That's precisely what I have and that's essentially the code I was trying to use. The problem crops up in that regardless which event I put the code in I've been receiving error messages.

I'm updating the rowsource property for the list box with the query (modified as per your example) and I get an error stating I cannot reference a control that doesn't have the focus.

When I set the focus to the list box I get a similar error message because I'm also referring to the text box holding the auto ID.

When I break the code into multiple statements so that I never refer to two controls at a time (and set focus to them prior to using them), I get an error message stating I cannot set focus to a control that isn't visible - though I may be misinterpreting that message.

The thing that I find most frustrating about this is that, if it were up to me, I'd make the forms & write all the code in either C++ or VB.Net - I'm much more familiar with each of those and know exactly how to code this in them. However, this is for a course I've been asked to deliver and the people attanding the class don't have the programming background I have. I must do this in Access 2007.

 

[PHV]

the code I was trying to use
Which code ? You've posted noting of your actual code ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I get an error stating I cannot reference a control that doesn't have the focus.

My suggestion does not require any vba code. It just requires a Sql criteria.

Actually there will most likely be an on current event code that says

me.yourListBoxName.requery

To make this even easier you can use a subform that is formatted to look a lot like a listbox. Then you just use standard linking. It would easily meet this:

"For what it's worth, I'd consider limiting the listbox to just the options a vehicle has (or doing a comma-separated list in a label) with a button that pops up another form to add/edit/remove entries."