Solved

More Than One Combox Issue

Posted on 2008-10-06
14
299 Views
Last Modified: 2011-10-19
When I try to use more than one combox I have the following issue. Combox 3 when re-queried based on combox 2 results is displaying the results based on that entire column, so if you have duplicates in the column it will show the duplicates. What I want to happen is for it to re-query combo 1, then combo 2, and based on those results give me combo 3. This way it will prevent duplicates from showing. For example, lets say you have combo 1 (Ram Type), combo 2 (Ram Size), and combo 3 (Ram Speed). Now in the table you have the following columns Ram Type, Ram Size, Ram Speed. Now for the data Ram Type = Desktop, Ram Size = 256MB, and Ram Speed = PC2100. Now, lets say we add to the table this row Ram Type = Laptop, Ram Size = 256MB, and Ram Speed = PC3200. When I go to my comboxes and select Ram Type as Desktop, Ram Size as 256MB, when I view ram speed it will show both PC2100 and PC3200 even though only on exist for Desktop which is the PC2100. Any ideas how to fix this?
0
Comment
Question by:justinmoore14
  • 7
  • 7
14 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
You need to include both the results of Combo 1 and Combo 2 in the rowsource query for combo 3.  The rowsource of combo 3 should look something like this:

SELECT [RamSpeed]
FROM YourTable
WHERE [RamType] = Forms!YourFormName![Combo 1] AND [RamSize] = Forms!YourFormName![Combo 2]
0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
Hello mbizup, that still does not resolve the issue, would you be willing to look at the database itself. Oh, and sorry I forgot to mention that it is Access 2007.


0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Justin,

Go ahead and post the database (or a sample including just the relevant parts) using the Attach File feature. Make sure to remove any personal or otherwise sensitive data.   You will have to either upload it as a .mdb file ore rename an .accdb file to .txt.
I don't have Access 2007 on this computer, but maybe another Expert will pitch in.


If not, I do have Access 2007 at home and can look at it later tonight.

0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
Hello mbizup, I have gone ahead and converted the database to .mdb for you. Let me know if you have problems viewing it.



SampleParts.mdb
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Justin,

This is in explanation of what is happening, which from a database perspective is correct and expected:

- To see this, start by sorting your Ram Main table by Ram Type, ascending so that the Desktop entries show up at the top.

- You have two entries for Desktop, 256 MB.

- These are not "true" duplicates, because they are different brands (Kingston and Edge).

- In order to filter this down to one entry in the Ram Speed combo box, you could add another combo to let the user select the brand first.
In other words, the user would pick Type -> Size -> Brand -> Speed.

- Alternatively, you could simply show a second column in the Speed combo box so that the user could see that these are indeed two different brands.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Also, you have speeds showing up as duplicates in the second combo (256 shows up twice in this scenario).  

To Avoid the duplicates, use the DISTINCT keyword in the combo's SQL:

SELECT DISTINCT [RAM SIZE] FROM RamMainQuery WHERE [RAM TYPE]=Forms![RAM FORM]!cboShowCat;
0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
Apparently, I am missing something. When I make the following changes as you suggested (unless I am doing something wrong), when I select Ram Type as Desktop and Ram Size as 256MB it is still displaying all of the results that exist for 256MB in Ram Brand. It is not displaying only the results that pertain to Desktop, 256MB, and then Brand??? See updates in database attached.
SampleParts.mdb
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Okay...
The issue is that for each combo box, you need to apply criteria from *all* of the preceding combo boxes.

So the combobox dependencies are as follows:

Type:  First combo box, doesn't depend on any others
Size: Depends on Type
Brand: Depends on Size and Type
Speed: Depends on Size, Type and Brand

So you need to be sure that all the needed criteria are in the queries for each combo box.

The WHERE clauses should look something like this:

Size:
    WHERE Type = {your type combo box}

Brand:
   WHERE Type = {your type combo box} AND Size = {your Size combo box}

Speed:
   WHERE Type = {your type combo box} AND Size = {your Size combo box} AND  BRAND = {your Brand combo box}

Make sense?  :-)


One more comment - I highly recommend renaming your controls to avoid spaces, and use proper case conventions such as:
txtYourTextboxName   <------- for textboxes
cboBrand
cboSize
cboSpeed
cboYourComboName  <---- for combos
etc.
0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
Yep it make perfect since, I actually got it working right before you posted that. I have create another sample that is dumb ed down to keep confusion down for others to use, and have it posted on my site. I will add the link below. So that others know that your solution is correct but also know that the sample database is correct I am going to accept both of our answers, but give you all the points it will allow me in the morning. I really appreciate you sorting though my mess.

Thanks,

Justin
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Justin -

Glad to help.
0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
Sample Database for using 3 or 4 Comboxes.


http://www.themooressite.com/tech/MS%20Office%202007/access.html


0
 
LVL 8

Author Closing Comment

by:justinmoore14
Comment Utility
I would just like to thank you again for all of your help today.

Justin
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Thanks for posting that link.

Are you going to add more samples eventually?

And, have you thought about trying your hand at answering some Access questions here yourself?   It's a fun way to sharpen your own skills while helping others.
0
 
LVL 8

Author Comment

by:justinmoore14
Comment Utility
You are welcome, and glad to. I believe in only going though agony one time, and many others do to, so if this helps someone else who is having the same issue it makes me happy.

Yes, as time goes on I plan on adding more samples, however as I am sure you could tell by looking at my site finding time is another thing.


I have...and you are correct it is a great and fun way to sharpen your skills by helping others, that is one of the reasons why I like Expert Exchange so much. There are a great bunch of Experts here, with a wealth of knowledge.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now