Hi,


can anyone tell me the simplest way to send the current record only to a report.

Thanks a lot,
K.

One way to do this is to have a unique number assigned to the record, for example the autonumber. Then build a the report to pull the data from a query which will prompt you for the record number.

I tried fooling around with getting this to work automatically, but did not have time to figure out a cleaner way.

Sorry, hope it helps,
Peter Paul

kirsty, have you solved your problem yet? if so could you e mail me the solution as i have the same problem. thanks.

I put a command button on the form showing the record and limit the output in the On Click property.
This is sample code:
DoCmd.OpenReport "yourReportname", acPreview, , "[UniqueIdFieldName]= forms!yourFormName!UniqueIdFieldName]"
That tells it to open the report in preview mode and to show only the record with the same unique ID as is showing on your form.

neal, could you explain step by step?

I'm assuming you've selected the current record using a form.
Put a command button on the form. If you use the wizard, it will ask you what the button is supposed to do. Tell it you want to do a report operation, then print preview.
Then follow the wizard prompts until you are done.
When the button is on the form, open the properties of the button. Select events. One will say "On click". Click in that field and at the right you'll see a down arrow and an elipses (...) beside it. Click on the elipses, then go to code builder. You'll see that most of what I said is already there: Ie, Docmd.OpenReport etc.
After acPreview, add two commas and this information: (Including quotes.)
"[UniqueIdFieldName]= forms!yourFormName!UniqueIdFieldName]"
What that tells Access to do is open the report and show only the record that has the same Unique ID number that is on the record you've selected with your form.
I hope that helps.

the program work but didnt print the current record
any thoughts.
is it really that hard i thought it would be quite easy?

Do you want to print or see a preview of the report? If you want to print, instead of acPreview, type acViewNormal.
Or, if the report is opening in preview, you can print it the same way you would any document, by clicking on the printer icon, or by clicking on File/Print.

