I have a table of events with a field for Region and a field for Agency.
Each event has region 1, 2, 3, 4 or 5 and an agency from an agency table with
hundreds of agencies. When they run a report, I want them to be able to run
it by a specific agency OR by region. If they run it by region, I want them
to be able to run a specific region OR ALL REGIONs.
I have a param form with controls for date range, agency and region. The
code below is in the click event for the "Run Report" button. It is only
kind of close to what I want.
Private Sub Command14_Click()
Dim Region As String
Dim Agency As String
If Not IsNull(Agency) And Not IsNull(Region) Then
MsgBox "You must choose EITHER a Region option or a Provider option."
'Me![Region].SetFocus
Region = Empty And Agency = Empty
Else
Me.Visible = False
End If
End Sub
Below is the SQL for the query.
SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.Type,
qryEventsInfo.EventDate, qryEventsInfo.StartTime, qryEventsInfo.StaffName,
qryEventsInfo.[Event Description], qryEventsInfo.[Report Submitted],
qryEventsInfo.Notes
FROM qryEventsInfo
WHERE
(((qryEventsInfo.Agency)=forms!frmSiteVisitByDateVendorParam!ChooseVendor))
Or (((qryEventsInfo.Reg)=forms!frmSiteVisitByDateVendorParam!ChooseRegion));
How do I code that choosing "All Regions" in the region drop down actually
chooses no region? How do have the word "None" show up at the top of the
drop down for the agency control? How do I code that the controls agency and
region go empty after the the message box appears? How do I get the focus
back in the now empty region control?
--
Thanks
You all are teaching me so much
|
|