Create report from form through vba

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
 
T

Tom Wickerath

How about having a defined report, instead of trying to create the report
through VBA? One reason for this is that you would never be able to
distribute your application in the compiled .mde format, if you needed to
create reports on-the-fly. You can use VBA code to display or hide various
sections of a report, such as the Detail section, in order to show more
detail. Have a look at Access MVP Armen Stein's "Report Selection Techniques"
example, which you can download from this site:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Ben said:
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!
<snip>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top