list box in form (access 2007)

Topic

New Reply

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

Reply | Quote

Replies

Hi there

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

You are still talking about forms instead of data.

You might say “I want to record the level of pain experienced by each patient at each visit. For pain I want to record the level of intensity, and whether it is constant or intermittent.”

Or perhaps you might say “For each patient the level of pain will be recorded periodically. Each time a recording is made, I want to record the date of the recording, the intensity of the pain, and whether it is constant or intermittent”

I am not saying that either of those is correct. I want you to say what you are trying to record.

I have not looked at your attachment yet, partly because I want a statement first, and partly because it is difficult for me to look at Access 2007 dbs. I need to get out and fire up a different computer.

Finding out what tables and queries are superfluous is not simple. The best option is to get a copy of Rick Fisher’s Find and Replace , and use the Cross-Ref search.and chose to “Only list unreferenced items”. It can be downloaded free, and used for 30 days without registration, but I think (but I am not sure) that Cross-ref searches are only available in the registered version.

Reply | Quote

I want to record the level of pain experienced by each patient at each visit and whether it is constant or intermittent and the type of pain.
I want this to show in a report like:
Current Pain
constant
sharp
intermittent
burn
stabbing
Rick B

Reply | Quote

In that case fields for pain should be added to the Visits table, rather than creating a new table.
Each dimension of pain should be a separate field, with its own list of values to choose from.
I would have thought two fields would be enough.
So you don’t need any new forms at all -just a couple of fields added to the visits form.

Reply | Quote

But this will allow me to save only one type of pain in each field.
I want to be able to save multiple pains ie “stabbing” and “sharp” for constant pain.
I’ve got a bigger problem. I tried to add a list box to a form and then open the form and the subforms have disappeared and I get the error”Reserved error(-3087)” Got any ideas?
Rick B

Reply | Quote

When you write out a statement describing the data you want to collect, it is import to specify if any of the fields mentioned need to hold multiple values, as that has a big impact on the design.
[indent]

---

I want to record the level of pain experienced by each patient at each visit and whether it is constant or intermittent and the type of pain

---

[/indent]

I thought that by breaking the description of pain into different dimensions you would only need one value for any one dimension.
But I don’t know enough about pain categorisation to know if that is true.

One dimension is the Intensity of the pain. Another dimension is whether it is constant or intermittent. A third Dimension seems to be some more descriptive term such as “Stabbing” or “Sharp”, and I think it is here that you think you need multiple values.

The general principle is that whenever you need multiple values you move the data into a related table, and display it on a subform ( or if you prefer and use 2007, create a multi value field, and perhaps display it as a list box .)

So the visits table would have:

