Display certain options in a Combo Box on a form 2

[althea]

HI!
Using Access97 and have a form with combo boxes. I want the values of one combo box to be based on a previous combo box on the same form. For example, If one combo box displays "laptop" or "desktop", and the user selects "laptop", I want the next combo box to display only the laptop options OR only the desktop options, not all of the options in the combo box. Any help greatly appreciated! Don't know where to start in the event procedure.

 

[THoey]

I gather you already have queries that populate the separate listboxes upon opening the form. What you need to do is in the ON_UPDATE event of the first listbox, have a requery of the second listbox adding the WHERE criteria that was selected in the first listbox.

Does that get you heading in the right direction? Terry M. Hoey

 

[althea]

How do I requery? Wouldn't it be easier to write code in VBA? Maybe an array to populate different options. Would you know how to do this?

 

[BoxHead]

Althea,

Sub Combo1_BeforeUpdate

   Combo2.Requery

End Sub

If you built your comboboxes with the wizard, select Combo2 in design view and open the Properties window. On the Data tab, select the RowSource and click the button with 3 dots to the right. It opens the underlying query in the SQL Builder view.

In the criteria line for the appropriate field, type:

    [Forms]![YourFormName]![Combo2]

Replace 'YourFormName' and 'Combo2' with your control names.


HTH

John

 

[althea]

Thanks for the great info, but my combo box is not built on a query, it is built on values I typed in. There is no way for me to requery, since it is not built on a query. The 2 combo boxes I have are both generated from a list I typed in myself. I am trying to make this form easier for someone else using it. For example, in one combo box there are 2 options, laptop or desktop. In the other there are 4 options. If a person selects laptop in one combo box, I only want 2 of the 4 options to appear in the 2nd combox box. Is this possible with combo boxes generated from fields in a table where the values are typed in?
Thanks!

 

[BoxHead]

Althea,

You'll have to change the string values of your combo box rowsource. The following shows the basic premise at work.

Private Sub Combo13_BeforeUpdate(Cancel As Integer)

    If Combo13 = "Laptop" Then
        Combo15 = ""   
        Combo15.RowSource = "Aaron;Brenda;Carla;Dave;Edna"
    ElseIf Combo13 = "Desktop" Then
        Combo15 = ""
        Combo15.RowSource = "Alice; Billy; Chauncey; Dean"
    Else
        Combo15 = ""
        Combo15.RowSource = "Aaron; Alice; Billy; Brenda; _
 Chauncey; Carla; Dean; Dave; Edna"
    End If

End Sub

HTH

John

 

[althea]

Thanks JOhn! I almost have it due to your help. But now when I use the form, no matter what I choose in the 1st two combo boxes, it always defaults to the "Else" statement in my code and gives me those 2 choices. How can I avoid this?
Here is my code so far:
Private Sub CPUType_AfterUpdate()

If CPUType = "Laptop" And CPUMfg = "Compaq" Then

CPUModel.RowSource = "Armada 5400"

ElseIf CPUType = "Laptop" And CPUMfg = "Dell" Then

CPUModel.RowSource = "Latitude CPX650"

ElseIf CPUType = "Desktop" And CPUMfg = "DELL" Then

CPUModel.RowSource = "450"

Else
CPUModel.RowSource = "GX110; GX420"

End If
End Sub

 

[BoxHead]

Althea, use some message boxes to check the values as you go.

Private Sub CPUType_AfterUpdate()

If CPUType = "Laptop" And CPUMfg = "Compaq" Then

CPUModel.RowSource = "Armada 5400"
MsgBox "Type1 = " & CPUType & vbnewline _
& "Mfg1 = " & CPUMfg" ,, "First IF"
ElseIf CPUType = "Laptop" And CPUMfg = "Dell" Then

CPUModel.RowSource = "Latitude CPX650"
MsgBox "Type2 = " & CPUType & vbnewline _
& "Mfg2 = " & CPUMfg" ,, "2nd IF"

ElseIf CPUType = "Desktop" And CPUMfg = "DELL" Then

CPUModel.RowSource = "450"
MsgBox "Type3 = " & CPUType & vbnewline _
& "Mfg3 = " & CPUMfg" ,, "3rd IF"

Else
CPUModel.RowSource = "GX110; GX420"

End If
End Sub

This should open a message box with each step.

Did you type the values of both text boxes? If CPUMfg is sourced off a table or query, your Sub could be reading a different column. The msgBox should tell you that.



HTH

John

 

[althea]

John,
Thanks so much for the suggestions, but I had to change the way I made the combo boxes. Instead of typing in the values, I made each of the boxes from a table with the options in the underlying table. Do you know how I would do the same thing I asked in the last post but with values from a table?

 

[BoxHead]

Althea,

Basing the combo boxes on table values should simplify things.

Take a look at the post in this thread from 10/16 and see if that makes sense or works for you. If not, post the fields from your table(s) with some examples of data.

I'm thinking it would like this:


CPUType CPUMfg CPUModel

  Laptop     Compaq    Armada 5400
  Desktop    Compaq    Tower XXX
  Laptop     Dell      CPX650
  Desktop    Dell      650
  unknown    unknown   GX110
  unknown    unknown   GX420

If I'm way off base, let me know and we'll figure it out.


John

 

[althea]

I like your idea to post the fields from my table with examples of data. If I populate the 3 tables with examples like your lists above, how do I get the appropriate options to appear on the combobox on the form? For example, If combobox1=laptop, and combobox2=compaq, box3=(only one option). I don't want the 3rd combo boxes to display all records in the underlying table. ONly the ones that meet certain criteria. Can you help? Does it make sense?
My goal here is to make it easy for anyone entering data about computer inventory. I want them to only see the options that apply to reduce the risk of error upon data entry.

 

[althea]

I think I'm almost there. One combo box is built on a table, and the 2nd combo is built on another table. I want the 3rd combo box to display criteria that depends on what is selected in the 1st two combo boxes. In the 3rd combo box ROW SOURCE property, I am using this SQL statment to filter:

SELECT CPUModelTable.CPUModel AS 
Expr1, CPUMfg AS Expr2
FROM 

CPUMfgTable, CPUModelTable, CPUTypeTable
WHERE 
(((CPUModelTable.CPUModel)=[Forms]![InventoryForm]![TypeofCPU]));

Nothing shows up in the 3rd box when I use the form in form view. Any ideas?

This is what the code above produces in the "Criteria" row of the 3rd combo box in Query Design View:

[Forms]![InventoryForm]![TypeofCPU]

How can I filter based on 2 combo boxes??

 

[BoxHead]

Althea,

It does sound like you're almost there! It seems like you may just be missing an "AND".

I'm more accustomed to the query grid, so I'll describe that.

You should have three columns: Type, Manufacturer and Model. The criteria for type should be

     [Forms]![InventoryForm]![TypeofCPU]

The criteria for manufacturer should be

     [Forms]![InventoryForm]![NameOfMfrCombo]

Make sure that you reference the combo box by its name and not the name of the field it is displaying.

If this doesn't work, remove all of the criteria from the query and take a look at it in Data view. Let me know what you see.

HTH



John

Use what you have,
Learn what you can,
Create what you need.

 

[althea]

Thanks! It now works.