Param Form Code ???

[knowshowrosegrows]

So I have a simple report that is fronted by a simple parameter form. On the
form they choose an agency from a drop down OR a region and then put in a
date range. I do not want them to be able to run the report by both a region
and an agency.

If they choose to run the report according to a region, I want them to be
able to choose region 1,2,3,4,5 OR ALL the regions.

I have a qry that will run the report by agency OR region. The SQL is below:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.EventDate
FROM qryEventsInfo
WHERE
(((qryEventsInfo.Agency)=forms!frmSiteVisitByDateVendorParam!ChooseVendor))
Or (((qryEventsInfo.Reg)=forms!frmSiteVisitByDateVendorParam!ChooseRegion));

What I don't know how to code in is the restriction that they can't put in
both an agency and a region parameter (I need a message box to pop up). I
also don't know how to code that if they choose ALL from the region drop
down, they can run a report for all the regions.

As always - any help is a gift.

 

[Brian]

Put code like this on the button that runs the report:

Private Sub MyButton_Click()
If not isnull(Region) and not isnull(agency) then
Msgbox "You cannot select both a region and an agency."
agency.setfocus
end if
End Sub

On the region, you could just let them leave it blank for all or select any
single one. Then, modify your query to assume that if they did not select a
region or agency, they want all regions.

Your SQL will end up like this:

SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.EventDate
FROM qryEventsInfo
WHERE
(forms!frmSiteVisitByDateVendorParam!ChooseVendor Is Not Null AND
qryEventsInfo.Agency)=forms!frmSiteVisitByDateVendorParam!ChooseVendor)
OR (forms!frmSiteVisitByDateVendorParam!ChooseRegion Is Not Null AND
qryEventsInfo.Reg)=forms!frmSiteVisitByDateVendorParam!ChooseRegion) OR
(forms!frmSiteVisitByDateVendorParam!ChooseVendor Is Null AND
forms!frmSiteVisitByDateVendorParam!ChooseRegion Is Null);

Hopefully, I typed all that correctly! I think perhaps you want this:

1. If there is an Agency, limit query to that Agency.
2. If there a Region, limit query to that Region.
3. If there is neither, show all records.

 

[knowshowrosegrows]

Thanks for your quick reply. Your code looks close. The only trick is that
I want them to be able to choose a dropdown option called ALL under Region
and I want the query to read that as if no region was chosen (so all regions
will be reported).