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

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?

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.

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

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?

Can you post a screen shot of your form with the list boxes?  I still have no idea what you are trying to do.

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

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

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?

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.  

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

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 __%).

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.

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

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?

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

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 

Of course we need the additional field,

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.

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

Thanks, glad to help.