Solved

Access 2007: Multiselect List box, change input mask based on # of selections

Posted on 2011-02-18
22
958 Views
Last Modified: 2012-05-11
Hello, I am curious if the following procedure would work in Access 2007.  I have a multi-select list box with several items.  The entries from this box are passed along to a field, and then I have another field describing these selections in the form of a %.  I would like to have consistent looking data so I want to apply an input mask to this field based on the number of selections the user has made in the multi-select box.  For example, the user has made 3 selections so the input mask would be something like 00%, 00%, 00% and changed based on how many values the user has selected.  Does anyone know how to code this?  Bonus points if you can make it such that Multiselect choice1 00%, Mutliselect choice2 00%, etc...

Thanks,
Bevo
0
Comment
Question by:Bevos
  • 11
  • 6
  • 5
22 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 34930052
If you populate the list box from a query or a table, you can set the format of the percent field prior to using it in the listbox.  How do you populate your listbox?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34930452
I guess I'm saying from my point of view, you are making the problem much larger than it is.  Rather  than define the problem at the 'end', why not 'explain' what you are trying to do.
0
 

Author Comment

by:Bevos
ID: 34931010
I am trying to allow a user to make multiple selections in a list box and then in a subsequent field give information about the selections of that list box.  I have it so that there are currently 3 list boxes each giving the same choice options and then 3 description fields for the %.  But this seems like a poor way to do this.  What do you suggest.  Am I making a mountain outta a mole hill?

T.  Bevo
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34931642
What are you trying to display a percentage of?  The percentage of selections, ie, there are 50 choices, the user selected 25, so you want to display 50 percent?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34934615
Can you post a screen shot of your form with the list boxes?  I still have no idea what you are trying to do.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34944062
Three List Boxes each giving the same choice options??  ListA, ListB, ListC, each with choices 1,2,3 and 4.  ListA has 1,2 selected, ListB has 2,3 selected ListC has 3,4 selected.  What do you want to see in the textbox - 1,2,2,3,3,4
0
 

Author Comment

by:Bevos
ID: 34963457
Hello, I hope the attached image might explain things better.  Sorry for the late reply but I haven't had time to work on any database stuff for a while.  Basically I would like to know how, if I have a multi-select list box can I edit a field to display things as I have it in the image.  That is to say, if I make one choice it gives me the option of <List box Selection> __%.  Where the __ is editable by the user to be some value that they choose.  Or if there are there choices the field should appear as  <List box Selection > __%,  <List box Selection 2> __%,  <List box Selection 3> __%.  I am hoping that by using this method of data entry it could save form space, but maybe it isn't following best database practices (i.e. I should have a separate field for each of the %s associated with its list box selection.)  Anyway, I'd be happy for any help on the topic and thanks for your patience.

Bevo
E-E.JPG
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34971387
Can you describe the structure of the underlying table for the list box. Is it along the lines of:

tblItems
======
ItemID - pk - autonumber
ItemDesc - Item1, Item2, Item3, etc?
ItemPct - number, percent - how many decimals?


0
 

Author Comment

by:Bevos
ID: 34980119
Hi this is an unbound multi-select list box whose structure is two fields Item and Description.  The item percent that you mention would be contained in a table corresponding to the form.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34984114
That is a stinker of a problem.  I wasn't able to solve it your way but you might want to have a look at the mdb attached.  One table, one form, one list box, two text boxes and a command box.   Q-26832455.mdb
0
 

Author Comment

by:Bevos
ID: 34984426
Hi GrayL I see what you did there.  A single list box output two text boxes.  However, I am looking to input information to a form (i.e. this percent row in tblItems would be blank, also it might contain multiple percentages based on the number of items selected in list box).  I am going to put an example DB of my own up because I guess I am awful at explaining what I would like to have happen.  Basically this whole thing is so conserve space on a form so that instead of having N text boxes to describe N selections from a list box I could just have 1.  Wherein the list box selections would be passed on to a text box with a place for the user to input a % (for example if someone were reading an article with a statistic saying that whites, asians and hispanics had XX%, XX%, and XX% below poverty level  they could select from a multi-select list box of race 'white' 'asian' 'hispanic' then the user could enter in a text box (txtPoverty) white __% asian __% or hispanic __%).
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Expert Comment

by:DockieBoy
ID: 34985596
Much easier if you select one at a time with a combo box, allow the entry for the percentage, then store the data with the click of a button, you could display it back with a list box.  Space saving, time saving and if you  store the data in it's own table then you can access it and manipulate it easily.

