restrict report to display current record only

[Bobnz]

I have a form with subform that I saved as a report but when the report opens it displays all records not just the forms current record. I have tried setting the datasource to show
me!formname!control where control has the current record id
but get a datatype mismatch.

Anyone know how I can do this.

B

 

[hymn]

DoCmd.OpenReport "YourReportName2, acPreview, , "stay.stay_id = " & stay_id

stay = table
stay_id = Primary key



Hope this helps
Hymn

 

[Bobnz]

Hi thanks for the reply, I used something similar

DoCmd.OpenReport stDocName, acPreview, , "tblclient_jobnumber = " & Me.tblclient_jobnumber & ""

B

 

[GingerR]

you don't need the final

& ""

 

[techwizz]

Can someone explain this more...?

Is the

DoCmd.OpenReport stDocName, acPreview, , "tblclient_jobnumber = " & Me.tblclient_jobnumber

the code for the report button? Thats what it looks like, but i seem to be getting an error.

 

[techwizz]

Here is my code

DoCmd.OpenReport stDocName, acPreview, "tblMaster Sheet_PatientID = "&Me.tblMaster Sheet_PatientID


the current form is a form with a subform. the 2 tables.

Master -table primary key PatientID
Visit -table for patient visit, but it pulls data from Codes

Codes -table with all medical codes

 

[GingerR]

what's the error?
please post all of your code.

also it looks like your query that the form is based on has two fields "PatientID" so that's why it's naming it "tblMaster Hseet_PatientID"

Also, since there's a space in that name, you should put it in square brackets.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[techwizz]

Here is the code. With the table with brackets...

Private Sub report_Click()
stDocName = "Bill"
DoCmd.OpenReport stDocName, acPreview, "[tblMaster Sheet]_PatientID = "&Me.tbl[Master Sheet]_PatientID
End Sub

The error is a compile error

 

[GingerR]

the entire name of the field should be in brackets. Also make this a WHERE clause and leave the filter clause blank (see the two commas after "acPreview")

DoCmd.OpenReport stDocName, acPreview, , "[tblMaster Sheet_PatientID] = " & Me.[tblMaster Sheet_PatientID]


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[techwizz]

That gives me the error Cant find sheet '|' ...

here is a demo file with the structure. Put it on utteraccess, couldnt seem to get it to work either.

http://www.utteraccess.com/forums/download.php?Number=628247

 

[techwizz]

Sorry bout that link..

here is a correct on

http://yourcodes.com/demo.zip

 

[techwizz]

I figured it out.

I would like to say thank you very much for helping me.

 

[GingerR]

What was the solution? It would be nice if you posted it here--it could help someone else.

Thanks--g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hansdebadde]

I wish techwizz had posted his answer. I have three different forms that I want to be able to have a command button to preview a report for the current record only. I have used the same format for all three but only two work.
Here is the whole enchilada:

Private Sub Command959_Click()
On Error GoTo Err_Command959_Click

Dim stDocName As String

stDocName = "RptF7On"
DoCmd.OpenReport stDocName, acPreview, , "[claimid]= " & Me.claimid

Exit_Command959_Click:
Exit Sub

Err_Command959_Click:
MsgBox Err.Description
Resume Exit_Command959_Click
End Sub

I keep getting asked for the claimid parameter value? Claimid is an autonumber primary key. When I type in the current record claimid at the prompt it does not work anyway, I just get a blank form. Any ideas? I know it is not a typo issue, I have copied the others over and just changed the Command button number

 

[GingerR]

Is ClaimID a number or text? Does RptF7On actually have ClaimID as a 'field'? If you hardcode in the WHERE portion of your string to be something you know is there, does it work?

i.e.

DoCmd.OpenReport stDocName, acPreview, , "[claimid]= 7"

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[hansdebadde]

Thanks GingerR. ClaimID is a number. No it is not a field in any of the three different reports. I don't have any other unique modifiers but I did try putting ClaimID in the report to see if it would work and it didn't.

 

[hansdebadde]

I tried your suggestin "[claimid]=74" and I still was asked for a parameter value?

 

[GingerR]

What's your report's recordsource? Please post it here.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html