list box in form (access 2007)

[johnhutchison]

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.

[johnhutchison]

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]

[rlbroerman]

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

[johnhutchison]

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.

[rlbroerman]

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

[johnhutchison]

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?

[rlbroerman]

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

[johnhutchison]

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.

[johnhutchison]

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.

<UL><LI>Take out what is not relevant to the question
<LI>Put in what is relevant
<LI>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. <LI>Visits with no pain will drop out of the results
   <LI>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,

[rlbroerman]

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

[johnhutchison]

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?

[HansV]

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