Two Combo Boxes Help!!

[Brlee1crv]

Here's my dilemma,
Let me set it up...

1. I have a job entry form that goes something like this:
Job Number: AP04001 - text box
Appraiser: John Smith - combo box (to select other names)
Reviewer: Alex King - combo box (to select other names)

2. I have a form that a user can utilize to pull up jobs for specific people:
Name: John Smith - combo box
Jobs: AP04001 - list box (displays all jobs under a person)

In my example what SQL can I use for the list box so that it pulls jobs if I select either Alex King or John Smith?

Currently I have this SQL for it but it doesn't seem to work:
SELECT Joblog.JobNo, Joblog.City, Joblog.Status
FROM Joblog
WHERE ((Joblog.Appraiser) Like [Forms]![frmCalendar]![cmbAppraiser1])) OR ((Joblog.Reviewer) Like [Forms]![frmJobSearch]![cmbAppraiser1]))
ORDER BY Joblog.JobNo;

Thanks.

 

[bhoran]

Ok 1st check your query by opening the form selecting one of the combo boxes and then run the query (keeping the form open) and check that you get the correct results.

The query seems ok to me.

If you are getting correct results what you need to so us adds some requery code to your form.

So on the onchange event of each combo box you would have the following code

me.lstJobs.requery

the only issue you have here is that if a user selects both combos they will get records relating to both.

 

[medic133]

I think I understand your question. Perhaps someone else has an easier way, but I think you can accomplish this with the following. I'm going to assume that you have 5 fields (there may be others) in one table named jobno, city, status, reviewer, and appraiser. What I see that you need are two queries to accomplish this. The first is a union query which I will call qryunion with the following SQL:

SELECT ALL joblog.appraiser AS Name, joblog.jobno, joblog.city, joblog.status
FROM joblog
GROUP BY joblog.appraiser, joblog.jobno, joblog.city, joblog.status
UNION ALL SELECT ALL joblog.reviewer AS Name, joblog.jobno, joblog.city, joblog.status
FROM joblog
GROUP BY joblog.reviewer, joblog.jobno, joblog.city, joblog.status;

