Access 2007 - form with option frame and combo boxes (using Query Def)

A common application created in Access for an end user or an everyday process will require a form, queries, and VBA/ macros, etc.

My task was to create a report which could be printed from a form based on whatever criteria known by the user. The user may want to create reports for all patients for a specific doctor, all doctor's patients for a specific office, or by specific member or patient Id. This can work for any form with combo boxes and/or option frame and option boxes. Here is what I found to work best!

1. Created a report -- This report has a main record source, and 4 additional sub- reports. I found sub-reports are created most easily by dragging and dropping created select queries onto my report in design view.
2.  Created a form - the record source is a query based off basic patient information in this case. Name, DOB, etc. This is the same record source as my report.
3. On the form I dragged an option box. I used the option box wizard to create 3 option boxes. The user will select just 1, based on the type of search they want to make.
4. I added 2 combo boxes, and one text box, and a report preview button. The combo boxes are each filled from a query, the list box is blank, with no default.
5. Then I added the following code to the report preview button_click event. 

Option Compare Database

Option Explicit


Private Sub btnPreview_Click()


'Declare variables
Dim strSQL As String
Dim qdfNew As DAO.QueryDef
Dim db As DAO.Database


Set db = CurrentDb


Select Case Me.FrameOption.Value


Case 1 'Search by doctor
'If combobox Null - msgBox
If IsNull(Me.cmbDoctor) = True Then
MsgBox "Select Doctor for reporting", vbExclamation + vbOKOnly, "Choose Doctor."


Else


'set variable
strSQL = "SELECT Name,"
strSQL = strSQL & " DOB,"
strSQL = strSQL & " NameID,"
strSQL = strSQL & " DrName,"
strSQL = strSQL & " DrPractice"
strSQL = strSQL & " FROM tblInformation"
strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "
strSQL = strSQL & " HAVING (((tblInformation.DrName=[Forms]![frmName]![cmbDoctor])) "


End If


db.QueryDefs.Delete ("qryInformation")


Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)
'open report
DoCmd.OpenReport "rptName", acViewPreview




Case 2 'Search by Practice


'If combobox Null - msgBox
If IsNull(Me.cmbPractice) = True Then
MsgBox "Select Practice for reporting", vbExclamation + vbOKOnly, "Choose Practice"
Else
'set variable

strSQL = "SELECT Name,"

strSQL = strSQL & " DOB,"

strSQL = strSQL & " NameID,"

strSQL = strSQL & " DrName,"

strSQL = strSQL & " DrPractice"

strSQL = strSQL & " FROM tblInformation""
strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "strSQL = strSQL & " HAVING (((tblInformation.DrPractice=[Forms]![frmName]![cmbPractice])) "


db.QueryDefs.Delete ("qryInformation")
Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)
End If


'open report
DoCmd.OpenReport "rptName", acViewPreview


Case 3 'Search by MemberID
'If textbox Null - msgBox
If IsNull(Me.NameId) = True Then
MsgBox "Enter NameID for reporting", vbExclamation + vbOKOnly, "Enter NameID"


Else


'set variable

strSQL = "SELECT Name,"

strSQL = strSQL & " DOB,"

strSQL = strSQL & " NameID,"

strSQL = strSQL & " DrName,"

strSQL = strSQL & " DrPractice"

strSQL = strSQL & " FROM tblInformation"

strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "
strSQL = strSQL & " HAVING (((tblInformation.NameID)=[Forms]![frmName]![txtNameID])) "


End If
db.QueryDefs.Delete ("qryInformation")
Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)


'open report
DoCmd.OpenReport "rptName", acViewPreview

End Select
'removes combobox and textbox values on lose focus

Me.Form.Requery

Me.cmbPractice.DefaultValue = ""

Me.cmbDoctor.DefaultValue = ""

Me.txtNameId.DefaultValue = ""

End Sub

6. I have an option frame with 3 options. I wanted either a combo box or textbox for search criteria available based on the users prefered method to create report. This is the VBA I added to the form Code.

Private Sub OptPractice_GotFocus()
Me.cmbDr.DefaultValue = ""
Me.cmbDr.Visible = False
Me.txtNameId.Visible = False
Me.txtNameId = ""
Me.cmbPractice.Visible = True
Me.lblForm.Caption = "Select Practice Name for Reporting"
End Sub



Private Sub OptDr_GotFocus()
Me.cmbPractice.DefaultValue = ""
Me.cmbPractice.Visible = False
Me.txtNameId.Visible = False
Me.txtNameId = ""
Me.cmbDoctor.Visible = True
Me.lblForm.Caption = "Select Doctor for Reporting"
End Sub




Private Sub OptNameID_GotFocus()
Me.cmbPractice.Visible = False
Me.cmbPractice.DefaultValue = ""
Me.cmbDr.Visible = False
Me.cmbDr.DefaultValue = ""
Me.txtMemberId.Visible = True
Me.lblForm.Caption = "Enter patient name for Reporting"
End Sub

 -- This is a method to create a report based on form criteria using a query def. If you had strictly text boxes -- say where the user was entering report start or end dates -- a simple forms!frmName![TxtbxValue] could be entered into the form recordsource query criteria. Because this form has many options used to create the appropriate report. I chose to use the query def method.. which is amazing!!