• a field for Pain Intensity (displayed as a combo box, with its own list of values)
• a field to describe the continuity of the pain (displayed as a combo box with its own list of values
  [/list]Then I would create a new table tblVisitPainDescription – VisitPainDescrptionID autonumber, VisitID Number and PainDescrption. PainDescription would be displayed as a combo with its ow list of values.
  These PainDescription would be displayed as a subform on the Visits form.

  If you prefer the multivalue field option, add PainDescription to the Visits table as a multivalue field, with its own list of values.

  I don’t know about the other problem. It sounds like the form has become corrupted. What do you see in Design View? Can you remove the list box? Have you tried “compact and repair”?

Reply | Quote

Rick,

I have been following this string of posts and you basically have two options if you want to sore multiple pain values for a visit.

1. Using the Multivalue list, select the pain or pains a person has per visit and store that in the visit table. (I have attached your database modified slightly that does this) This stores your selected items in one field with commas between each selection. Open your patient form to see this.

2. Create a new table – lets call it TablePains. Fields are ID, VisitNumber, Pain. Link to visit table as a one to many on the visit id. Use a multi select listbox and after selecting the “pains”, use code to go though the data and write it to the table. This way, each selection is stored as a separate value linked to the visit.

Anyway, the attached database shows an example of number 1. HTH

Reply | Quote

Gary,
Works great, thanks a bunch.
Can you show me the changes that you made. I tried something similar 50 times or so but couldn’t get it to work.
I would really like to see what you did differently.
Rick B

Reply | Quote

Rick,

– Open your database and close all open forms.
– Open your sbfVisits form in design view
– Delete the current pain combo box
– Open the field list
– Select the Paid ID field and drag it into the form and the multivalue list will work as your properties on the Pain ID in the main table are set as a lookup and allows multivalues.

You probably created the original combobox before setting your properties to allow for a multivalue field list so the combobox retained the original settings.

HTH

Reply | Quote

Thanks for the insight.
New problem. Now I get an error when I try to open a report thru a switchboard. The error is “There was an error executing the command”.
I don’t know how to determine what the error is. Can you help, please.
Rick B

Reply | Quote

Go into the switchboard table and see what you were trying to execute and make sure the spelling is correct.

Reply | Quote

Rick,

I stopped using switchboards a long time ago as I did not find them very useful. In prior versions of Access there was a limit to how many command buttons that could be used to trigger actions such that you would find people jumping from switchboard to switchboard.

You might find it much easier to create a form and then insert command buttons to do what you want. If you use the wizard, you could insert a command button that opens the report. FWIW, I would recommend staying away from switchboards. If you use a form, you can have the form open when the database opens so it looks like a switchboard.

Now to answer your question. The recordsource on the report is qryVisitsDate. As there is no object in the database with this name, you get the error message when trying to open the report as the recordsource does not exist.

HTH

Reply | Quote

Gary,
Works great, thanks a bunch.
I put pain into a text box in my report. The pain query has multiple pains seperated by commas but I only get one of the pains in my report. Got any ideas?
For another report I want to pick two variables ie SSN and visit date and print info from only that visit. How do I go abt that?
This is what I tried but it won’t compile ——strWhere = “[Soc Sec No] = ” & Chr(34) & Me.[ComboSSN] & Chr(34) & “” &
“[VisitDate] = ” & Chr(34) & Me.[VisitDate] & Chr(34) & “”
Thanks for the help.
Rick B

Reply | Quote

Try

strWhere = “[Soc Sec No] = ” & Chr(34) & Me.[ComboSSN] & Chr(34) & ” And ” &
“[VisitDate] = #” & Me.[VisitDate] & “#”

Reply | Quote

Did you see the first question in my post on 8/18 @02:18?
Rick B

Reply | Quote

I’m in a different time zone, so I’m not sure which post you mean.

Reply | Quote

The post said —–“I put pain into a text box in my report. The pain query has multiple pains seperated by commas but I only get one of the pains in my report. Got any ideas?”
Rick B

Reply | Quote

That was on the 28th, not on the 18th.

I’ve told you before (for example in post 707,176) that you shouldn’t store multiple values in a field.

Reply | Quote

OR

strWhere = “[Soc Sec No] = ” & Chr(34) & Me.[ComboSSN] & Chr(34) & ” And ” &
“[VisitDate] = #” & format(Me.[VisitDate],”mm/dd/yyyy”) & “#”

Reply | Quote

Your version is to be preferred (in fact necessary) when you may have users with non-US system settings. The version without Format will only work correctly if your computer is set to US date format.

Reply | Quote

JH,
I tried both yours and Hansv suggestions but I get a compile error. I must have something in my form twisted around.
Rick B

Reply | Quote

Gary,
I tried this and it worked great but when I to put it into a report but I only got one pain of the multiple pains seperate by commas in my query qryVisits. Hansv said this would be a problem but since you are familiar with multivalued check boxes I thought you might be able to help.
Rick B

Reply | Quote

Rick,

Your report should show you the multiple pains separated by comma’s if the data is setup as multivalue. I am not sure what the problem is you are having. Can you attach a stripped down version of your database?

Reply | Quote

Gary,
I thought I attached last time but I guess not.
Rick B

Reply | Quote

One last thing to check – in your report, query, or datasource for the report, make sure you are using the data field for PainID and not PainID.value.

Reply | Quote

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.

Reply | Quote

Rick,

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

> I think (but I am not sure) that Cross-ref searches are only available in the registered version

That is correct, that option is disabled in the free evaluation version, it only becomes enabled when you enter the registration key. The help file lists the extra features that are available in the registered version.
I’ve bought Find and Replace years ago and I find it well worth the modest price.

Reply | Quote

In the DB you posted there are required tables missing, some forms missing, but duplicate versions of other things.

When you post a db for others to look at make it easy by:

1. Checking that it works
2. Removing superfluous items

Your code for opening the report to a specific visit has two problems:

1. You start a new in the middle of a line of code. If you need to start a new line, you must use the continuation character _ at the end of the line.
2. visit date is not available on the form to use. Your combo is just a list of patients.

Your visits table has a primary key of visitID. If you want to open a report to a specific visit, use the ID to identify the visit rather than a combination of SSN and Date. If you use a combo box, the the ID number as a hidden first column.

Reply | Quote

JH,
I think all the necessary forms and tables ar included this time.
Rick B

Reply | Quote

JH,
I need to input the SSN and the visit date because that is all the final user will know. They won’t know the VisitID.
Thanks
Rick B

Reply | Quote

They don’t have to know it, or see it, but Access can still use it. Typically you would have a combo box that displays SSN and Date, but has visitID as a hidden first column, and that is what your code would use.

Reply | Quote

Because you still have not deleted all the superfluous stuff, I don’t know what report you are trying to open from what form.
You say you want to open a report to a specific visit, but I can’t see any reports that are about one visit at a time. You have reports that list visits, and reports that are about patients, with visits in a subform.

The form called “run old visitsreport” might be the one. The first combo should just list SSN numbers, and just have one column. The second should have two columns VisitID and Visit Date, (as it has), but only for patients whose SSN matches the other combo. Then the where condition just uses the hidden VisitID , which is the value of the combo box anyway.

Any report you open with a VisitID Where clause must include VisitID in its recordsource. Without that you get a parameter prompt.

Reply | Quote

JH,
You are correct. The report is “run old visitsreport” . I included “run visit report” to show how it work with only SSN.
I am confused. I tried what you suggested but can’t get it to work.
Can you help some more, please.
Rick B

Reply | Quote

Here is a demo.

frmRunVisitReport has 2 combos. The first just displays SSNs. The second displays visits for the selected SSN. Visit Date is displayed, but the ID is hidden in the first column, so the ID is the value of the control. Whenever the SSN is updated, the second combo must be requeried, and cleared. This code is in its AfterUpdate event.

The command button opens a new report : rptVisitDetails that shows all the information about a specific visit.
The Pain field is added to that report as a combo box. This displays the multiple values as a comma separated list. I think that is what you want.

I deleted other forms and reports to make the zip file small enough to post.

Reply | Quote

JH,
Works great. It is exactly what I was wanting Thanks a bunch.
When I copied this into my whole database I must have done something else inadvertently. Now when I run the report “copyof visits” it prints out multiples of the same info. Can you help, please.
Rick B

Reply | Quote

Rick

It is difficult for me to look at 2007 files so I have not looked at the attachment. I will if I have to.

When you use multivalued fields, you can add the .Value attribute for that field to the query. When you do that you get a separate record in the query for each of the many values.
Is that what is happening?

Reply | Quote

JH,
This looks like it might be the problem, but I can’t figure out how to implement your suggested solution. I don’t see where to add “.value”.
Can you help, please.
Rick B

Reply | Quote

I was not telling you to put in it! I was saying to taking it out if you had it.

I will have a look your db in a little while. Access 2007 is easier to get to now, as I have swapped computers.

Reply | Quote

You have lost me I’m afraid.

As I said a few posts ago, when you post a db for someone to look you need to take the time and trouble to make sure that what you are posting makes sense to other people.

• Take out what is not relevant to the question
• Put in what is relevant
• Make sure it works
  [/list]I posted you a working demo, which was about opening a report from a form. It needed a form , a report and a query. What you have posted does not have any of the things , so what do you want me to look at?

  If you include just the PainID field from visits in a query, you will see the multivalued field in the resuts. If you join on that field to tblPainLevel, two things will happen:

  1. Visits with no pain will drop out of the results
  2. Visits with more than one pain value will appear more than once.

  I think that is your problem, but I don’t know what query you need to fix,

Reply | Quote

JH,
I tried this, which is a great solution, but what I added to the code is strWhere = “[VisitID] = ” & Chr(34) & Me.[ComboSSNVD] & Chr(34) & “” but when I select an SSN and Visit Date in the combo box and press the command button to execute the above I get “Enter Parameter Value” for ” VisitID”
I have had this kind of problem but can’t remember how to solve it.
Can you help, please.
Rick B

Reply | Quote

Another problem with what you tried is because VisitID is a number field, not a text field , you don’t need the chr(34)s.

strWhere = “[VisitID] = ” & Me.[ComboSSNVD]

Reply | Quote

JH,
The attachment in my post 732,168 has what you ask for, I think. I don’t understand why “Visits with more than one pain value will appear more than once”.
Why would multiple pains cause multiple visits to be on the same report?
Thanks again
Rick B

Reply | Quote

The report copyofVisits gets its data from qryVisitsDate. Your visits table has 16 records, and so does qryVisitsDate, so I don’t see any problem there.
All of the visits in copyofVisits have different dates, so I can’t see what the problem is.

The subreport uses qryvisits, which joins table visits to tblPainLevel. As I explained last time. This only finds the last visit, but if it has multiple values for pain, it will return a separate record for each pain value.

Is your problem with the main report or the subreport?

Reply | Quote

This thread has become very long, so I will lock it. The discussion has been continued in the thread starting at post 738,103.

Reply | Quote