Create this query as you would any other union query (Query/SQL Specific/Union from the menu bar. This groups the reviewer and appraiser columns into one column. Now, a simple select query will return the values using the combo box of the second form as a parameter.

SELECT joblog.jobno, joblog.status, joblog.city, qryunion.Name
FROM joblog INNER JOIN qryunion ON joblog.jobno=qryunion.jobno
GROUP BY joblog.jobno, joblog.status, joblog.city, qryunion.Name
HAVING (((qryunion.Name)=[forms]![nameofyoursecondform]![comboboxname]));

Based on what I did with an experimental db, the result of this query gives you what I understand you want. I'm just not sure how you can get the result into a list box. I've done something very similar before, but I can't seem to get it to work right now. Let me know if this is anything close to what you need. Good luck!

 

[Brlee1crv]

Thanks I'll give it a shot and post my results.

 

[Brlee1crv]

Thanks medic that worked great. I'm able to display it in a listbox on a calendar I've got. Now I have to have it show on the days on the calendar that the job lies on. I tried using this SQL for the listbox but it doesn't seem to work. No results come up.What am I doing wrong?

SELECT joblog.JobNo, joblog.Status, joblog.City, qryunion.Name
FROM joblog INNER JOIN qryunion ON joblog.JobNo = qryunion.jobno
WHERE (((Format([DueDate],"Short Date&quot)=Format([Forms]![frmCalendar]![cmbMonth] & " " & [Forms]![frmCalendar]![txtDate3] & ", " & [Forms]![frmCalendar]![cmbYear],"Short Date&quot) AND ((joblog.Appraiser) Like [Forms]![frmCalendar]![cmbAppraiser]))
GROUP BY joblog.JobNo, joblog.Status, joblog.City, qryunion.Name
HAVING (((qryunion.Name)=[forms]![frmCalendar]![cmbAppraiser]));

 

[tstowe]

I have a dbase where this is done quite simply using continents and countries. Email me if you would like the file as it is simple and small in size.

Tony

tony.stowe@tn.ngb.army.mil

 

[bhoran]

I am guessing you haven't added the requery command. You need to add this to the OnChange event of your combo boxes so that when they change the listbox is requeried and displays the relevant results, see my previous post.

 

[Brlee1crv]

OK I can get the results to display in the listbox now but if it's not one thing it's another. Now the listbox will show duplicates if I select "ALL". Can someone see if my SQL is incorrect?

Combo Box:
SELECT EmployeeName, AppraiserID AS Name FROM Appraisers UNION SELECT &quot;*&quot;, &quot;<ALL>&quot; AS Name from Appraisers
ORDER BY Name;

List Box:
SELECT joblog.JobNo, joblog.City, joblog.Status, qryunion.Name
FROM joblog INNER JOIN qryunion ON joblog.JobNo = qryunion.jobno
WHERE (((qryunion.Name) Like [forms]![frmCalendar]![cmbAppraiser]) AND ((Format([DueDate],&quot;Short Date&quot)=Format([Forms]![frmCalendar]![cmbMonth] & &quot; &quot; & [Forms]![frmCalendar]![txtDate3] & &quot;, &quot; & [Forms]![frmCalendar]![cmbYear],&quot;Short Date&quot))
ORDER BY joblog.JobNo;

Why is it pulling duplicates into the list box?

 

[bhoran]

You could add grouping to your query. As these are saved queries you can open the query that is the source of the list box then goto the menu View/Totals and in the totals row in the design grid if the query make sure they are set to group by.

 

[Brlee1crv]

I checked and everything is set to Group By. I'm wondering if the UNION query is what I should be using. I can't figure it out. Any help please?

 

[bhoran]

On your listbox query that you have posted above there is NO grouping only sorting.

List Box:
SELECT joblog.JobNo, joblog.City, joblog.Status, qryunion.Name
FROM joblog INNER JOIN qryunion ON joblog.JobNo = qryunion.jobno
WHERE (((qryunion.Name) Like [forms]![frmCalendar]![cmbAppraiser]) AND ((Format([DueDate],&quot;Short Date&quot)=Format([Forms]![frmCalendar]![cmbMonth] & &quot; &quot; & [Forms]![frmCalendar]![txtDate3] & &quot;, &quot; & [Forms]![frmCalendar]![cmbYear],&quot;Short Date&quot))
ORDER BY joblog.JobNo;


if you did it would look some thing like:

List Box:
SELECT joblog.JobNo, joblog.City, joblog.Status, qryunion.Name
FROM joblog INNER JOIN qryunion ON joblog.JobNo = qryunion.jobno
WHERE (((qryunion.Name) Like [forms]![frmCalendar]![cmbAppraiser]) AND ((Format([DueDate],&quot;Short Date&quot)=Format([Forms]![frmCalendar]![cmbMonth] & &quot; &quot; & [Forms]![frmCalendar]![txtDate3] & &quot;, &quot; & [Forms]![frmCalendar]![cmbYear],&quot;Short Date&quot))
GROUP BY JobNo, City, Status
ORDER BY joblog.JobNo;

 

[Brlee1crv]

bhoran,
Thanks. I tried grouping it and now I get a message saying:
The specified field 'JobNo' could refer to more than one table listed in the FROM clause of your SQL statement.

I only have one Joblog table with one &quot;JobNo&quot; field. No results are displayed.

 

[bhoran]

Ok somewhere you have more than one control called JobNo maybe on one of your forms? You need to call each control something different.

However, I am surprised that if you only added the grouping it did not occur before.

Put the query in Design mode and check JobNo is not replicated somewhere and also check all the groupings. Then just ensure you don't have 2 controls on the same form called JobNo.

Is this over other queries? Try running your union query again to see that there is not a conflict in the union query also.

Cheers

 

[Brlee1crv]

Ok everything works now but ever since I used the UNION query in the listboxes my calendar form has slowed to a crawl. Before it used to take 2 secs to open and now it takes nearly 10 secs. Is there any other way to get what I'd like accomplished without a UNION query?