Solved

More Than One Combox Issue

Posted on 2008-10-06
14
321 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

12 Experts available now in Live!

Get 1:1 Help Now