Subtracting list boxes 1

[KentMorand]

I have three list boxes. One contains all the names in a table. The second contains all the names that have an entry in another table. I want the third listbox to display the names that are left over. So essentially I want to subtract the second list box from the first and put those names into the third. I have no idea what the code would look like. Any ideas? Thanks in advance for your help.

 

[jdegeorge]

I do this. I have 3 dropdown boxes. The first box's row source is based on a list of users not equal to the user selected in box 2 and 3, box 2 is based on the same list not equal to box 1 and 3, etc.

Here's the criteria I put in the row source query for the first box:

<>[ctlNFS_Owner2] And <>[ctlNFS_Owner3]

Hope this helps.

Jim DeGeorge

 

[KentMorand]

You have been a great help today Jim. This would probably work but the info contained in both of the first two listboxes doesn't populate until the user selects something on the form. When I try to set the row source of the third list box to what you gave me it gives me an error on form load because there is no values in the other two list boxes. Any other ideas and again I really appreciate your help.

 

[jdegeorge]

Kent

That isn't the row source of the box. It's the criteria added to the QUERY that controls the listbox.

It won't apply to your MDB because the fields, etc. are different, but here's the code behind my 1st of 4 comboboxes:

SELECT tblPassword.[UserID#], tblPassword.User FROM tblPassword WHERE (((tblPassword.[UserID#])<>[ctlNFS_Owner] And (tblPassword.[UserID#])<>[ctlNFS_Owner2] And (tblPassword.[UserID#])<>[ctlNFS_Owner3] And (tblPassword.[UserID#])<>[ctlNFS_Owner4])) ORDER BY tblPassword.User;

This is the record source code for the 2nd box:

SELECT tblPassword.[UserID#], tblPassword.User FROM tblPassword WHERE (((tblPassword.[UserID#])<>[ctlNFS_Owner] And (tblPassword.[UserID#])<>[ctlNFS_Owner1] And (tblPassword.[UserID#])<>[ctlNFS_Owner3] And (tblPassword.[UserID#])<>[ctlNFS_Owner4])) ORDER BY tblPassword.User;

Try to configure your code into this syntax. It works.

Jim DeGeorge

 

[KentMorand]

I can't seem to get that code to work. I don't know if I'm putting in the values correctly or not but I think I'm close. What I'm trying now is to run an SQL statement on an event. It looks something like this :

SQL = &quot;Select Distinct(TechName) From tblTech where Team = '&quot; & strTeam & &quot;'&quot;
SQL = SQL & &quot;and Division = '&quot; & strDivision & &quot;'&quot;
SQL = SQL & &quot;and MonthOfReport = '&quot; & strMonth & &quot;'&quot;
SQL = SQL & &quot;and TechName <> ' & Me![lstTechReport] & ' &quot;
Set rsRecord = db.OpenRecordset(SQL)
lstSubtract.RowSource = SQL
lstSubtract.Requery

This doesn't give me any errors but of course it's not giving me any results either. I'm pretty sure Me![lstTechReport] is not passing the values of that list box into the SQL statement. Any other ideas and I can't thank you enough for trying to help this blind man see.. lol

 

[jdegeorge]

You don't need the SQL = part. Open your form in design mode and click on the first list box. On the propertys window, click on row source, then click in the elipsis (...). This will open a query window. Add your table(s) and select the fields that the list is based on. In the criteria portion of that field in the grid, you would put

<> [lstTechReport]

or whatever the name of the 2nd listbox is.

Jim DeGeorge

 

[KentMorand]

I may be confusing you but I think you want me to put this into the query builder of the third listbox? This is the box that I want to populate with the techs who are in the first box but not in the second. I did try it in the third box but it didn't produce any results but it also didn't give me any errors... yay.. lol I think you definitely have me on the right track though. I'm so close I can taste it. Your help is so greatly appreciated Jim.

 

[jdegeorge]

Thanks for the star. Glad to be of SOME help. Why don't you paste the code you have for each list box's row source, and include the names assigned to these in the form as well as the field names.

Jim DeGeorge

 

[KentMorand]

Just finished work for the day but I will post when I get home. If you're not around then Jim I'll thank you now for your help and if you get a chance you can get back to me later. You da man.

 

[jdegeorge]

Kent

I won't be checking back in until Monday. Have a good weekend.

Jim DeGeorge

 

[KentMorand]

I'll post the situation anyway in case someone else can push me over the top. I have 3 combos and 3 lists on one form. The user would selct a Division(cboDivision) and a Team(cboTeam) which would populate a listbox(lstTechName) from a Table(tblTechName). They would then select a month(cboMonth) which would populate the second listbox(lstTechReport) full of techs who have an entry for that month in another table(tblTech). On the same event the third listbox(lstTechLeft) would fill with with the difference between the first two listboxes. Thanks for getting me so close Jim.