Let me know if you need more of an example.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34985811
Or, if you mast do it the other way, then this code will get you started;

Private Sub btnAdd_Click()
 Dim i As Integer
 Dim MyMask As String
 For i = 0 To Me.lstOptions.ListCount - 1
   If Me.lstOptions.Selected(i) Then MyMask = MyMask & Me.lstOptions.Column(1, i) & " " & 999 & "% "
  Next i
 Me.txtPercentages.InputMask = MyMask
 Me.txtPercentages.SetFocus
 Me.txtPercentages.SelStart = 11
End Sub

That works using a button called "btnAdd"  to set the input mask for the text box called "txtPercentages" using the options selected from a list box called lstOptions

You would need to adjust the .SelStart = 11 to which ever number suits the number of character in from the left of the text box that the user enters the first percentage into.

Substitute the names I have used for the controls for the names of your own controls, and hey presto, stuff happens, lol.

Be aware that this is catering for a user being able to enter 100%, there for, when entering less than 100%, the entry needs to start with a "0" or a space.

Good luck with this.  :)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34988029
Bevos:  Once you have the info in the text box, then what - where do you store it?  To extend my approach, the table should have three fields, MainArea, SubArea, and Percent.  If make Selections in the Poverty listbox, the word Poverty goes to MainArea, White, Asian, and Hispanic into three new records in the field SubArea, and then add the Percent one record at a time as per my original approach.  You would start with a table with no records.  Comments?
0
 

Author Comment

by:Bevos
ID: 34994458
Hi Dockieboy and GrayL thanks for your comments.  Because I feel this question is going to take a turn into asking for your more general advice I will award you both 250 points, but I'd just like a little more feedback. I am kind of torn between how I would like to do this.  On one hand, the results of this data will likely be exported into a simple word table in which case it wouldn't matter so much that they are recorded in a non-easy to manipulate field, but I would like to use good database practices as well.  
So here is my problem, the code being used above was an example for a larger database I am making that is involved with taking a great deal of data from scientific articles and then storing it into an Access Database.    I want to have an elegant way for users to enter this repetitive data.  There will be many times where the user needs to select (likely from a list or combo box) a group (maybe talking about descriptions of patients [i.e. stage 1 vs stage 2 cancer patients] and then enter a description about them.  I am new developing forms and my thinking is kind of stuck in the idea of "make a bunch of combo boxes giving group and then a text box for the user to enter the description".  But because of the amount of information needed to be entered this would look exceptionally sloppy.  Both of you seem to be very knowledgeable about this sort of thing and I would like to hear your feedback on how best to handle this.  

In a nutshell, how should a database creator go about making a non-cumbersome form when users will have to select multiple groups and enter a description of them in a text box.  I thought the merging example above was good, but now I'm not so sure this will be the best route to go.

Thanks for any advice, Bevo
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35001229
It would seem to me that the info stored in the table in the form:

White 20%, Asian 30%, Hispanic, 25% - as a single line - would be far more difficult to  subsequently analyze compared to:

Race           Pct
=======   ==
White          20%
Asian           30%
Hispanic       25%

Of course we the additional field, ie. the Title of the List box as an additional field as I was trying to say in my post at http:#a34988029
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35001248
Of course we need the additional field,
0
 

Author Comment

by:Bevos
ID: 35001501
Hi GrayL, does the way you have laid out the table work if all of those are going to be saved in a single record?  the title of the list box will be whatever corresponds to the group in question, for the example above it would be lstRace.
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 35003300
No, if you want the single record approach, DockieBoy looks like the answer.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35004559
Yep, the code I gave you will do the job, however, as you asked for our opinions, I am completely siding with GrayL.

The most user friendly way to do it is using a seperate table to store the data you are talking about, the best way to do it from your side of things is to store it in a seperate table.  I'ts the only way to go realy in my opinion.

I have tested the code I gave you, and while it works, it is confusing from an end users point of view.  The text box that you have for the user to enter the data will auto poulate with the text from the input mask, which in my opinion, will confuse people as to weather or not they are to input data there.

If you want to make it look nice, I think a combo box is the way to go, have the user select one at a time and enter the data as they go.  

There is probably many, many ways to go about it, but I think that will be easier for both you and the user.  :)
0
 
LVL 3

Accepted Solution

by:
DockieBoy earned 250 total points
ID: 35004574
Just thought I would add, I am more than happy to go into more detail and give you some examples if you like, however, I am a little busy at present and that could take me a couple of days.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35007943
Thanks, glad to help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now