How do I create a combo box where you can select 1 or all records?

[HockeyFan]

I currently have a combo box on a form with the names of three different people. If the user selects a name, it correctly will display all of the records that has that person's name associated with it. Works great.

What I would like to do is have a 4th selection in the combo box that says something like, 'All', which would display all of the records of all three people.

Note: The reason I posted this in forms is because the report is selected from a button on a form. When the button is clicked, it opens a report, which is based upon a query. The query looks at a combo box field on the form.

 

[TheAceMan1]

How are ya HockeyFan . . .


Have a look here faq702-4538

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HockeyFan]

Thanks Aceman. I'm not really an expert with sql, but I can try to get through it. One area where I see some confusion is they are referencing having a combo box on a form that gets the records from a table. My combo box is referencing a query, so the sql code will probably need to be a bit different from their example.

 

[dhookom]

HockeyFan,
Can you provide the SQL of the Row Source? I'm sure TheAceMan1 or someone else can provide the appropriate SQL.

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

Here is the sql for the row source of my current combo box.
I thought of creating a new combo box with a value list if this current one can't be modified for an 'all' option. This one pulls the names from a table with the 3 people's names. The fields in that table are Id, First name, Last name, and Active(yes/no).


SELECT ManagerTbl.PgmrId, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName
FROM ManagerTbl
ORDER BY ManagerTbl.ManagerLastName;

 

[dhookom]

Change the Row Source to:

SELECT PgmrId, ManagerFirstName, ManagerLastName
FROM ManagerTbl
UNION
SELECT 0,"All", "Managers"
FROM ManagerTbl
ORDER BY 3;

This assumes PgmrID is numeric. A value of 0 would be used by your query or whatever as All.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

FYI you already had the answer here:
faq702-4538

 

[HockeyFan]

The only problem Duane is that in my query, I have a field called 'direct manager'. That field gets its value from the combo box choice the user makes. I use a statement in my query such as '[Forms]![reportsfrm]![manager]'. Since 'All' has the value of '0', i'm assuming it is putting that value into the criteria field of my query and I am showing no results.

 

[PHV]

So, the criteria should be:
=[Forms]![reportsfrm]![manager] OR [Forms]![reportsfrm]![manager]=0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HockeyFan]

PHV/ Duane, I followed the link and modified my sql, however I still am seeing no results when I run the query. here is my modified sql.

SELECT ManagerTbl.PgmrId, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName FROM ManagerTbl UNION SELECT "*", "All", "Managers" FROM ManagerTbl
ORDER BY ManagerTbl.ManagerLastName;

 

[PHV]

So, the criteria should be:
=[Forms]![reportsfrm]![manager] OR [Forms]![reportsfrm]![manager]='*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HockeyFan]

In your suggestion above, [Forms]![reportsfrm]![manager] is how I originally had it.
When I changed it to [Forms]![reportsfrm]![manager]='*', that worked for the 'all' choice of my combo box, but when I selected any of the individual names, they didn't work.

 

[PHV]

Well, why not posting the whole SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HockeyFan]

Here is my combo box code and the query code. The combo box name on the form is called manager. The field in the query that has the critera of '[Forms]![reportsfrm]![manager]' is the DirectMgr field.


SELECT ManagerTbl.PgmrId, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName FROM ManagerTbl UNION SELECT "*", "<All>", "<Managers>" FROM ManagerTbl
ORDER BY ManagerTbl.ManagerLastName;

Here is my query code
SELECT DISTINCTROW tblProgramers.ProgrammerLastName, tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, tblProgramers.DirectMgr, ManagerTbl.ManagerFirstName, ManagerTbl.ManagerLastName, tblProgramers.Active
FROM ManagerTbl INNER JOIN tblProgramers ON ManagerTbl.PgmrId = tblProgramers.DirectMgr
WHERE (((tblProgramers.DirectMgr)=[Forms]![reportsfrm]![manager]) AND ((tblProgramers.Active)=-1) AND (([Forms]![reportsfrm]![manager])='*'));

 

[PHV]

Replace this:
WHERE (((tblProgramers.DirectMgr)=[Forms]![reportsfrm]![manager]) AND ((tblProgramers.Active)=-1) AND (([Forms]![reportsfrm]![manager])='*'));
with this:
WHERE (tblProgramers.DirectMgr=[Forms]![reportsfrm]![manager] OR [Forms]![reportsfrm]![manager]='*') AND tblProgramers.Active=-1;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HockeyFan]

I had to modify it slightly to get it to work. Essentially, I had to remove the -1 in the Active column in the OR row, so now only the first row has the -1 on it. It gave that error that it was too complex to evaluate. It changed the sql to the below. It is all working now. Thank you PHV and Duane for all of your help.

WHERE (((tblProgramers.DirectMgr)=[Forms]![reportsfrm]![manager]) AND ((tblProgramers.Active)=-1)) OR ((([Forms]![reportsfrm]![manager])='*'));