Krispy
07-21-2009, 06:05 AM
Hi all,
I've come here out of sheer desperation as I usually like to work things out for myself, but am wasting a lot of time and energy trying fix something which may not be fixable.
Have had a good read of relevant threads here and got a couple of ideas from them, but wonder whether anyone can advise before I continue with the wall head-banging. :(
Am in the process of designing health service database and really want it to be super-user friendly as they've had to put up with interim thing full of bugs since 2005. As I result I may be trying too hard where a simple solution might be better. Am using Access 2003.
System of working is thus:
many patients
many episodes for patients (periods of time when they need the service)
many referrals for each episode
Have therefore set up 3 tables; patients, episodes, referrals, each with unique ID, linked. Queries all work so structure seems sound.
One of the most important parts of this system will be a report which gives info to outside parties. I have it looking good - and it's based on:
1) query on 'patients' with all fields selected but no criteria
2) subreport - source query on 'episodes' with all fields selected but no criteria
3)subreport 2 - source query on 'referrals' with all fields selected but no criteria.
This report works (ie shows 1 patient, 1 episode, 1 referral) when criteria is entered manually (for test) into the source queries. So I know the report is okay.
What I had in mind to feed the report was this lovely little form which helps the user to select the patient by surname, then episode (showing only those episodes available to that patient) and then referral (showing only referrals for that episode) - in 3 neat combo boxes.
Then there's a button which previews the report based on the values in those combo boxes.
This form does what it should. It displays the right selectable data (event procedures all good). However the 'print' button does NOT do what it should - it shows every patient in the table, and if the user were to print, they'd be sending thousands of records to the printer.
I'm pretty sure the code works, so it seems that the values aren't being returned correctly to the 'where' clause. :mad:
I got help with the code syntax from someone clever. :o
The point is I suppose, am I making this more complicated than it need be?
I like the idea I read elsewhere in this forum about printing straight from a button on the form, using those fields. Except I don't know how to tweak that code in the 'where' criteria in query view.
In short, what's the best way to print 1 record from a 3-table report based on 3 queries?
Any advice would be most gratefully recieved.
I would describe my geek 'level' as intermediate. :)
I've come here out of sheer desperation as I usually like to work things out for myself, but am wasting a lot of time and energy trying fix something which may not be fixable.
Have had a good read of relevant threads here and got a couple of ideas from them, but wonder whether anyone can advise before I continue with the wall head-banging. :(
Am in the process of designing health service database and really want it to be super-user friendly as they've had to put up with interim thing full of bugs since 2005. As I result I may be trying too hard where a simple solution might be better. Am using Access 2003.
System of working is thus:
many patients
many episodes for patients (periods of time when they need the service)
many referrals for each episode
Have therefore set up 3 tables; patients, episodes, referrals, each with unique ID, linked. Queries all work so structure seems sound.
One of the most important parts of this system will be a report which gives info to outside parties. I have it looking good - and it's based on:
1) query on 'patients' with all fields selected but no criteria
2) subreport - source query on 'episodes' with all fields selected but no criteria
3)subreport 2 - source query on 'referrals' with all fields selected but no criteria.
This report works (ie shows 1 patient, 1 episode, 1 referral) when criteria is entered manually (for test) into the source queries. So I know the report is okay.
What I had in mind to feed the report was this lovely little form which helps the user to select the patient by surname, then episode (showing only those episodes available to that patient) and then referral (showing only referrals for that episode) - in 3 neat combo boxes.
Then there's a button which previews the report based on the values in those combo boxes.
This form does what it should. It displays the right selectable data (event procedures all good). However the 'print' button does NOT do what it should - it shows every patient in the table, and if the user were to print, they'd be sending thousands of records to the printer.
I'm pretty sure the code works, so it seems that the values aren't being returned correctly to the 'where' clause. :mad:
I got help with the code syntax from someone clever. :o
The point is I suppose, am I making this more complicated than it need be?
I like the idea I read elsewhere in this forum about printing straight from a button on the form, using those fields. Except I don't know how to tweak that code in the 'where' criteria in query view.
In short, what's the best way to print 1 record from a 3-table report based on 3 queries?
Any advice would be most gratefully recieved.
I would describe my geek 'level' as intermediate. :)