HELP: Creating a Criteria Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

ACCESS 2000

I have a report that when opened you are promted to enter a START DATE and
an END DATE, and the output shows those employees whose recertification
expires within that date range.

I have tried to create a singe form to appear instead of having 2 seperate;
however, when I place the command in the criteria line in the QUERY
associated with this report I get the following:

Forms!Entry!Start Date
Forms!Entry!End Date

Where did I go wrong?
 
Use [square brackets] around end date and start date in the query. Make sure
the form is open and the field names are correct. From what you asked I not
sure you understand the form should open the report not the query. That means
having a command button or something similiar on the form which will open the
report.

HTH
Martin J
 
I had the follwoing line in my query:
BETWEEN [forms]![entry]![start date] AND [forms]![entry]![end date]

What do I do from this point?
 
Put a command button on the form that should open the report. Make sure the
report source is the query. Your report now should work off the form instead
of having to put in 2 seperate dates. Try this site for a more comprehensive
explaination and options.

http://www.fontstuff.com/access/index.htm

HTH
Martin J
 
ACCESS 2000

I have a report that when opened you are promted to enter a START DATE and
an END DATE, and the output shows those employees whose recertification
expires within that date range.

I have tried to create a singe form to appear instead of having 2 seperate;
however, when I place the command in the criteria line in the QUERY
associated with this report I get the following:

Forms!Entry!Start Date
Forms!Entry!End Date

Where did I go wrong?
Click to expand...

Create an unbound form. Add 2 Text Controls.
Name one StartDate and the 2nd EndDate.

Add a command button.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In each query, in it's Date field's criteria line, write:
Between forms!ParamForm!StartDate AND forms!ParamForm!EndDate

Next, code the main report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the main report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report. The form will display
and wait for the entry of the dates. Click the command button and the
report will run without need for any further parameter entries. When
the report closes, it will close the form.
 
this didn't help. I get the following message:

COMPILE ERROR:
Only comments may appear after End Sub, End Funcation, or End Property.

The On Error... Line is highlighted when I try to print

Private Sub cmdPrint_Click()
Me.Visible = False
End Sub

On Error GoTo Err_cmdPrint_Click


Dim stDocName As String

stDocName = "report 1"
DoCmd.OpenReport stDocName, acNormal

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
 
Hi Tim

you have an End Sub on line three of your code
delete that and it should work

Cheers
julieD
 
this doesn't work. Now I get a new error message.
"This action can't be carried out while processing a form or report event"
 
Hi Tim

sorry, missed the me.visible = false line - not sure why you're using this,
but i would delete it to.

Cheers
JulieD
 
I asked John Vinson, and that was the advice he gave.
let me take you though step-by-setp, because that didn't work either.

1. created a form with 2 unbound entry fields and a command button to print
the corresponding report.

2. Set the onCLick command to the code I posted.

3. Set the corresponding query to open the blank form.

4. Set the report OnOpen code with
Private Sub Report_Open(cancel as integer)
DoCmd.Openform, "form1",,,,,acDialog
End Sub

5. Set the report OnClose code with
Private Sub Report_Close()
DoCmd.Close acFor, "form1", acSaveNo
end sub
 
this didn't help. I get the following message:

COMPILE ERROR:
Only comments may appear after End Sub, End Funcation, or End Property.

The On Error... Line is highlighted when I try to print

Private Sub cmdPrint_Click()
Me.Visible = False
End Sub

On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "report 1"
DoCmd.OpenReport stDocName, acNormal

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
Click to expand...

You've miss-understood the instructions I gave.
You're supposed to open the ParamForm from the report, not the report
from the ParamForm command button.

The code behind the command button on the form should read exactly
like this:
Private Sub cmdPrint_Click()
Me.Visible = False
End Sub

The code in the REPORT OPEN event should read like this:

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

The Code in Report CLOSE event should read just like this:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

What you do is open the Report (but not from the Paramform!!!).
The report will then open the ParamForm (that's what the
DoCmd.OpenForm does).
After you enter the parameter dates and click the command button on
the form, the form will hide (that's what the Me.Visible = False
does). The Report will then run.
When you close the report, it will close the form (that's what the
DoCmd.Close does).
It does work.
 
Back
Top