Report using Parameter query

[Mahesh]

Hi

I am not sure if this is the right way or not but it works.

I created a dialog box form which has three fields.

1. Enter Ethnic Group
2. Enter Starting Date (mm/dd/yyyy)
3. Enter Ending Date (mm/dd/yyyy) and I put a command button named OK
which opens the report created using the query withe above parameters.

The report works perfect.

Now I want the form to be a blank when opened and I also need the
report header to show the parameters i.e " List of Clients From Asian
Ethnic Group referred between 05/01/2006 & 09/01/2006 ".

Is it possible.

Thanks

 

[John Spencer]

If the Dialog Box form remains open, you can refer to its controls on the
report.

If you close the Dialog box form then you can pass the values you have using
the OpenArgs argument (in Access2003).

If you are using an earlier version of Access, you could assign the values
to a global variable and then use VBA to retrieve the values. OR instead of
closing the form when the OK button is clicked, set the form's visible
property to False. That way you can reference the controls on the form in
the report. In the report's close event, you can then close the dialog box
form.

So, first thing to help us help you is to post the code behind the command
button. That will allow us to see how you are using the form's controls.

 

[Mahesh]

Hi John

I have the following code behind the On Click for OK command button.


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "Ethnicity"
DoCmd.OpenReport stDocName, acPreview
Me.Visible = False

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks
Mahesh

 

[John Spencer]

Ok, since the form is still open (it is just not visible) you can refer to
the form's controls on your report.

That could be something like assigning the control source on the report to
be

= " List of Clients From " & [Forms]![YourFormname]![EthnicControl] & "
Ethnic Group referred between " & [Forms]![YourFormname]![StartDateControl]
& " and " & [Forms]![YourFormname]![EndDateControl]

 

[Mahesh]

Hi John

Thanks. It worked perfect.

I have one more question.

When I open the form and enter data and click OK. The report opens and
forms closes ( I am not sure if this closing or is it hiding ). But
when I go back and click on the form it still shows the previous
entered information rather than the fields blank. ( might be the form
is hiding than closing).

Is there anyway that the form fields are blank when I reopen it .

Thanks in advance.

Mahesh

Ok, since the form is still open (it is just not visible) you can refer to
the form's controls on your report.

That could be something like assigning the control source on the report to
be

= " List of Clients From " & [Forms]![YourFormname]![EthnicControl] & "
Ethnic Group referred between " & [Forms]![YourFormname]![StartDateControl]
& " and " & [Forms]![YourFormname]![EndDateControl]

Hi John

I have the following code behind the On Click for OK command button.


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "Ethnicity"
DoCmd.OpenReport stDocName, acPreview
Me.Visible = False

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks
Mahesh

 

[John Spencer]

Since you are hiding the form (visible = false) then the form will keep the
last entered values.

One way to handle this is to use the REPORT's close event to close the form.
At this point you should no longer need the form.

Use the following in the report's close event
DoCmd.Close acForm, "MyDialogFormName"

Hi John

Thanks. It worked perfect.

I have one more question.

When I open the form and enter data and click OK. The report opens and
forms closes ( I am not sure if this closing or is it hiding ). But
when I go back and click on the form it still shows the previous
entered information rather than the fields blank. ( might be the form
is hiding than closing).

Is there anyway that the form fields are blank when I reopen it .

Thanks in advance.

Mahesh

Ok, since the form is still open (it is just not visible) you can refer
to
the form's controls on your report.

That could be something like assigning the control source on the report
to
be

= " List of Clients From " & [Forms]![YourFormname]![EthnicControl] & "
Ethnic Group referred between " &
[Forms]![YourFormname]![StartDateControl]
& " and " & [Forms]![YourFormname]![EndDateControl]

Hi John

I have the following code behind the On Click for OK command button.


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "Ethnicity"
DoCmd.OpenReport stDocName, acPreview
Me.Visible = False

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks
Mahesh

John Spencer wrote:
If the Dialog Box form remains open, you can refer to its controls on
the
report.

If you close the Dialog box form then you can pass the values you have
using
the OpenArgs argument (in Access2003).

If you are using an earlier version of Access, you could assign the
values
to a global variable and then use VBA to retrieve the values. OR
instead
of
closing the form when the OK button is clicked, set the form's visible
property to False. That way you can reference the controls on the
form
in
the report. In the report's close event, you can then close the
dialog
box
form.

So, first thing to help us help you is to post the code behind the
command
button. That will allow us to see how you are using the form's
controls.


Hi

I am not sure if this is the right way or not but it works.

I created a dialog box form which has three fields.

1. Enter Ethnic Group
2. Enter Starting Date (mm/dd/yyyy)
3. Enter Ending Date (mm/dd/yyyy) and I put a command button named
OK
which opens the report created using the query withe above
parameters.

The report works perfect.

Now I want the form to be a blank when opened and I also need the
report header to show the parameters i.e " List of Clients From
Asian
Ethnic Group referred between 05/01/2006 & 09/01/2006 ".

Is it possible.

Thanks

 

[Mahesh]

Hi John

Thanks. that worked perfect.

Mahesh

Since you are hiding the form (visible = false) then the form will keep the
last entered values.

One way to handle this is to use the REPORT's close event to close the form.
At this point you should no longer need the form.

Use the following in the report's close event
DoCmd.Close acForm, "MyDialogFormName"

Hi John

Thanks. It worked perfect.

I have one more question.

When I open the form and enter data and click OK. The report opens and
forms closes ( I am not sure if this closing or is it hiding ). But
when I go back and click on the form it still shows the previous
entered information rather than the fields blank. ( might be the form
is hiding than closing).

Is there anyway that the form fields are blank when I reopen it .

Thanks in advance.

Mahesh

Ok, since the form is still open (it is just not visible) you can refer
to
the form's controls on your report.

That could be something like assigning the control source on the report
to
be

= " List of Clients From " & [Forms]![YourFormname]![EthnicControl] & "
Ethnic Group referred between " &
[Forms]![YourFormname]![StartDateControl]
& " and " & [Forms]![YourFormname]![EndDateControl]

Hi John

I have the following code behind the On Click for OK command button.


Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "Ethnicity"
DoCmd.OpenReport stDocName, acPreview
Me.Visible = False

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks
Mahesh

John Spencer wrote:
If the Dialog Box form remains open, you can refer to its controls on
the
report.

If you close the Dialog box form then you can pass the values you have
using
the OpenArgs argument (in Access2003).

If you are using an earlier version of Access, you could assign the
values
to a global variable and then use VBA to retrieve the values. OR
instead
of
closing the form when the OK button is clicked, set the form's visible
property to False. That way you can reference the controls on the
form
in
the report. In the report's close event, you can then close the
dialog
box
form.

So, first thing to help us help you is to post the code behind the
command
button. That will allow us to see how you are using the form's
controls.


Hi

I am not sure if this is the right way or not but it works.

I created a dialog box form which has three fields.

1. Enter Ethnic Group
2. Enter Starting Date (mm/dd/yyyy)
3. Enter Ending Date (mm/dd/yyyy) and I put a command button named
OK
which opens the report created using the query withe above
parameters.

The report works perfect.

Now I want the form to be a blank when opened and I also need the
report header to show the parameters i.e " List of Clients From
Asian
Ethnic Group referred between 05/01/2006 & 09/01/2006 ".

Is it possible.

Thanks