cbo Change to Populate Listbox with Distinct Entries (1 Viewer)

E

e-H2O

New member
Local time
Today, 12:41
Joined
Jul 21, 2015
Messages
3
All - new to the forum, and today marks my first post.

I have a combo box that populates many listboxes based on a selection (listbox values are coming from another table [Master]) - simple enough. I want the listboxes to populate with DISTINCT entries - also seems simple enough (right?).

As you'll see below, if the user selects "All" from the combo box (cboSite), I want the listboxes to populate with distinct values from all sites; otherwise, any other selection is a unique site and the listboxes will populate with distinct values based on that unique site.

This works BEAUTIFULLY if I select a unique site, but when I select "All", there is always one (and only one) duplicate value in EACH listbox. That is to say: all values but one in the listbox are distinct.

FOR EXAMPLE:
  1. Select unique site (not "All") from "cboSite"
  2. The listbox "lstMajEquip" (which only has values "Yes" and "No") populates to show "Yes" and "No"
  3. Select "All" from "cboSite"
  4. The listbox "lstMajEquip" (which only has values "Yes" and "No") populates to show "Yes","No", and "No" (what?!)
As mentioned, this happens for all 8 of the listboxes, not just "lstMajEquip"...

I'm totally stumped! Any ideas as to why this might be happening?

As usual, forgive me if I'm accidentally omitting details/am using incorrect formatting for my question (still trying to figure out the ropes!). Been using Access/VBA just about every day for the past 3 months.

EDIT: using Access 2010

Code: 
Private Sub cboSite_Change()
If Me.cboSite = "All" Then
    Me.lstBusiness.RowSource = "SELECT DISTINCT Master.Business, Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.Business) IS NOT NULL) And ((Master.Business)<>''));"
    Me.lstProjectSpeed.RowSource = "SELECT DISTINCT Master.[Project Speed], Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.[Project Speed]) IS NOT NULL) And ((Master.[Project Speed])<>''));"
    Me.lstExecutionType.RowSource = "SELECT DISTINCT Master.[Execution Type], Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.[Execution Type]) IS NOT NULL) And ((Master.[Execution Type])<>''));"
    Me.lstEngineeringBy.RowSource = "SELECT DISTINCT Master.[Engineering By], Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.[Engineering By]) IS NOT NULL) And ((Master.[Engineering By])<>''));"
    Me.lstConstructionBy.RowSource = "SELECT DISTINCT Master.[Construction By], Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.[Construction By]) IS NOT NULL) And ((Master.[Construction By])<>''));"
    Me.lstComplvsReturn.RowSource = "SELECT DISTINCT Master.Compl_vs_Return, Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.Compl_vs_Return) IS NOT NULL) And ((Master.Compl_vs_Return)<>''));"
    Me.lstProjectType.RowSource = "SELECT DISTINCT Master.Project_Type, Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False) And ((Master.Project_Type) IS NOT NULL) And ((Master.Project_Type)<>''));"
    Me.lstMajEquip.RowSource = "SELECT DISTINCT Master.MajorEquipment, Master.Site, Master.Exclude FROM Master WHERE (((Master.Exclude)=False));"
Else
    Me.lstBusiness.RowSource = "SELECT DISTINCT Master.Business, Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.Business) IS NOT NULL) And ((Master.Business)<>'') And ((Master.Exclude)=False));"
    Me.lstProjectSpeed.RowSource = "SELECT DISTINCT Master.[Project Speed], Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.[Project Speed]) IS NOT NULL) And ((Master.[Project Speed])<>'') And ((Master.Exclude)=False));"
    Me.lstExecutionType.RowSource = "SELECT DISTINCT Master.[Execution Type], Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.[Execution Type]) IS NOT NULL) And ((Master.[Execution Type])<>'') And ((Master.Exclude)=False));"
    Me.lstEngineeringBy.RowSource = "SELECT DISTINCT Master.[Engineering By], Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.[Engineering By]) IS NOT NULL) And ((Master.[Engineering By])<>'') And ((Master.Exclude)=False));"
    Me.lstConstructionBy.RowSource = "SELECT DISTINCT Master.[Construction By], Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.[Construction By]) IS NOT NULL) And ((Master.[Construction By])<>'') And ((Master.Exclude)=False));"
    Me.lstComplvsReturn.RowSource = "SELECT DISTINCT Master.Compl_vs_Return, Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.Compl_vs_Return) IS NOT NULL) And ((Master.Compl_vs_Return)<>'') And ((Master.Exclude)=False));"
    Me.lstProjectType.RowSource = "SELECT DISTINCT Master.Project_Type, Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.Project_Type) IS NOT NULL) And ((Master.Project_Type)<>'') And ((Master.Exclude)=False));"
    Me.lstMajEquip.RowSource = "SELECT DISTINCT Master.MajorEquipment, Master.Site, Master.Exclude FROM Master WHERE (((Master.Site)=forms![Expenditure Plot Options]!cboSite) And ((Master.Exclude)=False));"
End If
End Sub
 
Last edited:
Ranman256

Ranman256

