asked on

Access 2007: On a survey implement the option for 'other'

Hello, I have attached an example to this question to make the problem a little easier to understand.
I have two tables, one that populates a multi-select listbox with a listing of encyclopedias and another which stores the selections of these choices and also has a unique ID.
In the list box of choices I have the option of 'other' for the choice of encyclopedia that you use and would like the user to be able to then type his or her response. However, this is not working for the multi-select list box. Does anyone know of a programmatic way to solve this problem?

Thanks so much,
Bevo S.

EE-Example-List.accdb

rj8820

Ok I have attached a sample of how I think you can do what you want.

I created a field nthe table and on the form called "Other comments". Then I created a simple bit of vba code (you could probably do it in a macro to) which gets called when the field changes. The code does a refresh to update everything and then checks the Encyclopedia field for "Other (Specify") and asks for the comments and populates the comments field on the form.

If there is no Other selected it makes sure the Comments field is cleared. You could use the field on the form rather than the inputbox but that causes some weird interface artifacts so the input box method is probably better. Hope that helps.
EE-Example-List.accdb

als315

I can't recommend you use multiselect in Access DB. If you like to have later some statistics, it will be very difficult to use this field in queries.

Bevos

ASKER

Thanks for the advice als315. I realize it is difficult to work with this type of comma separated list in a SQL query, but these data are not meant to be rigorously analyzed or queried. Instead it is simply meant to be exported to a word table for people to look at as a comma separated list.
rj8820, this looks really good! I'm having a bit of a problem though. It works great for the first entry, but on subsequent entries it says 'Run-time error '94': Invalid use of Null' and points to the line: Me.Other_Comments = InputBox("Please specify the ""other""", , Me.Other_Comments)
Do you have any suggestion on how to fix this error?

Thanks so much in advance, Bevo S.

rj8820

ok my bad. It's objecting to a default value of null for me.other_comments...

Change it to

Me.Other_Comments = InputBox("Please specify the ""other""", , _
isnull(Me.Other_Comments),"",Me.Other_Comments)

That way if the value is null it uses an empty string instead (which is an entirely different thing)

Remove the continuation if you want

Cheers, RJ

rj8820

AND of course I missed a closing bracket in my haste...

Me.Other_Comments = InputBox("Please specify the ""other""", , _
isnull(Me.Other_Comments),"",Me.Other_Comments))

ASKER CERTIFIED SOLUTION

rj8820

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bevos

ASKER

No problems at all. I think this is a really nice way to solve the problem. Thanks so much for all of the help.

Bevo S.