Neal, you were missing one of these [ in the line of code for the last UniqueIdFieldName

it should look like this:

DoCmd.OpenReport "yourReportname", acPreview, , "[UniqueIdFieldName]= forms!yourFormName![UniqueIdFieldName]"

i tried and tried until i saw that the two didnt match

it works tho- i just tried it - and i know nearly nothing about visual basic

if anyone is still confused, do this:

deleate whatever preview button you were previously using on your form

add a new button that previews the report you want

IMMEDIATLY(dont add anything else) right click and go to the build event option, choose code builder

that should bring you to the visual basic window

scroll all the way to the bottom (thats where the code for your button should be because it was the last thing you added)

(type it exactly as it appears, that means with the " " , just change the reportname, ect... to what your stuff is actually named)

there should already be a line that says:

DoCmd.OpenReport "yourReportname", acPreview

you should add this to the end of it:

, , "[UniqueIdFieldName]= forms!yourFormName![UniqueIdFieldName]"

hence, when it is done, you will have this:

DoCmd.OpenReport "yourReportname", acPreview, , "[UniqueIdFieldName]= forms!yourFormName![UniqueIdFieldName]"


i hope that solves everything

You should also add
DoCmd.RunCommand acCmdSaveRecord
before the open report statement, otherwise newly added records might not get printed

Ok, I have created the report button, added the extra VB code and tried to test it. What i am getting is a prompt for unique field. Is this what it is supposed to do? I would like to have the report automatically filter for currrent record and output to preview. Can this be done?

You probably have a typo. Make sure that your control reference is correct.

I have tried this with my code:

Dim stDocName As String

stDocName = "Service Report"

DoCmd.OpenReport stDocName, acPreview, , "[Job No] = forms!Service Job Card![Job No]"


However, I get the error message:

Syntax error (missing operator) in query expression '(Job No] = forms!Service Job Card![Job No])'.

What am I doing wrong??? :confused:

You should NEVER use names that contain spaces. If you do, you MUST use square brackets! to surround the poor name.

DoCmd.OpenReport stDocName, acPreview, , "[Job No] = forms![Service Job Card]![Job No]"

Also, if the [Job No] is on the current form you can use:

DoCmd.OpenReport stDocName, acPreview, , "[Job No] = " & Me.Job_No
Notice how VBA renames your field name. It cannot work with embedded spaces.

I tried with square brackets and with me.Jobs_Job_No and I get the report to pop up but it contains no information. Now what can I do??

Also, should I get rid of all these spaces between names, and how will that afffect the rest of my tables, forms, etc etc etc??

Look up the article on Name AutoCorrect in the MSDN library. Used properly, this feature will "follow" the name changes from tables to queries, forms, and reports. You will need to make any necessary code or macro changes.

If the report is not showing anything, is it because you forgot to save the current record.

DoCmd.RunCommand acCmdSaveRecord
Should preceed your OpenReport Method.

I added the DoCmd.Runcommand acCmdSaveRecord.

Tried it again..

Still blank. One Page. No data. ?? I don't get it

I have 140 records there when I bring the report up normally, and they are definitely all saved. Do I need to specify all the data to go on the page because I have more data on the form than on the form.

Stupid question now....
Would it be because they are not coming off the same query??
Would I have to make a new query using the information off all three forms (two subforms)??
or
Do I make subreports for the information from the subforms? (if this makes sense)

Ok... now I tried this information on a dialogue box form which used to have a macro trying to do the same thing (which didn't work) and it works!?!? I still don't understand why this won't work on the form, because it is the exact same information, and the code is basically the same, just
"[Job No] = forms![Print Dialogue Box]![Job No] instead of Me.Jobs_Job_No

is that unique field on the form retreiving a number or text?

I had the same issue and I used the recommendations on this thread and it worked. Thanks !

I have followed everything, but I am getting all the records, I have now spent 6 hours on this its driving me mad, please can anyone help me, my event procedure listed blow.

Dim strDocName As String
Dim strWhere As String
strDocName = "Wilton 3 shot figured specification"
strWhere = "[ID]=Forms![Wilton 3 Shot Figured]![ID]"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport strDocName, acPreview, , strWher

Try this:

strWhere = "[ID]= " & Forms![Wilton 3 Shot Figured]![ID]

The difference is that in my example, the value for the argument is evaluated before the where argument is sent to the report. So if you were to stop the code and hover over the strWhere variable, you would see something like:

"[ID] = 483" -- with 483 being the ID of the current record.

Its worked!!! thanks Pat I had given up!!

Hi All,

Not sure if this post is still any interest. I wanted to thank all previous posters for their help. It allowed me get a code working, however I am experiencing one problem I am hoping someone can help with.

The code works for any field (text or numerical) in my form except for the ID autonumber field. And the crunch is that the ID field is the only unique field in my table. When I use the code with ID entered as the field name I get a pop-up requesting user "Enter Parameter Value". If I switch the field name to any other field on the form, this message does not pop-up and the current record is selected and printed accordingly. I have also tried switching the autonumber field name to "Entry" and the same window pops-up.

Any ideas on where I could correct this would be greatly appreciated. Thanks in advance.

Sounds like you are invoking the Parameter function when using [square brackets]. Check all the references to the fields are correct.

I have tried everything in this thread to get a report to print only the current record. However, each time I try to print I am getting a prompt for the unique ID just like a previous user. I have tried to find any typos but everything looks correct. This is my code:

Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
DoCmd.RunCommand acCmdSaveRecord
Dim stDocName As String

stDocName = "rptTabbedActionPlanCopyReport"
strWhere = "[PatientID]=" & Me![PatientID]
DoCmd.OpenReport stDocName, acPreview, , strWhere
Exit_cmdDisplay_Click:
Exit Sub

Err_cmdDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdDisplay_Click
End Sub

Can anyone find offer any suggestions? Also, just to be sure: is the control name to be used in the Where statement? For example:

"[Unique_Field]=" & Me![Name_of_control]

Thanks