Printing Report by ID Number 1

[hwmueller]

Hi

I want a user to enter their unique ID number and then be able to preview and print a report with their selections (entered earlier). The user ID is the same ID number they used to enter their selections in the first place so it is really just a case of getting the info from the tables and puting it into the report. How do I construct the form which asks for their user number and then link this to the report?? Pls provide the entire code as I am a novice at this!! Thans very much and a merry Christmas to those who celebrate it.

Hans

 

[kevinclark]

Rather then try and second guess how you have your database set up or how you have the various object named I thought I would just use this example and hopefully you can get the idea from it.

Lets say you have a simple database with 1 table called tblNames. The table consists of 3 fields. The 1st field is Fname, the 2nd is Lname and the 3rd is Address.

First Step is to create a Select Query. In Design view add the table 'tblNames' and then drop all its fields onto the Query design Grid. Save this Query as qryLName.

Next step is to create a report. Just use the Report Wizard for now. When it asks you which table and fields to base the report on, you will select 'tblNames' and again include all its fields. You can set everything else that the wizard may ask to your preference. Save this report as rprtNames.

Next step is to create a Form. In design view add 1 TextBox (name it txtLName) and 1 CommandButton (name it cmdReport). If you have the ‘Control Wizards’ turned on then as soon as you add the CommandButton it will ask you what action you want the Command Button to take when pressed. Your selection would be to preview the Report called ‘rprtNames’. Save this Form as frmLookUp.

Next Step is to tie the Query called ‘qryLName’ to this Form. To do this you need to return to and open your Query called ‘qryLName’. Under the field named ‘Lname’ and in the Criteria section you will type the following line: Forms![ frmLookUp]![ txtLName]
Save your changes.

Close the Report and the Query. Open the form type in a name (a last name that exists in the table) and press the command button.


Hope this helps

 

[hwmueller]

Hi Kevin

Thanks very much for your help. I have done what you described and when I call up the query and enter the ID number (instead of the user name as you suggested) and the correct record appears. So far so good! I also created the form as you described and this is where the problem occurs. I enter the users ID number into the text box field in the form and click on print preview. Instead of showing only the users record, it shows all of them. In other words it seems that the Print preview button isnt linked to the query nor the text box. I can write what ever I want in the text box and click on print preview and it still shows the entire report! How do I link these 2 items to the query?

Thanks again for your help

Hans

 

[kevinclark]

Hi Hans

This is probably what you meant but just to clarify. The Command Button is not linked to the Query. The Command Button itself does nothing more then activate the Report. The Report however is linked to the Query. The Query then has a parameter that points back to the TextBox on the Form.

Try Opening the Query in Design View and under your ID Field, in the Criteria Section confirm that you have a parameter similar to the following:
Forms![The Form Name]![The TextBox Name]

If the Query seems correct, try recreating the report. Again use the Report Wizard and point the Report to the Query (Not the Table). If you by accident point the Report to the Table instead of the query, then as described, all records will be displayed.


If the problem is not corrected by one of the two above checks then try returning to the Form and open it in Design View. Make sure the Control Wizard is turned on and then add a new Command Button. As soon as you add a new command button the Control Wizard will pop up. It will prompt you to select a Category and an Action from its lists. The Category to choose will be Report Operations and the action will be Preview Report. The Wizard will then prompt you to select the report that it should Preview (Again select the Report - not the table or the query). Continue answering any questions it asks and then try running it again.

 

[hwmueller]

Hi Kevin

Once again thanks a million – I have got a huge step closer to solving the problem. Basically the problem was as you assumed, namely that the report wasn’t linked to the query. What happens now is that when I enter the number into the textbox and click preview, a query box appears with the label Forms! Print! IDNum. After I enter the number here again (I can also leave the text box blank!) a second window pops up with the label ID. Once I enter the correct number here (or leave it blank, it doesn’t matter) the correct record appears in a preview screen. So the problem now seems to be that the text box (called IDNum) isn’t linked to the query. In design view it also says unbound. Any ideas?

Thanks again for your fantastic help!!

Hans

 

[kevinclark]

Hi Hans

You should not bind the TextBox to anything. I think the remaining problem likely lay in the Parameter itself or in the naming of your Form.

First re-confirm that the parameter that you have set in your query has everything spelled correctly (Its an error I often make).

If the spelling is correct then it may be possible that Access is getting confused by your use of the word Print. The word Print is a reserved word by many programs. To confirm that this is not causing the problem you could rename your Form to something like frmPrint and then change the parameter in the Query to read

[Forms]![frmPrint]![IDNum]

 

[hwmueller]

Hi Kevin

Thanks for your speedy reply. I have made the adjustments you suggested but without much luck. Once I click the Print preview button it still comes up with a ‘Enter Parameter Value’ box. I enter the number here and only after clicking ok does the report appear. I am doing this all in Office XP – possibly the reason?

Hope that this isn’t messing your about too much!

Hans

 

[kevinclark]

Hi Hans

Has me a bit stumped then. I can not really see it being an Office XP problem but then again, all versions of MS Office has their quirks. Unfortunately I don't have a copy of Office XP to experiment with right now, so you might want to repost the Question / Problem (explain where your at now) and see if anyone else has a solution to help you out. Sounds like your close though so keep with it and good luck. If you get a chance, let me know how you make out.

 

[hwmueller]

Hi Kevin

In case you read this - I managed to solve the problem!! Strangely enough all it needed was an equals (=) sign in front i.e =[forms]! etc.

Thanks very much for yuor help

Hans

 

[kevinclark]

Thats great Hans, good for you