Well-known member
Local time
Today, 13:41
Joined
Apr 9, 2015
Messages
4,337
There was no need for all this coding. Use queries.

A table with your list of choices and queries
[Caption],[qry]
Equp, qsEquip
construct, qsConstruct.

The user picks a caption from the list, but the Qry is the bound field.
then the list can just reset to the cboChoice. No code needed.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 19:41
Joined
Nov 3, 2010
Messages
6,142
The above is NOT how such things are done - there is no need to swap the row source.

Before I tell you "the secret" show me the row source of cboSite. Do you get values from a table or have you hardcoded a list?

Further the Change event is not the right event to use for this, because it runs each time you type a character. You'd use AfterUpdate.

As to your DISTINCT predicament: your row sources return 3 values, you speak only of 1. What do the other 2 look like?
 
Last edited:
E

e-H2O

New member
Local time
Today, 12:41
Joined
Jul 21, 2015
Messages
3
Thank you both for the replies!

spikepl:

The RowSource of cboSite is a table.
Code: 
SELECT DISTINCT tblSiteNames.Abbreviation, tblSiteNames.[Site Name] FROM Master, tblSiteNames ORDER BY tblSiteNames.[Site Name];

Change vs. AfterUpdate: yep, that was an oversight. Been using AfterUpdate for literally everything else; not sure why this one was different! Thanks!

The "predicament": after reading your comment here, I realized that I really only need to return 1 value, not 3, and THAT'S ultimately where my problem came from. I'm now only returning the one, and my code works perfectly (because I was returning "Site" with each statement, I would get duplicates because the site-other parameter combo was distinct).

Of course, even though it works, if you have another suggestion about how to go about doing this, I'm all ears! As I mentioned, I'm new to the VBA world and don't know all the best ways of doing things.
 
V

vbaInet

AWF VIP
Local time
Today, 18:41
Joined
Jan 22, 2010
Messages
26,374
As a side note, the fact that you're using a so-called Master table with the DISTINCT predicate would indicate that your table isn't normalised. Are you familiar with this term?
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 19:41
Joined
Nov 3, 2010
Messages
6,142
How to add All to choices of a combo is described here:http://access.mvps.org/access/forms/frm0043.htm

And then, if your bound column is 0 or "all" when you want all:

Code: 
Me.lstBusiness.RowSource = "SELECT DISTINCT Master.Business, Master.Site, Master.Exclude FROM Master WHERE ((((Master.Site)=forms![Expenditure Plot Options]!cboSite) OR forms![Expenditure Plot Options]!cboSite) = 'all' ) And ((Master.Business) IS NOT NULL) And ((Master.Business)<>'') And ((Master.Exclude)=False)) OR;"

The above depends on the value of cboSite so you do a requery in the AfterUpdate of cboSites for each such dependent combo/list.
 
Last edited:
E

e-H2O

New member
Local time
Today, 12:41
Joined
Jul 21, 2015
Messages
3
vbaInet said:
As a side note, the fact that you're using a so-called Master table with the DISTINCT predicate would indicate that your table isn't normalised. Are you familiar with this term?
Click to expand...

I am not, but have since looked it up. Based on my limited understanding, my table is DEFINITELY not normalized... But that's not an issue for what I'm trying to do here, I don't think.

This code is part of an export-to-Excel-query-building form that allows a user to select certain parameters (metadata) to export certain projects based on these parameters. I have a plethora of projects in different sites (only 1 site per project), of different project types (only 1 type per project), etc. So I want the listboxes to populate based on the project types, construction, etc. that exist in my "Master" table of all projects (maybe that was poor naming on my part?), but I don't want the project type names, etc. to duplicate in the listbox, even though there is more than one project with that project type.

The normalization issues are related to some other types of data I've got going for each project. In an attempt to avoid having a ton of tables from which data was being pulled, I've ended up with a MONSTER of a "Master" table. Oops. :rolleyes:
 
V

vbaInet

AWF VIP
Local time
Today, 18:41
Joined
Jan 22, 2010
Messages
26,374
e-H2O said:
I am not, but have since looked it up. Based on my limited understanding, my table is DEFINITELY not normalized... But that's not an issue for what I'm trying to do here, I don't think.

The normalization issues are related to some other types of data I've got going for each project. In an attempt to avoid having a ton of tables from which data was being pulled, I've ended up with a MONSTER of a "Master" table. Oops. :rolleyes:
Click to expand...
In a database (which Access is), you need as many tables as is necessary in order to fulfil all normalisation rules. What you currently have is a flat file structure, the same as what you see in Excel and this sort of structure fails in Access.
 
You must log in or register to reply here.

Similar threads

N
Solved Record set too slow with SQL server Cloud & Parameters not working
Replies
15
Views
410
cheekybuddha
cheekybuddha
M
Populate Sub Form with similar to last main form record data
Replies
6
Views
532
MHART08
M
E
Solved Listbox search not working with aggregated query
Replies
7
Views
261
elannesse
E
H
Solved Problem with Rowsource in Combobox of Continuous Form.
Replies
15
Views
334
HillTJ
H
D
Problem with IIF statement and filtering a query
Replies
4
Views
572
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom