[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

More Than One Combox Issue

Posted on 2008-10-06
14
Medium Priority
?
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 22649774
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
ID: 22650054
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
ID: 22650214
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:justinmoore14
ID: 22650464
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
ID: 22651758
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
ID: 22651817
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
ID: 22652184
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 22654435
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
ID: 22654650
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
ID: 22654942
Justin -

Glad to help.
0
 
LVL 8

Author Comment

by:justinmoore14
ID: 22655765
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
ID: 31503395
I would just like to thank you again for all of your help today.

Justin
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22656161
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
ID: 22658368
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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