Report filter to show only current record

[fuglyjowls]

Help needed again please

I have managed to link a report to a form where you can double click on the surname and the report opens automatically BUT I want the report to only show the data from the record that I have double clicked - at teh moment the report is coming up with thousands of pages - one for each record.

Can anyone help me with this - I think it's probably something simple but I can't work it out on my own

 

[RuralGuy]

From VBA help:

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

Pass your criteria in the WhereCondition.

 

[fuglyjowls]

Thanks for your help but I don't think it is quite what I need - I want it to automatically open the report and populate it with whichever record I am double-clicking on. I don't want to have to specify the criteria in the command.

Does this make sense? Thanks

 

[RuralGuy]

What code are you using to open the report?

 

[RuralGuy]

What code or method are you currently using to open the report?

 

[fuglyjowls]

In design view of the form (in datasheet view) I have gone to properties (for surname) and where it says "on double click" I have entered a macro which opens the report

 

[RuralGuy]

I've not used macro's before so bear with me. In the OpenReport line of the macro there is a space below for a WhereCondition. Your WhereCondition will look something like: [surname] = Me.surname
I could walk you through the steps to do it with code if you want to post back.

 

[fuglyjowls]

Thanks for getting back to me - in the wherecondition of my macro there is nothing! If I put in [surname] = Me.surname it comes up with a box requesting me.surname

Hope this makes sense

 

[RuralGuy]

Here you will need to be pretty specific. On your form is a control that displays the surname. That control has a name. Access usually defaults the name to the same name as the field to which it is bound. Is the field name 'surname'? If not, you need to change 'surname' to the actual name of the field in the table/query. Because Access does not recognize Me.surname, I'm pretty sure that is not the name of your control or field.

 

[fuglyjowls]

have looked at it all ways but it is definitely called surname

 

[RuralGuy]

Maybe it is because it is a macro. Let's try:
[surname] = [surname]

 

[fuglyjowls]

Tried this but it opened up the report with the surname "holland" no matter which surname I double-clicked. It also had no other data in the report - I'm beginning to think this is impossible. Thanks for sticking with it I really appreciate your help

 

[RuralGuy]

I found a datasheet sample on my system and I'm trying to get it to work. I'll post back when I've got it.

 

[RuralGuy]

Well I finally got mine to work but the macro is very limiting. It was necessary to go through the forms collection to get it to work. I don't know if your datasheet form is a main form or a subform so I'll give you the reference to put in the WhereCondition for both.

If MainForm:
[Process] = [Forms]![MainForm]![Process]

If SubForm:
[Process]=[Forms]![MainForm]![SubFormControlName].[Form]![Process]

You need to use your names for [MainForm] and [SubFormControlName]

 

[fuglyjowls]

Thanks for help but I'm so sorry it still isn't working - I put in your suggested wording and now when I double click it says "enter parameter value - process"

Is there a way to do this without using the macro?

Thanks so much for helping

 

[RuralGuy]

My bad. *My* field was Process. *Your* field is surname, sorry. Replace both Process with surname! Yes there is a way to do it without macros but we're almost there now.

 

[fuglyjowls]

sorry still no joy

it now says "enter parameter value" forms!production order summary!surname

(production order summary is the name of my form).

 

[RuralGuy]

Did you put it in with brackets?
[forms]![production order summary]![surname]

 

[fuglyjowls]

yes - with brackets and ! exactly as you said

 

[RuralGuy]

Is it a main form or a sub form?