Form button help please

[thegunnerman]

Hi everyone, ok I'm having a bit of a nightmare. I've basically just changed jobs and my new employers have found out I have some Access knowledge. So I have now been tasked with making a few changes to their existing database. Basically here is what I have.

The database is used for tracking the progress of trainee's. I can view each trainee's details from a form (Picture, Name, DOB, test results etc).

What I am trying to achieve is to be able to create an end of course report (EOCR) ( to be printed and also saved electronically) containing all the details on the individual. To do this I have written a query with all the relevant info and created the form, all ok so far!!!!!!!!!!!!!!!!!

Here is my problem........... I would like to have the ability to be able to view the individuals report from the form. This would then allow me to print and save it. However I need the button to run the query but only for the student details I am currently viewing. All i have been able to acheive so far is to for the button to run the query and have a criteria in the query so it asks which to students report to create.


Hope this makes sense about what I want to acheive and also I've posted this problem in the correct thread.


Any help would be greatly appreciated.

 

[DreamGenius]

If you've got the button to fire off a query which requests variables, you're more than half way there!

Ideally, though I stand ready to be corrected by those more knowledgeable, you should make the EOCR report that you've written use the query as a record source.

Rather than the query asking you for parameters, you ought to get it to pick them up from the form. This will be in the format " ... WHERE Query.StudentID = [Form]![FormName]![StudentID]"

Then, when you click on the button, the report should open automatically, with the correct student details, and only that student's details. You should have most of the correct query bound to the form.

If you don't already, I'd recommend putting the student identifier from the query or table that is bound to the form on the form, if necessary as a hidden textbox.

 

[thegunnerman]

Hi,
Thanks for that rapid response and from what your telling me to do it sounds like you know exactly what I'm after.

OK I've got the EOCR form looking at the query as the record source. However I am struggling with the next bit! (Sorry I'm not too good at this)

I've taken the paramaters from off of the query so it now returns a huge report with all students on the DB. How do I get the Report to Pick up the paramaters.

... WHERE Query.StudentID = [Form]![FormName]![StudentID)

Thanks for your help its greatly appreciated

 

[DreamGenius]

First, a question: Does your Form, which has the button on it to open the Report, have a Field (hidden or visible) which contains the unique identifier for that record?

If not, you need to add one. Then, with the Form open in Design view, open the Query that sits the Report is bound to, also in Design view. Again, the unique identifier needs to be in the Query, and we'll add the criteria to it:

1. Click in the Criteria: box under the unique identifier field
2. Click on the Build button on the toolbar - looks like a magic wand
3. This opens a dialog box like Browser.PNG below
4. Use the navigation to browse through Forms > Loaded Forms > Your Form to pick the field and value
5. Double-click the <Value> entry to build the code for you! See Entered.PNG below
6. Click [OK] to transfer this code back to the Criteria box of your Query, and the query is dependant on the Form.

If the form isn't open, you'll get asked for the parameter. If it is, the Report should just run, showing the details just for the student requested, as the Query will filter the result.

Good luck

 

[thegunnerman]

Hi mate,

Your a star. Job done and all working well. Thanks for your time it's greatly appreciated.