Here's what I am trying to do.
Currently, I have 1 form. On the top half of the form, there is a listbox (lstReports) that displays a list of reports that a user can run. (The Rowsource for the listbox is: SELECT DISTINCTROW tblReports.ReportDescription, tblReports.ReportName FROM tblReports ORDER BY tblReports.ReportDescription; )
On the bottom half of the form is a series of comboboxes (cboStartDept, cboEndDept, cboStartJobTitle, cboEndJobTitle, cboStartName, cboEndName) that are displayed or not displayed depending on the report the user selects in the listbox above. These comboboxes are the parameters for the reports. (There are actually more parameters than I mentioned - just cut down for simplicity.)
At the end of the form are several command buttons: Print, Preview, Close.
The form works great as is, with the code below. However, I want to move all of the combobox parameters to a subform. I am running out of room on the existing form, and need to be able to include more parameters as new reports are added to the database. Moving the comboboxes to the subform will allow an almost infinite amount of parameters available.
So, my problem is trying to figure out which of the code below goes with the MainForm (frmReports) and which code goes with the SubForm (frmReportsSUB). I need to figure out how to get the MainForm and SubForm to communicate with each other depending on the report chosen in the MainForm. And, I need to get the command buttons to work correctly, depending on the MainForm report and the SubForm parameters.
In addition, all of this code was inherited. I don't really understand VB very well, and don't really know how to go about getting all of this to sync up.
Any help would be greatly appreciated!!
Sarah
------
EXISTING CODE for form:
Option Compare Database
Option Explicit
Private blnSelectDept As Boolean, _
blnSelectJobTitle As Boolean, _
blnSelectName As Boolean
---------
Private Function ChecklstReports() As Boolean
'Check to ensure a report is selected
On Error Resume Next
If IsNull(Me.lstReports) Then
MsgBox "You must select a report to continue."
Me.lstReports.SetFocus
ChecklstReports = False
Else
ChecklstReports = True
End If
End Function
--------
Public Sub SetReportVariables(strReportName As String)
'This procedure sets the form display variables for the
'report name provided
On Error GoTo ErrHandler
'Declare procedure variables
Dim djetDB As Database, rsnpReports As DAO.Recordset, _
strCriteria As String
'Set procedure variables
Set djetDB = CurrentDb
Set rsnpReports = djetDB.OpenRecordset("tblReports", dbOpenSnapshot)
strCriteria = "ReportName = '" & strReportName & "'"
'Read the recordset
rsnpReports.MoveLast
'Find the specified report and set display variables for that report
With rsnpReports
.FindFirst strCriteria
blnSelectDept = !SelectDepts
blnSelectJobTitle = !SelectJobs
blnSelectName = !SelectNames
'close recordset
.Close
End With
ExitSub:
Exit Sub
ErrHandler:
MsgBox Err & " " & Err.Description
Resume ExitSub
End Sub
-------
Private Sub lstReports_AfterUpdate()
'Sets the visible properties of the parameter fields
'once a report is selected by user.
On Error GoTo ErrHandler
SetReportVariables Me.lstReports
If blnSelectDept = True Then
Me.cboStartDept.Visible = True
Me.cboEndDept.Visible = True
Else
Me.cboStartDept.Visible = False
Me.cboEndDept.Visible = False
End If
If blnSelectJobTitle = True Then
Me.cboStartJobTitle.Visible = True
Me.cboEndJobTitle.Visible = True
Else
Me.cboStartJobTitle.Visible = False
Me.cboEndJobTitle.Visible = False
End If
If blnSelectName = True Then
Me.cboStartName.Visible = True
Me.cboEndName.Visible = True
Else
Me.cboStartName.Visible = False
Me.cboEndName.Visible = False
End If
ExitSub:
Exit Sub
ErrHandler:
MsgBox Err & " " & Err.Description
Resume ExitSub
End Sub
Currently, I have 1 form. On the top half of the form, there is a listbox (lstReports) that displays a list of reports that a user can run. (The Rowsource for the listbox is: SELECT DISTINCTROW tblReports.ReportDescription, tblReports.ReportName FROM tblReports ORDER BY tblReports.ReportDescription; )
On the bottom half of the form is a series of comboboxes (cboStartDept, cboEndDept, cboStartJobTitle, cboEndJobTitle, cboStartName, cboEndName) that are displayed or not displayed depending on the report the user selects in the listbox above. These comboboxes are the parameters for the reports. (There are actually more parameters than I mentioned - just cut down for simplicity.)
At the end of the form are several command buttons: Print, Preview, Close.
The form works great as is, with the code below. However, I want to move all of the combobox parameters to a subform. I am running out of room on the existing form, and need to be able to include more parameters as new reports are added to the database. Moving the comboboxes to the subform will allow an almost infinite amount of parameters available.
So, my problem is trying to figure out which of the code below goes with the MainForm (frmReports) and which code goes with the SubForm (frmReportsSUB). I need to figure out how to get the MainForm and SubForm to communicate with each other depending on the report chosen in the MainForm. And, I need to get the command buttons to work correctly, depending on the MainForm report and the SubForm parameters.
In addition, all of this code was inherited. I don't really understand VB very well, and don't really know how to go about getting all of this to sync up.
Any help would be greatly appreciated!!
Sarah
------
EXISTING CODE for form:
Option Compare Database
Option Explicit
Private blnSelectDept As Boolean, _
blnSelectJobTitle As Boolean, _
blnSelectName As Boolean
---------
Private Function ChecklstReports() As Boolean
'Check to ensure a report is selected
On Error Resume Next
If IsNull(Me.lstReports) Then
MsgBox "You must select a report to continue."
Me.lstReports.SetFocus
ChecklstReports = False
Else
ChecklstReports = True
End If
End Function
--------
Public Sub SetReportVariables(strReportName As String)
'This procedure sets the form display variables for the
'report name provided
On Error GoTo ErrHandler
'Declare procedure variables
Dim djetDB As Database, rsnpReports As DAO.Recordset, _
strCriteria As String
'Set procedure variables
Set djetDB = CurrentDb
Set rsnpReports = djetDB.OpenRecordset("tblReports", dbOpenSnapshot)
strCriteria = "ReportName = '" & strReportName & "'"
'Read the recordset
rsnpReports.MoveLast
'Find the specified report and set display variables for that report
With rsnpReports
.FindFirst strCriteria
blnSelectDept = !SelectDepts
blnSelectJobTitle = !SelectJobs
blnSelectName = !SelectNames
'close recordset
.Close
End With
ExitSub:
Exit Sub
ErrHandler:
MsgBox Err & " " & Err.Description
Resume ExitSub
End Sub
-------
Private Sub lstReports_AfterUpdate()
'Sets the visible properties of the parameter fields
'once a report is selected by user.
On Error GoTo ErrHandler
SetReportVariables Me.lstReports
If blnSelectDept = True Then
Me.cboStartDept.Visible = True
Me.cboEndDept.Visible = True
Else
Me.cboStartDept.Visible = False
Me.cboEndDept.Visible = False
End If
If blnSelectJobTitle = True Then
Me.cboStartJobTitle.Visible = True
Me.cboEndJobTitle.Visible = True
Else
Me.cboStartJobTitle.Visible = False
Me.cboEndJobTitle.Visible = False
End If
If blnSelectName = True Then
Me.cboStartName.Visible = True
Me.cboEndName.Visible = True
Else
Me.cboStartName.Visible = False
Me.cboEndName.Visible = False
End If
ExitSub:
Exit Sub
ErrHandler:
MsgBox Err & " " & Err.Description
Resume ExitSub
End Sub
