Supressing enter paremeter value in subreports -----M-----

[Mark909]

I have a report that a 2 subreports.

Each subreport is based on the same qry but different data is displayed

The main report and subreports have a paremeter query "Enter Project_ID" on
field

How can i stop the report asking for the parameter several times?

 

[BruceM]

You could use a different query for the subreports. Are you saying the main
report and the subreports use the same query? A little more description of
the database's purpose and structure may help.

 

[Ken Sheridan]

As the parameter, rather than a simple prompt, reference a control on an
unbound dialogue form, e.g.

Forms!frmProjectDlg!txtProjectID

And open the report from a button on the form. Even better than a text box
would be a combo box which lists the projects so a user merely has to select
one from its list. Normally in a case like this the ID would be hidden and
the combo box would list more meaningful names or descriptions by being set
up along these lines:

RowSource: SELECT Project_ID, Project FROM Projects ORDER BY Project;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If you want to cater for the report being opened directly from the
properties sheet you can put the following in its Open event procedure:

Const conFORMNOTOPEN = 2450
Const conFORM = "frmProjectDlg"
Dim frm As Form

On Error Resume Next
Set frm = Forms(conFORM)
If Err = conFORMNOTOPEN Then
Cancel = True
DoCmd.OpenForm conFORM
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

If you do include such code in the Open event procedure and you open the
report from code in a module other than the frmProjectDlg's you'll need to
handle the error which occurs when the return value of the Cancel argument is
set to True, e.g.

Const conOPENCANCELLED = 2501
Const conREPORT = "rptProjects"
Const conFORM = "frmProjectDlg"

On Error Resume Next
DoCmd.OpenReport conREPORT, acViewPreview
Select Case Err.Number
Case 0
' no error
Case conOPENCANCELLED
' open dialogue form
DoCmd.OpenForm conFORM
Case Else
'unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

Ken Sheridan
Stafford, England

 

[Mark909]

Thanks Ken that brilliant! Ive got it set up now

As the parameter, rather than a simple prompt, reference a control on an
unbound dialogue form, e.g.

Forms!frmProjectDlg!txtProjectID

And open the report from a button on the form. Even better than a text box
would be a combo box which lists the projects so a user merely has to select
one from its list. Normally in a case like this the ID would be hidden and
the combo box would list more meaningful names or descriptions by being set
up along these lines:

RowSource: SELECT Project_ID, Project FROM Projects ORDER BY Project;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

If you want to cater for the report being opened directly from the
properties sheet you can put the following in its Open event procedure:

Const conFORMNOTOPEN = 2450
Const conFORM = "frmProjectDlg"
Dim frm As Form

On Error Resume Next
Set frm = Forms(conFORM)
If Err = conFORMNOTOPEN Then
Cancel = True
DoCmd.OpenForm conFORM
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End If
End If

If you do include such code in the Open event procedure and you open the
report from code in a module other than the frmProjectDlg's you'll need to
handle the error which occurs when the return value of the Cancel argument is
set to True, e.g.

Const conOPENCANCELLED = 2501
Const conREPORT = "rptProjects"
Const conFORM = "frmProjectDlg"

On Error Resume Next
DoCmd.OpenReport conREPORT, acViewPreview
Select Case Err.Number
Case 0
' no error
Case conOPENCANCELLED
' open dialogue form
DoCmd.OpenForm conFORM
Case Else
'unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select

Ken Sheridan
Stafford, England