?
Solved

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

Posted on 2011-04-20
7
Medium Priority
?
318 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
0
Comment
Question by:Bevos
  • 4
  • 2
7 Comments
 
LVL 1

Expert Comment

by:rj8820
ID: 35438621
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
0
 
LVL 40

Expert Comment

by:als315
ID: 35439240
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.
0
 

Author Comment

by:Bevos
ID: 35447778
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:rj8820
ID: 35447904
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
0
 
LVL 1

Expert Comment

by:rj8820
ID: 35447914
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))
0
 
LVL 1

Accepted Solution

by:
rj8820 earned 2000 total points
ID: 35447941
OMG I am losing my ability to type. Here is the double checked definitive version :-P

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

For my punishment I have attached a version of your DB with the correction and apologise for the confusion
EE-Example-List.accdb
0
 

Author Comment

by:Bevos
ID: 35447983
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question