list box in form (access 2007)

[johnhutchison]

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.

[HansV]

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

[rlbroerman]

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

[johnhutchison]

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.

[rlbroerman]

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

[johnhutchison]

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.

<hr>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<hr>

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:
<UL><LI>a field for Pain Intensity (displayed as a combo box, with its own list of values)
<LI>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"?

[GARYPSWANSON]

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

[rlbroerman]

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

[GARYPSWANSON]

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

[rlbroerman]

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

[patt]

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

[GARYPSWANSON]

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

[rlbroerman]

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

[HansV]

Try

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

[johnhutchison]

OR


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