report parameter from form

[mark.macumber]

Hi All,
Im new to MS Access VBA programming and reporting but im an
experienced programmer in general. I need to do a MS Access report, and
find that the default pop-ups for parameters are plain and boring. I
would like to use a form to ask the user for input.

I have heard this can be done but cant get it to work.

Here is what I have done.
* Created Report
* Created Form
* Added Event to Report to open form:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateInput", acNormal
End Sub

* Added SQL in report query to use form params:

WHERE ExpiryDate < [Forms]![DateInput]![LabelStart]

This all works to a degree. But my form is full screen, and my report
isnt taking into account the parameters that I type into the form.

Can someone please help me?

Most Appreciated!
Kram

 

[fredg]

Hi All,
Im new to MS Access VBA programming and reporting but im an
experienced programmer in general. I need to do a MS Access report, and
find that the default pop-ups for parameters are plain and boring. I
would like to use a form to ask the user for input.

I have heard this can be done but cant get it to work.

Here is what I have done.
* Created Report
* Created Form
* Added Event to Report to open form:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateInput", acNormal
End Sub

* Added SQL in report query to use form params:

WHERE ExpiryDate < [Forms]![DateInput]![LabelStart]

This all works to a degree. But my form is full screen, and my report
isnt taking into account the parameters that I type into the form.

Can someone please help me?

Most Appreciated!
Kram

While you have opened the form you haven't stopped the report opening
to wait for the parameter.

Open the form in dialog, which will cause processing to wait for the
form to close or become not visible.

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateInput", , , , , acDialog
End Sub

Add a command button to the form. Code it's click event:
Me.Visible = False

Add code to the report's Close event:
DoCmd.Close acForm, "DateInput"

Now, when you open the report, it will open the form and wait. When
you have entered the criteria and then click the command button, the
form will hide and the report will run. When you close the report, it
will also close the form.

 

[Marshall Barton]

Hi All,
Im new to MS Access VBA programming and reporting but im an
experienced programmer in general. I need to do a MS Access report, and
find that the default pop-ups for parameters are plain and boring. I
would like to use a form to ask the user for input.

I have heard this can be done but cant get it to work.

Here is what I have done.
* Created Report
* Created Form
* Added Event to Report to open form:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateInput", acNormal
End Sub

* Added SQL in report query to use form params:

WHERE ExpiryDate < [Forms]![DateInput]![LabelStart]

This all works to a degree. But my form is full screen, and my report
isnt taking into account the parameters that I type into the form.

That can be a complicated way to filter a report, but the
trick is to open the form in Dialog mode so the report is
suspended until the user is done entering the parameters.

DoCmd.OpenForm "DateInput", acNormal, _
WinowMode:= acDialog

This way, nothing happens in the report until the form is
closed or it is made invisible. You must add your own OK
button to the form and set its Click event to:
Me.Visible = False
Also make certain that users can not close the form, because
it must remain open while the report is running. Use the
report's Close event to close the form.

Most folks prefer a different approach by using a form with
the parameter controls and a button to open the form. This
way, the command button's click event can check to make sure
the parameters are filled in with acceptable values.

This latter approach also allows you to remove the
parameters from query and use code to construct the filter
string. The report can then be filtered by using the
OpenReport method's WhereCondition argument. The advantage
of this method of filtering is that the parameters can be
optional, and, with a little more work, far more versatile.

 

[mark.macumber]

Hey thanks both of you for replying so very fast! I really appreciate
the help, since you both told me practically the same thing I think
thats what i'll do. But just so you know while I was waiting for a
reply, granted it was onyl about half an hour , I did find out about
that where clause in the DoCmd.OpenReport funtion. I am using that and
it works great. But ill have a good play with it now that I know better
about how it works.
Thanks again.