current record (1 Viewer)

[kirsty]

Hi,


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

Thanks a lot,
K.

 

[Peter Paul]

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

 

[kevgais]

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

 

[Neal]

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.

 

[kevgais]

neal, could you explain step by step?

 

[Neal]

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.

 

[kevgais]

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

 

[Neal]

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.

 

[al718]

missing something in that line of code

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

 

[al718]

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

 

[Rich]

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

 

[vericlese]

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?

 

[Pat Hartman]

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

 

[T13v0hz]

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???

 

[Pat Hartman]

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.

 

[T13v0hz]

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??

 

[Pat Hartman]

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.

 

[T13v0hz]

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

 

[helloworld]

could be...

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

 

[arturo_w]

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