my problem with two dynamic combobox

[Aks 2010]

Hello. I am facing a problem in access that cost me delay one week to submit my project of inventory database that I make it for computer device that used in our department.

I make combobox, name it(brand name)of computer company. other combobox that show you the model of computer. I want when user select the name of computer company then the next combobox of model display only the model of that company.

The problem is for example when I select hp copmany and I go to next combobox that will show me onle the model of hp but the model of hp for ex is :

1- dv5
2-dv6
3-pavilion 3847

when I select the second or third model then will only select the first one to fill the combobox so I need way to make combobox select any item of combobox.

I hope you understand to me.

Thank you.

[Steven Kogan]

This requires VBA coding.

The recordsource of the second combo box (Combo2) would need to filter depending on the value of the first combo box (Combo1).

You could use the after update event of the first combo box to change the recordsource of the second combo box accordingly.

The code would be something like this:

Private Sub Combo1_AfterUpdate()
    If Nz(Combo1, "") = "" Then
        Combo2.RowSource = ""
    Else
        Combo2.RowSource = "SELECT Test2.ID, Test2.Field1 " _
        & "FROM Test2 WHERE (((Test2.TestID) = " _
        & [Forms]![Parameters]![Combo1] & ")) " _
        & "ORDER BY Test2.Field1;"
    End If
End Sub

If you need more specific code, please provide me with the current RowSource of your second combo box, the name of the form, and the names of the two combo boxes.

[Aks 2010]

thank you steven to help me

i need specific code please
becuase i don't understrand the varible clearly hear

i don't understand the recordsource you want

do you mean the row source or the control source!!

the row source is Model Query
the control Source is Device Information_mod el

name of form is GQ Form

the first combobox name is BC
the secomd combobox name is MC


thank you...

[NeoPa]

Why don't you play around with Example Filtering on a Form for a while. It should help you understand the issues clearly enough to handle this problem.

PS. Please do not request help via instant messaging. This is against site rules and will not be tolerated as it is inconsiderate of our experts.

[Steven Kogan]

You are right - I meant RowSource of the 2nd combobox MC.

Can you provide the SQL of "Model Query"? The code would be modified so that the Where clause restricts values according to the contents of the first combobox BC.

The code would be something like:

Private Sub BC_AfterUpdate()
    If Nz(BC, "") = "" Then
        MC.RowSource = ""
    Else
        MC.RowSource = "SELECT * " _
        & "FROM [Model Query] WHERE (((BrandID) = " _
        & [Forms]![GQ Form]![BC] & ")) " _
        & "ORDER BY ModelName;"
    End If
End Sub

If Model Query already has a filter that references [Forms]![GQ Form]![BC] then your code could be:

Private Sub BC_AfterUpdate()
    MC.Requery
End Sub

[parodux]

Here's an example:

http://www.parodux.com/wp-content/uploads/2010/08/OneComboboxAffectingTheOther.mdb

[Aks 2010]

hello

can any one tell me how i can make parameter read the value from combobox???

that the problem i faced now to i can filter the model!

[NeoPa]

I'm sorry. What do you mean?

[Aks 2010]

the answer of members doesn't help me

i try to find other way

i put the code of query

when i select the hp in brand name
and go to model
the window of parameter pop up so when i write hp in parameter filter the model
then only the model of hp shown

i whant make it read the parameter from brand name!

[NeoPa]

Do you mean you'd like the Model ComboBox to be filtered such that it only shows those models that are relevant to the Brand?

If so, then that will involve filtering and referencing controls on your form. It's a bit difficult to proceed with you in that direction as you haven't told us any information that we would need, such as the names of your controls and how you have the form set up at the moment.

I can see that English is a bit of a problem for you. We can understand that. This information is not to do with language difficulties though. Just listing the names of the controls would be a good start.

[Steven Kogan]

Hi Aks 2010,

I notice you reposted your question, but in this thread you should be close to a solution.

Can you post the SQL for the query 'Model Query'? You wrote that Model Query is the rowsource for the 2nd combobox, named MC.

[Aks 2010]

hi steven

the SQL of Model Query is:

SELECT Model.MCode, Model.BModel, Brand.BCode, Brand.[Brand Name]
FROM Brand INNER JOIN Model ON Brand.[Brand Name] = Model.Bcode;

---
yes that query for 2nd combobox but now named BM Q
becuse i make it with the table of brand name to i can filter that model

also the first combobox is related to the Brand Query

i hope you can help me with it


thank you...

[Steven Kogan]

For this to work the first combo box would be named BC, and it should contain the Brand.Bcode (even though it displays the brand).

Private Sub BC_AfterUpdate() 
    If Nz(BC, "") = "" Then 
        [BM Q].RowSource = "" 
    Else 
        [BM Q].RowSource = "SELECT Model.MCode, Model.BModel, Brand.BCode, Brand.[Brand Name] " _ 
        & "FROM Brand INNER JOIN Model ON Brand.[Brand Name] = Model.Bcode " _ 
        & "WHERE (((Brand.Bcode) = " & me.[BC] & ")) " _ 
        & "ORDER BY Model.BModel;" 
    End If 
End Sub

Hopefully this works for you.

[NeoPa]

I would like to bring to the attention of anyone helping in here that the OP has created a new thread (Filtering the two queries in two combobox) where they had hoped to get an answer to this same problem. With their general lack of willingness to respond to requests for information (or at all generally) I have no further interest in this thread on a technical level.