report based on queries, filtered by form?

[traffas]

I have a database concerning ambulance calls. Tables include Patient information, situation, chief complaint, narrative, vitals, ekg's, medications, etc.... All information is entered though a "run form" Form. The run form uses command buttons to POPup forms to fill out all data in various tables. All data is linked through a "event number" I have a report designed on several sub reports which are based on queries.

The report design is just the way I want it. I have a command button on the run 'Form' that opens the report. My problem is that I don't know how to pull up the current report based on the form "event number". I get all the reports, if I move through the record selector, I eventually find the report that I want. How do I call up the current report based on the form 'event number'?

Many thanks in advance
all of you have been very helpfull in the past......

Shawn

 

[mrf1xa]

I presume the query on which your report is based oncludes the event no . Make sure there is a field on your form showing event no as well (it doesn't have to be visible) and set the query criteria under event no to Forms![Name of your form]![name of event no field]. That should do it....

Nigel

 

[traffas]

Thanks, will try it now...... and get back to you......

 

[traffas]

Nigel,

I did as you asked and entered
Forms![patient report]![text2] Where patient is the initial form and text2 is where the user enters the Event number. My reports are blank........

A little more on my design.......
The opening form has a textbox where the user enters a "event number". A new form opens with an invisable "event number" that is defaulted to the value of the Textbox on the opening form. All subsequent forms used to enter data also have and "event number" that is defaulted to the initial form's textbox. All data entered into table are linked by "event number".

Command button on second form or "patient run form" call for printing of the report. I have entered 3 'reports'. Initially I got a record selection on the bottom of the report page and could 'navigate' through the reports and find the one with the appropriate data. After adding to the criteria in the queries, I get blanks..........

any ideas? thanks.........
Shawn

 

[WildHare]

Ok - the first time,you get all the reports because you don't specify WHICH event number you want. The second time,you get NO report, because you specfiy an incorrect or missing Event number... the old > ROCK, < HARDPLACE situation



I'm confused as to why you have the user enter an &quot;event&quot; number, and then hide it on the next forms..Is this user-entered event number the key to your whole process?

Your query needs to refer to an Event number that is on an loaded form. Try this. Open the form, and enter an event. Leave the form open. Then run the query to see if it can see the event number. If the query returns the one record you see, you're good there. Now run the report. If the report shows more than one record, then somehow the report isn't using the query as it's record source.

Make sure you don't CLOSE the form before you run the report. You can hide it but don't close it.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[traffas]

The 'event number' is the main key ot the database. It distingushes each 'call' from every other.

I've done as you asked. the queries that i opened list all records and not just the ones from the open form.

Is there a way to select a record number when the cmdPrint button is pressed? A code statement preceding 'print' 'where report number = event number'?

Shawn
(thanks for your patience..)

 

[WildHare]

1) you can issue the DoCmd.OpenReport method with a filter option, I think it's

DoCmd.OpenReport "{report-name}", , , "Event Number = " & Forms!myForm-name!EventNumber
[code]

or something like that. 

2) I'd rather have you get the query to respect the criteria you set on it. I think the problem may have something to do with your structure and the possible 1~many's you have. Can you give us some more detail?

3) Just for grins, if you hard-code a single  event number in your query, how many records does it return?


 Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:
[URL unfurl="true"]www.forumco.com/jmhare[/URL]
or my site, [URL unfurl="true"]www.jmhare.com[/URL]

 

[traffas]

When I hard wire the criteria to a specific event number, I get the correct records back. More on my design......

report navigation > opens patient report > opens other specific forms for specific data.

report navigation sets the event number
patient reports form > enters name, date, address, physician and age information specific to patient.......

command buttons open other forms like: Situation, physical assessment, history, home medications, treatment narritive.

all of the above are a one to one relationship

one to many relationships examples as followes:
vital signs, medications administered, iv initiated and EKG strips........

each form has a &quot;event number&quot; whic is atomatically filled in by defaulting it to the value of the previous form's &quot;event number&quot;. The &quot;event number is hidden and disabled to keep the user from mistakenly editing it. And it need be only entered once by user.


Shawn
thanks for your patientce.......

 

[traffas]

Just a little more.... The main reports is based on sub reports....... Each of these subreports are based on a query and then inserted into the main report design. Master and child properties are linked by event number.

Thanks...
Shawn

 

[traffas]

Thank you all very much......

Success..... I retried the criteria in my 14 queries that make up my reports and it worked.... yea...

Many thanks again

Shawn