We help IT Professionals succeed at work.

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

Bevos
Bevos asked
on
Medium Priority
327 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.

Commented:
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

Commented:
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))
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.