Select from two combo boxes on form to run report

[BeaBea]

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.Requ ery
Me!FindMgr.SetF ocus

The second combo box is named: FindDBS. The row source of this box is set to: SELECT DISTINCT strDBS FROM tblMain WHERE MGRName=Forms!P aramForm2.FindM gr 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.

[ChipR]

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.

[BeaBea]

Hi Chip,

Thank you for your reply.

The SQL view of my query is:

SELECT qryMaster.MgrNa me, qryMaster.strSS N, qryMaster.strCl aimantName, qryMaster.strPr oduct, qryMaster.strTy peofClaim, qryMaster.ysnAp proved, qryMaster.ysnNo nComp, qryMaster.[curr$$Paid], qryMaster.dtmDe cisionDate, tblDBS.DBSName
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDB S = tblDBS.DBSName) INNER JOIN tblManagers ON (qryMaster.MgrN ame = tblManagers.Mgr Name) AND (tblDBS.cmbMana ger = tblManagers.Mgr Name)
WHERE (((qryMaster.Mg rName)=[Forms]![ParamForm2].[FindMgr]) AND ((tblDBS.DBSNam e)=[Forms]![ParamForm2].[FindDBS]) AND (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm2]![StartDate] And [Forms]![ParamForm2]![EndDate])) OR (((qryMaster.Mg rName)=[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.

[ChipR]

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.

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"
  )
)

[BeaBea]

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.MgrNa me, qryMaster.strSS N, qryMaster.strCl aimantName, qryMaster.strPr oduct, qryMaster.strTy peofClaim, qryMaster.ysnAp proved, qryMaster.ysnNo nComp, qryMaster.[curr$$Paid], qryMaster.dtmDe cisionDate, tblDBS.DBSName, tblMain.dtmDeci sionDate AS Expr1, Forms!ParamForm 3.FindMgr AS Expr2
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDB S = tblDBS.DBSName) INNER JOIN tblManagers ON (tblDBS.cmbMana ger = tblManagers.Mgr Name) AND (qryMaster.MgrN ame = tblManagers.Mgr Name)
WHERE (((qryMaster.Mg rName)=[Forms]![ParamForm3].[FindMgr]) AND ((tblDBS.DBSNam e)=[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,

[ChipR]

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:

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]="All"   'or ""
      OR
      (tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS]
    )
  )
)

[BeaBea]

Hi Chip,

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

Thank you.

[ChipR]

In that case, the code in the previous post should work, with line 14 ending with [FindDBS]=""

[BeaBea]

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.MgrNa me, qryMaster.strSS N, qryMaster.strCl aimantName, qryMaster.strPr oduct, qryMaster.strTy peofClaim, qryMaster.ysnAp proved, qryMaster.ysnNo nComp, qryMaster.[curr$$Paid], qryMaster.dtmDe cisionDate, tblDBS.DBSName, tblMain.dtmDeci sionDate AS Expr1, Forms!ParamForm 3.FindMgr AS Expr2
FROM (qryMaster INNER JOIN tblDBS ON qryMaster.strDB S=tblDBS.DBSNam e) INNER JOIN tblManagers ON (tblDBS.cmbMana ger=tblManagers .MgrName) AND (qryMaster.MgrN ame=tblManagers .MgrName)
WHERE (([tblMain].[dtmDecisionDate]) Between [forms]![ParamForm3]![StartDate] AND [Forms]![ParamForm3]![EndDate]) AND (([Forms]![ParamForm3].[FindMgr])="All" OR ((qryMaster.Mgr Name)=[Forms]![ParamForm3].[FindMgr] AND ([Forms]![ParamForm3].[FindDBS]="" OR (tblDBS.DBSName )=[Forms]![ParamForm3].[FindDBS])));

Thank you,

[ChipR]

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:

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] IS NULL
      OR
      (tblDBS.DBSName)=[Forms]![ParamForm3].[FindDBS]
    )
  )
);

Glad you're getting closer.

[BeaBea]

Chip,

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

BeaBea