I have reports created that pull from queries. I would like to create a form that would allow a certain criteria pulled from aprox. 5-6 fields in the query to access the report many different ways. I was wondering if creating a new form (not based on a certain table or query would be the way to start? If so, who do I get fields such as, beginning date and ending date to work? HELP!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I create a criteria form for reports?
- Thread starter rlh777
- Start date
Will the criteria be required for each field. If so, you'll need to set up paramaters in the criteria field on the query level.
You can do this a couple of ways. If you are creating a form. In the criteria field, you'll have to put something like this:Between [Forms]![nameofform]![name of field] And [Forms]![nameofform]![nameoffield].
I have one for a date range that looks like this in the criteria field.
Between [Forms]![frmDate Range]![Beginning Date] And [Forms]![frmDate Range]![End Date]
Then on the form itself create unbound text boxes. The name of the box must match the name of the parameter in the criteria field.
Another way is to not build a form, rather just to put parameters boxes in the criteria field so when the report is ran a dialog box will pop up to give you an opportunity to input the data(or range) you are looking for. For instance if you have a date field and you are looking for a date range you can just put this in the criteria field. Between [beginning date] and [end date]. When you run the query or report a dialog box will pop up and ask you for those paramaters.
Finally, if the fields are not required to run the query, you may want to consider filters.
You can do this a couple of ways. If you are creating a form. In the criteria field, you'll have to put something like this:Between [Forms]![nameofform]![name of field] And [Forms]![nameofform]![nameoffield].
I have one for a date range that looks like this in the criteria field.
Between [Forms]![frmDate Range]![Beginning Date] And [Forms]![frmDate Range]![End Date]
Then on the form itself create unbound text boxes. The name of the box must match the name of the parameter in the criteria field.
Another way is to not build a form, rather just to put parameters boxes in the criteria field so when the report is ran a dialog box will pop up to give you an opportunity to input the data(or range) you are looking for. For instance if you have a date field and you are looking for a date range you can just put this in the criteria field. Between [beginning date] and [end date]. When you run the query or report a dialog box will pop up and ask you for those paramaters.
Finally, if the fields are not required to run the query, you may want to consider filters.
- Thread starter
- #3
Okay...I need more help. I have a form created with two fields on it. They are called; Zone: and Office Type:. They are both Unbound fields. They are combo boxes so the user can select which zone and what office type. My problem...I don't know how to make the form select on the zone that is chosen and\or the office type that is chosen. HELP!
OK. You'll need to go back to the query and in your field that has Zone types, you'll have to put [forms]![nameofyourform]![zone] and then for your office type, you'll go to the query again an in your field that contains office type, in the criteria field put [forms]![nameofyourform]![office type]
Then go back to the form and put a command button in for run report.
Once users have specified the parameters and click run report, that should do it.
Sean
![[cannon]](/data/assets/smilies/cannon.gif "[cannon] [cannon]")
![[machinegun]](/data/assets/smilies/machinegun.gif "[machinegun] [machinegun]")
![[auto]](/data/assets/smilies/auto.gif "[auto] [auto]")
![[flush3]](/data/assets/smilies/flush3.gif "[flush3] [flush3]")
Then go back to the form and put a command button in for run report.
Once users have specified the parameters and click run report, that should do it.
Sean
I built a report query building form that does what you are talking about. it allows the user to select some, all or none of some all or none of the many field options displayed. I will paste in the code below...apologies for its size....email me and i can send you a zip of the db that also has the working forms. It runs from a report criteria table that builds a given report based on a given query
================Private Sub Form_Open(Cancel As Integer)
Dim dbs As database
Dim rst As Recordset
Dim sql As String, Field As String
Dim i As Integer
Dim Table As String
Me!lblReport.Caption = ReportCaption
Me!lblReport.Tag = "V"
Set dbs = CurrentDb()
sql = "SELECT * FROM [ReportIndex]"
Set rst = dbs.OpenRecordset(sql)
If Not (rst.EOF) Then
If Not (IsNull(rst!Fld1)) And rst!Fld1 <> "" Then
lbl1.Visible = True
lbl1.Caption = rst!Fld1Cap & " ="
Select Case rst!Fld1Typ
Case "Text"
lst1.Visible = True
cmd1.Visible = True
cmd1.Caption = "Clear " & rst!Fld1Cap
lst1.RowSourceType = "Table/Query"
Table = rst!Fld1Src
lst1.RowSource = "SELECT " & rst!Fld1 & " FROM " & Table & " GROUP BY " & rst!Fld1 & " ORDER BY " & rst!Fld1
Case "Range"
lbl1StartDt.Visible = True
lbl1StartDt.Caption = "Start"
txt1StartDt.Visible = True
lbl1EndDt.Visible = True
txt1EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld2)) And rst!Fld2 <> "" Then
lbl2.Visible = True
lbl2.Caption = rst!Fld2Cap & " ="
Select Case rst!Fld2Typ
Case "Text"
lst2.Visible = True
cmd2.Visible = True
cmd2.Caption = "Clear " & rst!Fld2Cap
lst2.RowSourceType = "Table/Query"
Table = rst!Fld2Src
lst2.RowSource = "SELECT " & rst!Fld2 & " FROM " & Table & " GROUP BY " & rst!Fld2 & " ORDER BY " & rst!Fld2
Case "Range"
lbl2StartDt.Visible = True
txt2StartDt.Visible = True
lbl2EndDt.Visible = True
txt2EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld3)) And rst!Fld3 <> "" Then
lbl3.Visible = True
lbl3.Caption = rst!Fld3Cap & " ="
Select Case rst!Fld3Typ
Case "Text"
lst3.Visible = True
cmd3.Visible = True
cmd3.Caption = "Clear " & rst!Fld3Cap
lst3.RowSourceType = "Table/Query"
Table = rst!Fld3Src
lst3.RowSource = "SELECT " & rst!Fld3 & " FROM " & Table & " GROUP BY " & rst!Fld3 & " ORDER BY " & rst!Fld3
Case "Range"
lbl3StartDt.Visible = True
txt3StartDt.Visible = True
lbl3EndDt.Visible = True
txt3EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld4)) And rst!Fld4 <> "" Then
lbl4.Visible = True
lbl4.Caption = rst!Fld4Cap & " ="
Select Case rst!Fld4Typ
Case "Text"
lst4.Visible = True
cmd4.Visible = True
cmd4.Caption = "Clear " & rst!Fld4Cap
lst4.RowSourceType = "Table/Query"
Table = rst!Fld4Src
lst4.RowSource = "SELECT " & rst!Fld4 & " FROM " & Table & " GROUP BY " & rst!Fld4 & " ORDER BY " & rst!Fld4
Case "Range"
lbl4StartDt.Visible = True
txt4StartDt.Visible = True
lbl4EndDt.Visible = True
txt4EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld5)) And rst!Fld5 <> "" Then
lbl5.Visible = True
lbl5.Caption = rst!Fld5Cap & " ="
Select Case rst!Fld5Typ
Case "Text"
lst5.Visible = True
cmd5.Visible = True
cmd5.Caption = "Clear " & rst!Fld5Cap
lst5.RowSourceType = "Table/Query"
Table = rst!Fld5Src
lst5.RowSource = "SELECT " & rst!Fld5 & " FROM " & Table & " GROUP BY " & rst!Fld5 & " ORDER BY " & rst!Fld5
Case "Range"
lbl5StartDt.Visible = True
txt5StartDt.Visible = True
lbl5EndDt.Visible = True
txt5EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld6)) And rst!Fld6 <> "" Then
lbl6.Visible = True
lbl6.Caption = rst!Fld6Cap & " ="
Select Case rst!Fld6Typ
Case "Text"
lst6.Visible = True
cmd6.Visible = True
cmd6.Caption = "Clear " & rst!Fld6Cap
lst6.RowSourceType = "Table/Query"
Table = rst!Fld6Src
'lst6.RowSource = "SELECT " & rst!Fld6 & " FROM " & Table & " GROUP BY " & rst!Fld6 & " ORDER BY " & rst!Fld6
lst6.RowSource = "SELECT [" & rst!Fld6 & "] FROM " & Table & ";"
Case "Range"
lbl6StartDt.Visible = True
txt6StartDt.Visible = True
lbl6EndDt.Visible = True
txt6EndDt.Visible = True
End Select
End If
If Not (IsNull(rst!Fld7)) And rst!Fld7 <> "" Then
lbl7.Visible = True
lbl7.Caption = rst!Fld7Cap & " ="
Select Case rst!Fld7Typ
Case "text"
lst7.Visible = True
Command124.Visible = True
Command124.Caption = "Clear " & rst!Fld7Cap
lst7.RowSourceType = "Table/Query"
Table = rst!Fld7Src
lst7.RowSource = "SELECT " & rst!Fld7 & " FROM " & Table & " order by fld29;"
'lst7.RowSource = "SELECT rst!fld29 from titem order by fld29"
Case "Range"
'lbl7StartDt.Visible = True
'txt7StartDt.Visible = True
'lbl7EndDt.Visible = True
'txt7EndDt.Visible = True
End Select
End If
If Not (IsNull(rst!Fld8)) And rst!Fld8 <> "" Then
lbl8.Visible = True
lbl8.Caption = rst!Fld8Cap & " ="
Select Case rst!Fld8Typ
Case "Text"
lst8.Visible = True
Command133.Visible = True
Command133.Caption = "Clear " & rst!Fld8Cap
lst8.RowSourceType = "Table/Query"
Table = rst!Fld8Src
lst8.RowSource = "SELECT " & rst!Fld8 & " FROM " & Table & " GROUP BY " & rst!Fld8 & " ORDER BY " & rst!Fld8
Case "Yes/No"
lst8.Visible = True
Command133.Visible = True
Command133.Caption = "Clear " & rst!Fld8Cap
lst8.RowSourceType = "Table/Query"
Table = rst!Fld8Src
lst8.RowSource = "SELECT " & rst!Fld8 & " FROM " & Table & " GROUP BY " & rst!Fld8 & " ORDER BY " & rst!Fld8
Case "Range"
'lbl7StartDt.Visible = True
'txt7StartDt.Visible = True
'lbl7EndDt.Visible = True
'txt7EndDt.Visible = True
End Select
End If
End If
End If
End If
End If
End If
Else 'No selection criteria needed, we checked the report index table
If lblReport.Tag = "V" Then
DoCmd.OpenReport Me![lblReport].Caption, acPreview
'DoCmd.Close acForm, "RptSelCriteria"
Else
DoCmd.OpenReport Me![lblReport].Caption, acNormal
SendKeys "%C", False
'DoCmd.Close acForm, "RptSelCriteria"
End If
End If
End Sub
Private Sub cmd1_Click()
Dim inc As Integer
inc = 0
Do While (inc < lst1.ListCount)
If lst1.Selected(inc) = True Then
lst1.Selected(inc) = False
End If
inc = inc + 1
Loop
End Sub
Private Sub Command73_Click()
On Error GoTo Err_Command73_Click
DoCmd.Close
Exit_Command73_Click:
Exit Sub
Err_Command73_Click:
MsgBox Err.Description
Resume Exit_Command73_Click
End Sub
Public Function ReportCaption() As String
If optStatus = 1 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Open Risks and Actions Summary"
Exit Function
End If
If optStatus = 1 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Open Issues and Actions Summary"
Exit Function
End If
If optStatus = 3 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Risks and Actions Summary"
Exit Function
End If
If optStatus = 3 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Issues and Actions Summary"
Exit Function
End If
If optStatus <> 0 And optItem = 3 And optReportType <> 0 Then
ReportCaption = "Closed TSR Log"
Exit Function
End If
If optStatus <> 0 And optItem <> 0 And optReportType = 3 Then
ReportCaption = "Open TSR Log1"
Exit Function
End If
If optStatus = 1 And optItem = 1 And optReportType = 2 Then
ReportCaption = "TSR Detail Report"
Exit Function
End If
If optStatus = 1 And optItem = 2 And optReportType = 2 Then
ReportCaption = "TSR Detail Report"
Exit Function
End If
If optStatus = 2 And optItem = 2 And optReportType = 2 Then
ReportCaption = "Closed TSR Detail Report"
Exit Function
End If
If optStatus = 2 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Closed Risks Summary"
Exit Function
End If
If optStatus = 2 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Closed Issues Summary"
Exit Function
End If
MsgBox "Choose another configuration"
End Function
================Private Sub Form_Open(Cancel As Integer)
Dim dbs As database
Dim rst As Recordset
Dim sql As String, Field As String
Dim i As Integer
Dim Table As String
Me!lblReport.Caption = ReportCaption
Me!lblReport.Tag = "V"
Set dbs = CurrentDb()
sql = "SELECT * FROM [ReportIndex]"
Set rst = dbs.OpenRecordset(sql)
If Not (rst.EOF) Then
If Not (IsNull(rst!Fld1)) And rst!Fld1 <> "" Then
lbl1.Visible = True
lbl1.Caption = rst!Fld1Cap & " ="
Select Case rst!Fld1Typ
Case "Text"
lst1.Visible = True
cmd1.Visible = True
cmd1.Caption = "Clear " & rst!Fld1Cap
lst1.RowSourceType = "Table/Query"
Table = rst!Fld1Src
lst1.RowSource = "SELECT " & rst!Fld1 & " FROM " & Table & " GROUP BY " & rst!Fld1 & " ORDER BY " & rst!Fld1
Case "Range"
lbl1StartDt.Visible = True
lbl1StartDt.Caption = "Start"
txt1StartDt.Visible = True
lbl1EndDt.Visible = True
txt1EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld2)) And rst!Fld2 <> "" Then
lbl2.Visible = True
lbl2.Caption = rst!Fld2Cap & " ="
Select Case rst!Fld2Typ
Case "Text"
lst2.Visible = True
cmd2.Visible = True
cmd2.Caption = "Clear " & rst!Fld2Cap
lst2.RowSourceType = "Table/Query"
Table = rst!Fld2Src
lst2.RowSource = "SELECT " & rst!Fld2 & " FROM " & Table & " GROUP BY " & rst!Fld2 & " ORDER BY " & rst!Fld2
Case "Range"
lbl2StartDt.Visible = True
txt2StartDt.Visible = True
lbl2EndDt.Visible = True
txt2EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld3)) And rst!Fld3 <> "" Then
lbl3.Visible = True
lbl3.Caption = rst!Fld3Cap & " ="
Select Case rst!Fld3Typ
Case "Text"
lst3.Visible = True
cmd3.Visible = True
cmd3.Caption = "Clear " & rst!Fld3Cap
lst3.RowSourceType = "Table/Query"
Table = rst!Fld3Src
lst3.RowSource = "SELECT " & rst!Fld3 & " FROM " & Table & " GROUP BY " & rst!Fld3 & " ORDER BY " & rst!Fld3
Case "Range"
lbl3StartDt.Visible = True
txt3StartDt.Visible = True
lbl3EndDt.Visible = True
txt3EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld4)) And rst!Fld4 <> "" Then
lbl4.Visible = True
lbl4.Caption = rst!Fld4Cap & " ="
Select Case rst!Fld4Typ
Case "Text"
lst4.Visible = True
cmd4.Visible = True
cmd4.Caption = "Clear " & rst!Fld4Cap
lst4.RowSourceType = "Table/Query"
Table = rst!Fld4Src
lst4.RowSource = "SELECT " & rst!Fld4 & " FROM " & Table & " GROUP BY " & rst!Fld4 & " ORDER BY " & rst!Fld4
Case "Range"
lbl4StartDt.Visible = True
txt4StartDt.Visible = True
lbl4EndDt.Visible = True
txt4EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld5)) And rst!Fld5 <> "" Then
lbl5.Visible = True
lbl5.Caption = rst!Fld5Cap & " ="
Select Case rst!Fld5Typ
Case "Text"
lst5.Visible = True
cmd5.Visible = True
cmd5.Caption = "Clear " & rst!Fld5Cap
lst5.RowSourceType = "Table/Query"
Table = rst!Fld5Src
lst5.RowSource = "SELECT " & rst!Fld5 & " FROM " & Table & " GROUP BY " & rst!Fld5 & " ORDER BY " & rst!Fld5
Case "Range"
lbl5StartDt.Visible = True
txt5StartDt.Visible = True
lbl5EndDt.Visible = True
txt5EndDt.Visible = True
End Select
If Not (IsNull(rst!Fld6)) And rst!Fld6 <> "" Then
lbl6.Visible = True
lbl6.Caption = rst!Fld6Cap & " ="
Select Case rst!Fld6Typ
Case "Text"
lst6.Visible = True
cmd6.Visible = True
cmd6.Caption = "Clear " & rst!Fld6Cap
lst6.RowSourceType = "Table/Query"
Table = rst!Fld6Src
'lst6.RowSource = "SELECT " & rst!Fld6 & " FROM " & Table & " GROUP BY " & rst!Fld6 & " ORDER BY " & rst!Fld6
lst6.RowSource = "SELECT [" & rst!Fld6 & "] FROM " & Table & ";"
Case "Range"
lbl6StartDt.Visible = True
txt6StartDt.Visible = True
lbl6EndDt.Visible = True
txt6EndDt.Visible = True
End Select
End If
If Not (IsNull(rst!Fld7)) And rst!Fld7 <> "" Then
lbl7.Visible = True
lbl7.Caption = rst!Fld7Cap & " ="
Select Case rst!Fld7Typ
Case "text"
lst7.Visible = True
Command124.Visible = True
Command124.Caption = "Clear " & rst!Fld7Cap
lst7.RowSourceType = "Table/Query"
Table = rst!Fld7Src
lst7.RowSource = "SELECT " & rst!Fld7 & " FROM " & Table & " order by fld29;"
'lst7.RowSource = "SELECT rst!fld29 from titem order by fld29"
Case "Range"
'lbl7StartDt.Visible = True
'txt7StartDt.Visible = True
'lbl7EndDt.Visible = True
'txt7EndDt.Visible = True
End Select
End If
If Not (IsNull(rst!Fld8)) And rst!Fld8 <> "" Then
lbl8.Visible = True
lbl8.Caption = rst!Fld8Cap & " ="
Select Case rst!Fld8Typ
Case "Text"
lst8.Visible = True
Command133.Visible = True
Command133.Caption = "Clear " & rst!Fld8Cap
lst8.RowSourceType = "Table/Query"
Table = rst!Fld8Src
lst8.RowSource = "SELECT " & rst!Fld8 & " FROM " & Table & " GROUP BY " & rst!Fld8 & " ORDER BY " & rst!Fld8
Case "Yes/No"
lst8.Visible = True
Command133.Visible = True
Command133.Caption = "Clear " & rst!Fld8Cap
lst8.RowSourceType = "Table/Query"
Table = rst!Fld8Src
lst8.RowSource = "SELECT " & rst!Fld8 & " FROM " & Table & " GROUP BY " & rst!Fld8 & " ORDER BY " & rst!Fld8
Case "Range"
'lbl7StartDt.Visible = True
'txt7StartDt.Visible = True
'lbl7EndDt.Visible = True
'txt7EndDt.Visible = True
End Select
End If
End If
End If
End If
End If
End If
Else 'No selection criteria needed, we checked the report index table
If lblReport.Tag = "V" Then
DoCmd.OpenReport Me![lblReport].Caption, acPreview
'DoCmd.Close acForm, "RptSelCriteria"
Else
DoCmd.OpenReport Me![lblReport].Caption, acNormal
SendKeys "%C", False
'DoCmd.Close acForm, "RptSelCriteria"
End If
End If
End Sub
Private Sub cmd1_Click()
Dim inc As Integer
inc = 0
Do While (inc < lst1.ListCount)
If lst1.Selected(inc) = True Then
lst1.Selected(inc) = False
End If
inc = inc + 1
Loop
End Sub
Private Sub Command73_Click()
On Error GoTo Err_Command73_Click
DoCmd.Close
Exit_Command73_Click:
Exit Sub
Err_Command73_Click:
MsgBox Err.Description
Resume Exit_Command73_Click
End Sub
Public Function ReportCaption() As String
If optStatus = 1 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Open Risks and Actions Summary"
Exit Function
End If
If optStatus = 1 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Open Issues and Actions Summary"
Exit Function
End If
If optStatus = 3 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Risks and Actions Summary"
Exit Function
End If
If optStatus = 3 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Issues and Actions Summary"
Exit Function
End If
If optStatus <> 0 And optItem = 3 And optReportType <> 0 Then
ReportCaption = "Closed TSR Log"
Exit Function
End If
If optStatus <> 0 And optItem <> 0 And optReportType = 3 Then
ReportCaption = "Open TSR Log1"
Exit Function
End If
If optStatus = 1 And optItem = 1 And optReportType = 2 Then
ReportCaption = "TSR Detail Report"
Exit Function
End If
If optStatus = 1 And optItem = 2 And optReportType = 2 Then
ReportCaption = "TSR Detail Report"
Exit Function
End If
If optStatus = 2 And optItem = 2 And optReportType = 2 Then
ReportCaption = "Closed TSR Detail Report"
Exit Function
End If
If optStatus = 2 And optItem = 1 And optReportType = 1 Then
ReportCaption = "Closed Risks Summary"
Exit Function
End If
If optStatus = 2 And optItem = 2 And optReportType = 1 Then
ReportCaption = "Closed Issues Summary"
Exit Function
End If
MsgBox "Choose another configuration"
End Function
- Status
Similar threads
- Locked
- Question
- Locked
- Helpful tip
- Locked
- Question
- Locked
- Question