Combo box Add All

[user622]

Hello all, I'm new to access and new to the forums.



Im creating a form to answer a questionare for several locations listed in a combo box, in some instances i need to apply the answer to a question to all locations.

Is there a way to add an all option to the combo box that adds new data fields with the same answer for all the locations in the list? I have tried 2 different tutorials where I copied the given code and both resulted in access crashing.

[note] the questions are on a form and the answer and location is on a subform

any help or recommendations would be appreciated

[Rod]

Hi,


There are two aspects to your question:

1. Including the 'all' option in the combo box list;
2. Identifying when 'all' is selected and taking the appropriate action.

To achieve 1 you need a UNION query if the locations are retrieved from a table, or simply add an 'all' option if the row source for your combo box is a value list. I can't really help much more with this unless you tell us about the row source for your combo box.

For 2 (from your write-up) it seems you need to add rows to existing tables or update existing rows in those tables. Identifying that 'all' is selected is not difficult; adding data to your tables is more complicated. Again I cannot give specific help until I see your master/subform user interface and your database design. Whatever the solution there is no command to do it for you; you will be involved in a small amount of VBA coding.

[Rod]

Just been thinking some more about this.

I don't think using a combo box to trigger specific action that updates your database is a good idea. By all means include an all option in the list, if appropriate, but combo boxes are used more for browsing data. I suggest including a command button that when clicked adds the answer to all locations.

Another scheme - better in my opinion - would be to display your locations in a multi-select list box (include an option for all locations) and then the command button adds the answer to all selected locations. Much more flexible!

[user622]

Thanks! I didn't know that list boxes could do that, and it will actually solve alot of problems that I saw coming up. Now I just need the code to make new entries for each selection from the listbox. I dont know any VBA myself so if anyone could help me out, either by supplying the code or directing me to somewhere that has it, it would be greatly appreciated.

[Rod]

Will get back to you asap.

[Rod]

Attached is an example of the mechanism for selecting locations. You face a learning curve re VBA (we're here to help) but have a look at the code behind the and see if you can work out what it's doing. A quick way to open the VBA coding window is ALT + F11.

Play with the example form; remember it's not cast in stone.

Attachment 3731

I'll send you a private message with my email address. Send me a message if you would like a commentary on how to set up the list box, etc. I think it's not suitable for a post here.