help!!! report with parameterised query

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

I have a report which I want to parameterise. I have
already added a parameter to the query that it uses and it
works fine. However, I want the user to be able to select
the value of the parameter from a list, rather than have
to type the whole value. The values need to come from a
column in one of the tables. What is the best way of
doing this?

I thought about using a form that has a combo-box field
bound to the relevant table column, and then the value
entered in this field is used in the report. However, I'm
not 100% how to get this to work. I have Access 97, and I
know I would execute the report using a docmd, but how
would I pass it the parameter which then needs to be
passed to the query??? And is there an easier way to do
this anyway!!!

Cheers,

Dan.
 
dan said:
I have a report which I want to parameterise. I have
already added a parameter to the query that it uses and it
works fine. However, I want the user to be able to select
the value of the parameter from a list, rather than have
to type the whole value. The values need to come from a
column in one of the tables. What is the best way of
doing this?

I thought about using a form that has a combo-box field
bound to the relevant table column, and then the value
entered in this field is used in the report. However, I'm
not 100% how to get this to work. I have Access 97, and I
know I would execute the report using a docmd, but how
would I pass it the parameter which then needs to be
passed to the query???
Click to expand...

That's the way I would do it.

First, get rid of the parameter in the query. When the
report is run from the database window it should display all
the data without any prompts.

Then report can be restricted to the value in the combo box
by specifying OpenReport's WhereCondition argument.

For a numeric type field:
DoCmd.OpenReport "reportname", acViewPreview, , _
"tablefield = " & Me.thecombobox

For a text type field:
DoCmd.OpenReport "reportname", acViewPreview, , _
"tablefield = """ & Me.thecombobox & """"

And is there an easier way to do
this anyway!!!
Click to expand...

I don't think so, the above approach is not particularly
difficult.
 
This seems to be a similar problem than what I am trying to resolve, so I
tried it, I can't get to work either. It can't find the combo box field

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenReport "7d-Any Date Payroll by Empl with Burden",
acViewPreview, , "employeeName" = """ & Me.Combo Employee & """""
DoCmd.FindRecord Me![EmployeeSelect]
End Sub
 
Annelie said:
This seems to be a similar problem than what I am trying to resolve, so I
tried it, I can't get to work either. It can't find the combo box field

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenReport "7d-Any Date Payroll by Empl with Burden",
acViewPreview, , "employeeName" = """ & Me.Combo Employee & """""
DoCmd.FindRecord Me![EmployeeSelect]
End Sub
Click to expand...


If you must use names with a space or other funky characters
in it, then you must enclose it it in square brackets.

.. . . , , "employeeName" = """ & Me.[Combo Employee] & """""

It looks like you have this code in the report's Open event
procedure. If so, that is all wrong - it should be in the
form's print report command button's Click event procedure.
 
Back
Top