list box in form (access 2007)

[rlbroerman]

I am trying to insert a list box into “frmPatients” with option to save multiple values using check boxes. tblPainLevel and tblCurrentPain have multiple valued check boxes. My attempt is shown in the lower right corner of the attached “frmPatients”. I have inserted a list box into the form but it doesn’t have the check boxes or does it let me save multiple values in a record. Like the tables do. Can you help, please?
Rick B

[Jezza]

Hi there

Is there a chance you could save the database in 2003 format and zip it again?

[GARYPSWANSON]

The first step is you are going to need to modify the listbox to a multi-select listbox to be able to select multiple values.

In the form design view, select the properties of the listbox, go to the other tab and set the Multi Select row to extended. This permits you to select multiple values in your listbox. You still need to determine how to get the data where you want.

I am, unfortunately, lost in your description of where you want to write the selections to. Once you make your selections, do you plan to store them in a table linked to the patient ID or other?

This should give you a start. If you search the forum on multi select list boxes you will find many examples of how to get the data selected and store them. Post back if you are stuck.

[johnhutchison]

I think the problem here, which I have not yet had a chance to investigate properly, is that 2007 multivalue fields are being used. Converting back to 2003 is not an option if I am correct.

[kentg]

I don't know if this helps but to get all the values, of lines that are selected in a list box, in earlier version to 2007 this code works
Dim varIndex As Variant
Dim strFilter As String

For Each varIndex In lstBox.ItemsSelected
strFilter = strFilter & ", " & lstBox.ItemData(varIndex)
' if the ID field is a string use this line instead of the previous line
' strFilter = strFilter & ", """ & lstBox.ItemData(varIndex) & """"
Next varIndex

in this example it is just loops through the rows and concatenating the values of the selected lines into a string to pass to a report filter but you could write each value to a table.
In this example the multi select property of the list box is set to normal.

[GARYPSWANSON]

John,

After re-reading the post, you are absolutely correct. I am going to post a link to the original post that has directions on how to use multivalue fields.

[GARYPSWANSON]

Rick,

Check out the following link for a review on creating multivalue fields. HTH http://office.microsoft.com/en-us/access/H...=CH100645681033

[rlbroerman]

I can do this to table but I can't get to it form a form.
Rick b

[rlbroerman]

multi value list box is new to 2007 therefore a 2003 would not have it.
Rick b

[johnhutchison]

I have had a look in 2007, and I think we need to take a step back to clarify your table structure, and what you want to achieve, before we look at how to do it with a form and list box.

tblPainLevel is just there as a lookup table I believe. It does not need multivalues, and does not need to lookup a value list that is the same as its own contents. So change this table so it is just a plain list of values.

What is tblCurrentPain trying to record? and why do you want multiple values? At the moment it seems that each patient has multiple records in tblcurrentpain, and each of these has multiple values. If you really need multiple records, then I imagine you need some other field to distinguish them, such as a date field.
I don't understand this - which does not make it wrong - but I want to be clear about it.

As you know multivalue fields are new in 2007. I suspect that many developers are reluctant to use them (I certainly am) because the idea of multivalue fields is not consistent with relational design. I see them as a shortcut for people who can't be bothered with (or don't understand) proper design. They might work, but I would be worried that I might find later that I am restricted with what I can do with the data contained in the multivalued field.

[GARYPSWANSON]

Rick,

John brings up some good points. In looking at your current design, you input patient data into TblPatients. Then you use a one to many relationship to track visits in the table named Visits that is linked back to the patient table via the SocSecNo. Your table PainLevel is used as a lookup to select the PainLevel of the patient for the current visit.

If I understand your request, you want the ability to select one or many Pain Levels for the current patient visit and want to store the data selected. (Correct?)

The question is how to do this? Do you want to use a MultiValue field as the selection that stores the data in a string as choice 1, choice2, choice3? I would not do it this way as it would then be a little more difficult to get a count on patients with a particular pain as the stored data is stored as part of a string. If using a multiselect listbox, you can loop through the values and store the data in a table that links back to the visit table via the visit number and store each pain as a separate value. It would then be easy to use the "pains" selected for further analysis or querying.

Anyway, back to your question. How do you want to obtain the data - via a multivalue listing that stores the data in a string or a multiselect listbox that would store the individual records in a new table linked to the visit as a one to many relationship?

[GARYPSWANSON]

Rick,

One other question. Your form that has the listbox for pains is tied to the main patient form. Wouldn't you want this listbox that selects pains to be tied to not only the patient but on a particular visit as I would assume that a patient could have different symptoms or pains on different visits.
The listbox for pains should be tied to the vist table. You might want to move this listbox to the visits form. Just something to think about.

[rlbroerman]

I relent.
After numerous comments about not using a multivalue list box. I agree and will try to use a subform instead.
Originally I thought the multi value check box look like a good idea because the subforms that I had created seemed so mechanical and did not fit in with te rest of my form.
Got any ideas on how to make the subform more like the rest of the form?
Rick B

[johnhutchison]

You have done the exact opposite of what I asked.

Before talking about what what type of forms/controls to use, get clear about the table structure you need, and what it all means.

I am not telling you not to use a mutlivalued field in a table (and perhaps displaying it with a list box) because I don't yet understand what you are trying to do.
However, I am sceptical about the use of multivalued fields.

[rlbroerman]

JH,
Sorry, I guess I jumped ahead to try to use a subform instead of a list box.
I think what I want to do with Pain is what I have done with Current Medications subform in the attached Database.
Another question. How can I tell what tables, queries, and etc. are used in my database and which ones are superflous so that I can find out what is not necessary and get rid of them. I want to clean up my database and get rid of old non-used stuff.
Rick Broerman