Print report as per form details (1 Viewer)

[equaliser]

I have 2 issues with printing a report. I know this has been asked before but i dont seem to be able to get the results i re quire from them.

I currently have a button set up on a form to preview a report. When clicked it runs a query which askes me to place the Auto id no in. it then filters the form to this ID. How do i get it to automactically place the ID number in so the user doesnt have to entre it.

The second problem is when you look at the above query it lists several enteries for the same ID no. This seems to be dictaded by the sub report and the amount of records in there. I.E. 4 records printed records.

any help would be great.

cheers

EQ

 

[lightray]

For the 1st problem, you need to pass the ID to the DoCmd statement when you produce the report. Something like: DoCmd.OpenReport stDocName, acPreview, , "DivisionID = " & cboDivision

See if problem 2 persists after this and post back.

 

[equaliser]

For the 1st problem, you need to pass the ID to the DoCmd statement when you produce the report. Something like: DoCmd.OpenReport stDocName, acPreview, , "DivisionID = " & cboDivision

See if problem 2 persists after this and post back.

Thks for replying Lightray, Tried the above code it came with an error( extra expression. This is my original code for the button

Private Sub Command157_Click()
On Error GoTo Err_Command157_Click

Dim stDocName As String

stDocName = "TSTL NPR REPORT"

DoCmd.OpenReport stDocName, acPreview

Exit_Command157_Click:
Exit Sub

Err_Command157_Click:
MsgBox Err.Description
Resume Exit_Command157_Click

End Sub

Can you describe what the "DivisionID = " & cboDivision
equals

regards EQ

 

[lightray]

You should use a report name with no spaces in it as this may cause problems when passed through to the DoCmd. something like rptTstlNpr or rptTSTL_NPR although it is not good practise to use a underscore '_' character either.

The "DivisionID = " & cboDivision is the parameter input bit. You should use your your Auto ID No here so that when it is entered it is passed through to the DoCmd statement. The first bit will be the name as it appears in the query (and used on the report), and the cboDivision bit, will be the name of the field on your input form that the user selects the Auto ID No from. Don't forget the two commas as in the example.

The surrounding quotes are needed, and it's gets a little more difficult if the field type is not a number. let me know how you get on.

 

[Wiz47]

You should use a report name with no spaces in it as this may cause problems when passed through to the DoCmd. something like rptTstlNpr or rptTSTL_NPR although it is not good practise to use a underscore '_' character either.

I have been programming since the late '70s in multiple languages and have used the underscore consistently throughout. I have never had a problem with a call because of it. I understand why spaces should be avoided, but am curious as to your reasoning for avoiding the underscore.

 

[lightray]

It is not an imperative, it is something that was explained on a post here that I read sometime ago. Check out this thread, though it is not the one I remember using. http://www.access-programmers.co.uk/forums/showthread.php?t=104645&highlight=underscore

 

[Wiz47]

It is not an imperative, it is something that was explained on a post here that I read sometime ago. Check out this thread, though it is not the one I remember using. http://www.access-programmers.co.uk/forums/showthread.php?t=104645&highlight=underscore

Okay - got it. (Thanks for the link) Some like it, some don't and for various personal reasons - the only one technical having to do with porting the code to another language. I guess it's all a matter of taste. I use underscores to separate and make fields more meaningful. Like First_Name and Last_Name. Then I'll name my unbound fields in the search form txtFirst_Name and txtLast_Name. This way I can always reference one to the other when setting up a tab order or calling one to the other in a long or complex query. Each to their own I guess.

Thanks again for the explanation.

 

[equaliser]

Lightray,

Sorry for not getting straight back. Been away yet again. I've put in the comands as suggested. It hasnt worked. In the query I have set up I still have the criteria comment [type npr ID no] which filters the records. Is there not something i can write into the query which will read the auto ID on the current form and filter accordingly.

cheers eq

 

[lightray]

Hi, can you post your code? or are you able to upload that part of the mdb? a quick way to do this is duplicate the mdb and then remove all the irrelevant objects.

 

[equaliser]

Thanks for you help lightray.

The code is as follows.

Private Sub Command157_Click()
On Error GoTo Err_Command157_Click

Dim stDocName As String

stDocName = "TSTLNPRREPORT"

DoCmd.OpenReport stDocName, acPreview, , "ID = " & ID

Exit_Command157_Click:
Exit Sub

Err_Command157_Click:
MsgBox Err.Description
Resume Exit_Command157_Click

End Sub

'ID' is the auto number field given the default name by Access. Its just been dragged into the form. The 'ID' is found in the table NPRINPUT. I've inherited this data base and modified it greatly. I'm just going through altering the name of things as per your advice on no spaces on naming. I will try probably later this weekend breaking it all down to post the actual database.

Thanks again for your time and effort.

EQ