B
Ben
Hello,
I'm trying to make a report generate from input in a form.
The form defines the SQL statement for the query which would generate
the report.
The form has 2 areas:
1) the user selects the search criteria (WHERE of the query the report
reads from)
2) the user selects the fields to be displayed from a few checkboxes
(SELECT of the same query)
The query works great.
Is there a way to make a new tabbed report automatically from vba that
uses all the fields in the query?
Or is there a better, more logical, or more Access appropriate
approach?
Thanks!
Here's an example of my form code:
Private Sub Search_Click()
On Error GoTo Err_Search_Click
Dim strFilter As String
Dim db As DAO.Database
Dim qdfExisting As DAO.QueryDef
Dim SQL_Text As String
Dim CriteriaCount As Integer
Dim FieldCount As Integer
CriteriaCount = 0
FieldCount = 0
SQL_Text = "SELECT "
'=================================================================================
' Choose which fields to include in the query
' Seat table fields
ProgramList.SetFocus
If chkProgram = True Or ProgramList.Text = "" Then
strFilter = "[tblSeats].[ProgramID]"
If FieldCount > 0 Then
SQL_Text = SQL_Text & ", " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
FieldCount = FieldCount + 1
End If
' the IF statments repeat for all the form's checkboxes relating to
available fields from the 1st table
' Test table fields
If chkRegulation = True Then
strFilter = "[tblTests].[RegID_tst]"
If FieldCount > 0 Then
SQL_Text = SQL_Text & ", " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
FieldCount = FieldCount + 1
End If
' the IF statments repeat for all the form's checkboxes relating to
available fields from the 2nd table
SQL_Text = SQL_Text & " FROM tblSeats INNER JOIN tblTests ON
tblSeats.Variation = tblTests.Variaton_tst WHERE "
'=================================================================================
' Choose conditions for the WHERE clause
ProgramList.SetFocus
If ProgramList.Text <> "" Then
strFilter = "[tblSeats].[ProgramID] = '" + ProgramList.Text + "'"
If CriteriaCount > 0 Then
SQL_Text = SQL_Text & " AND " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
CriteriaCount = CriteriaCount + 1
End If
' the IF statement repeats for each criteria chosen in the 1st part of
the form
Set qdfExisting = CurrentDb().QueryDefs("qryAllTests")
qdfExisting.SQL = SQL_Text
'DoCmd.OpenReport "rptTests2", acViewPreview
'removed the above line because it doesn't allow changing fields
Exit_Search_Click:
Exit Sub
Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
I'm trying to make a report generate from input in a form.
The form defines the SQL statement for the query which would generate
the report.
The form has 2 areas:
1) the user selects the search criteria (WHERE of the query the report
reads from)
2) the user selects the fields to be displayed from a few checkboxes
(SELECT of the same query)
The query works great.
Is there a way to make a new tabbed report automatically from vba that
uses all the fields in the query?
Or is there a better, more logical, or more Access appropriate
approach?
Thanks!
Here's an example of my form code:
Private Sub Search_Click()
On Error GoTo Err_Search_Click
Dim strFilter As String
Dim db As DAO.Database
Dim qdfExisting As DAO.QueryDef
Dim SQL_Text As String
Dim CriteriaCount As Integer
Dim FieldCount As Integer
CriteriaCount = 0
FieldCount = 0
SQL_Text = "SELECT "
'=================================================================================
' Choose which fields to include in the query
' Seat table fields
ProgramList.SetFocus
If chkProgram = True Or ProgramList.Text = "" Then
strFilter = "[tblSeats].[ProgramID]"
If FieldCount > 0 Then
SQL_Text = SQL_Text & ", " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
FieldCount = FieldCount + 1
End If
' the IF statments repeat for all the form's checkboxes relating to
available fields from the 1st table
' Test table fields
If chkRegulation = True Then
strFilter = "[tblTests].[RegID_tst]"
If FieldCount > 0 Then
SQL_Text = SQL_Text & ", " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
FieldCount = FieldCount + 1
End If
' the IF statments repeat for all the form's checkboxes relating to
available fields from the 2nd table
SQL_Text = SQL_Text & " FROM tblSeats INNER JOIN tblTests ON
tblSeats.Variation = tblTests.Variaton_tst WHERE "
'=================================================================================
' Choose conditions for the WHERE clause
ProgramList.SetFocus
If ProgramList.Text <> "" Then
strFilter = "[tblSeats].[ProgramID] = '" + ProgramList.Text + "'"
If CriteriaCount > 0 Then
SQL_Text = SQL_Text & " AND " & strFilter
Else
SQL_Text = SQL_Text & strFilter
End If
CriteriaCount = CriteriaCount + 1
End If
' the IF statement repeats for each criteria chosen in the 1st part of
the form
Set qdfExisting = CurrentDb().QueryDefs("qryAllTests")
qdfExisting.SQL = SQL_Text
'DoCmd.OpenReport "rptTests2", acViewPreview
'removed the above line because it doesn't allow changing fields
Exit_Search_Click:
Exit Sub
Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub