By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Select from two combo boxes on form to run report

P: 15
Hi All,

I have been reading some of the threads in your forum and decided to join and post my question. Hopefully it won't be too lengthy.

I have created an Unbound Form called paramform2. This form contains two combo boxes, a text box for the start date, a text box for the end date and a command button that runs the report.

The first combo box is named FindMgr. The row source of this box is set to: SELECT"All" As MgrName FROM tblMain UNION SELECT MgrName FROM tblMain ORDER BY MgrName. Column Count and Bound Column are both set to 1. The After Update event is:

Me!FindMgr.Requery
Me!FindMgr.SetFocus

The second combo box is named: FindDBS. The row source of this box is set to: SELECT DISTINCT strDBS FROM tblMain WHERE MGRName=Forms!ParamForm2.FindMgr ORDER BY strDBS; Column Count and Bound column are both set to 1.

The criteria in the query for both boxes are: [Forms]![ParamForm2].[FindMgr] and [Forms]![ParamForm2].[FindDBS]

When I select a one manager name and one employee name and run the report, I get a report for all of the employees listed under that Manager.

There are two things I need to be able to do:

1. Select one manager name and one employee name and have the report run just for that manager and that one employee.

2. Select a manager name and have a report run for all of the employees under that manager.

Is there a way to do this? Any help would be greatly appreciated.

Thank you.
Jul 17 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
What's the SQL for your query look like?
Is DBS = employee name? It looks like your After Update event should requery the FindDBS box rather than the FindMgr box.
Jul 20 '09 #2

P: 15
Hi Chip,

Thank you for your reply.

The SQL view of my query is:

SELECT qryMaster.MgrName, qryMaster.strSSN, qryMaster.strClaimantName, qryMaster.strProduct, qryMaster.strTypeofClaim, qryMaster.ysnApproved, qryMaster.ysnNonComp, qryMaster.[curr$$Paid], qryMaster.dtmDecisionDate, tblDBS.DBSName
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDBS = tblDBS.DBSName) INNER JOIN tblManagers ON (qryMaster.MgrName = tblManagers.MgrName) AND (tblDBS.cmbManager = tblManagers.MgrName)
WHERE (((qryMaster.MgrName)=[Forms]![ParamForm2].[FindMgr]) AND ((tblDBS.DBSName)=[Forms]![ParamForm2].[FindDBS]) AND (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm2]![StartDate] And [Forms]![ParamForm2]![EndDate])) OR (((qryMaster.MgrName)=[Forms]![ParamForm2].[FindMgr]) AND (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm2]![StartDate] And [Forms]![ParamForm2]![EndDate])) OR ((([Forms]![ParamForm2].[FindMgr])="All"));

Yes,DBS = employee name. I will change the After Update event.

Please let me know if there is anything else I can supply that might help us find a solution for what I need to accomplish.
Jul 20 '09 #3

Expert 100+
P: 1,287
I think we can see, with the query spaced out, that even when a FindDBS is specified, the OR from line 16 to 26 is going to match records that don't match the FindDBS. We'll need to rework the logic for the query. I hope to have time to revisit this later today. Possibly add in something around line 25 to make sure that no DBS is specified with an AND.
Expand|Select|Wrap|Line Numbers
  1. WHERE
  2. (
  3.   (
  4.     (qryMaster.MgrName)=[Forms]![ParamForm2].[FindMgr]
  5.   )
  6.   AND
  7.   ( 
  8.     (tblDBS.DBSName)=[Forms]![ParamForm2].[FindDBS]
  9.   )
  10.   AND
  11.   (
  12.     ([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm2]![StartDate] 
  13.     And [Forms]![ParamForm2]![EndDate]
  14.   )
  15. )
  16. OR
  17. (
  18.   (
  19.     (qryMaster.MgrName)=[Forms]![ParamForm2].[FindMgr]
  20.   )
  21.   AND
  22.   (
  23.     ([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm2]![StartDate]
  24.     And [Forms]![ParamForm2]![EndDate]
  25.   )
  26. )
  27. OR 
  28. (
  29.   (
  30.     ([Forms]![ParamForm2].[FindMgr])="All"
  31.   )
  32. )
Jul 20 '09 #4

P: 15
Hi Chip,

I changed the SQL a little bit and was able to run a report based on 1 Manager Name and 1 Employee Name. Here is the updated SQL.

SELECT qryMaster.MgrName, qryMaster.strSSN, qryMaster.strClaimantName, qryMaster.strProduct, qryMaster.strTypeofClaim, qryMaster.ysnApproved, qryMaster.ysnNonComp, qryMaster.[curr$$Paid], qryMaster.dtmDecisionDate, tblDBS.DBSName, tblMain.dtmDecisionDate AS Expr1, Forms!ParamForm3.FindMgr AS Expr2
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDBS = tblDBS.DBSName) INNER JOIN tblManagers ON (tblDBS.cmbManager = tblManagers.MgrName) AND (qryMaster.MgrName = tblManagers.MgrName)
WHERE (((qryMaster.MgrName)=[Forms]![ParamForm3].[FindMgr]) AND ((tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS]) AND (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm3]![StartDate] And [Forms]![ParamForm3]![EndDate])) OR ((([Forms]![ParamForm3].[FindMgr])="All"));

Still working on being able to select a Manager Name and have a report run for all employees under that Manager as well.

Thank you,
Jul 22 '09 #5

Expert 100+
P: 1,287
How do you want the user to choose all employees under one manager? Do you want to use "all" like in the FindMgr, or just leave the FindDBS combo blank? Something like:
Expand|Select|Wrap|Line Numbers
  1. WHERE 
  2.   ([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm3]![StartDate] 
  3.   And [Forms]![ParamForm3]![EndDate] 
  4. )
  5. AND
  6. (
  7.   ([Forms]![ParamForm3].[FindMgr])="All" 
  8.   OR
  9.   (
  10.     (qryMaster.MgrName)=[Forms]![ParamForm3].[FindMgr] 
  11.     AND  
  12.     (
  13.       [Forms]![ParamForm3].[FindDBS]="All"   'or ""
  14.       OR
  15.       (tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS]
  16.     )
  17.   )
  18. )
Jul 22 '09 #6

P: 15
Hi Chip,

I think the best way would be to leave the FindDBS field blank.

Thank you.
Jul 22 '09 #7

Expert 100+
P: 1,287
In that case, the code in the previous post should work, with line 14 ending with [FindDBS]=""
Jul 22 '09 #8

P: 15
Hi Chip,

I so very much appreciate all of your help. I am getting closer to finishing this database.

I typed the code you gave me. But when I tried running a report by selecting the Managers name and leaving the DBS name blank it did not return any records. This is what I have for my SQL now.

SELECT qryMaster.MgrName, qryMaster.strSSN, qryMaster.strClaimantName, qryMaster.strProduct, qryMaster.strTypeofClaim, qryMaster.ysnApproved, qryMaster.ysnNonComp, qryMaster.[curr$$Paid], qryMaster.dtmDecisionDate, tblDBS.DBSName, tblMain.dtmDecisionDate AS Expr1, Forms!ParamForm3.FindMgr AS Expr2
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDBS=tblDBS.DBSName) INNER JOIN tblManagers ON (tblDBS.cmbManager=tblManagers.MgrName) AND (qryMaster.MgrName=tblManagers.MgrName)
WHERE (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm3]![StartDate] AND [Forms]![ParamForm3]![EndDate]) AND (([Forms]![ParamForm3].[FindMgr])="All" OR ((qryMaster.MgrName)=[Forms]![ParamForm3].[FindMgr] AND ([Forms]![ParamForm3].[FindDBS]="" OR (tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS])));

Thank you,
Jul 22 '09 #9

Expert 100+
P: 1,287
Sorry, it seems a blank box is not the same as "". It's considered Null, so (hopefully) we can check for that. Try this out:
Expand|Select|Wrap|Line Numbers
  1. WHERE
  2. (
  3.   ([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm3]![StartDate] 
  4.   AND [Forms]![ParamForm3]![EndDate]
  5. )
  6. AND
  7. (
  8.   ([Forms]![ParamForm3].[FindMgr])="All" 
  9.   OR
  10.   (
  11.     (qryMaster.MgrName)=[Forms]![ParamForm3].[FindMgr] 
  12.     AND 
  13.     (
  14.       [Forms]![ParamForm3].[FindDBS] IS NULL
  15.       OR
  16.       (tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS]
  17.     )
  18.   )
  19. );
Glad you're getting closer.
Jul 22 '09 #10

P: 15
Chip,

You are brilliant. That worked perfectly. Thank you so very much !

BeaBea
Jul 23 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.