combo box functions need help!!!

[harvey]

hi!

i am working on a college project in which i have to create a DB stock control system for a sports shop. i have just started working on it and i am not very good in DB's so i am not sure what to do. i have a created a combo box that shows data to choose from another table and another combo that does the same. the problem is that i want the second combo box to depend on what was chosen in the first box. the first combo box is manufacturer and the second is type, so i want if the user chooses adidas from the first list than the second list should automatically show the data from the types of adidas trainers from another table that has been saved, i want it to switch through tables depending on the first combo box.

i look forward to hear from any one who can tell me the solution for this and thanks in advance

 

[IP076]

I'm pretty new also, but I'll see if I can't help...

What you'll need to do is in the Row Source for your second combo box (on the Data tab) is make sure there is a column in the underlying source that has 'ManufacturerID' or whatever your first combo box is set to....In the Criteria for this column, you'll link it back to the first combo box...
=[Forms]![FormName]![Combo1]

Then you need to access the first combo box, AfterUpdate Event.

Its on the Event tab of the Properties window. Click on the button with 3 periods on it on the right side, and select code builder.

Copy this into the section that shows up right under
Private Sub Combo1_AfterUpdate()

Dim ctlCombo As Control
    ' Return Control object pointing to a combo box.
    Set ctlCombo = Me!Combo2
    ' Requery source of data for list box.
    ctlCombo.Requery

The whole code should look like this...

Private Sub Combo1_AfterUpdate()
 Dim ctlCombo As Control
    ' Return Control object pointing to a combo box.
    Set ctlCombo = Me!Combo2
    ' Requery source of data for list box.
    ctlCombo.Requery
End Sub

This code will requery the second combo box...Like I said, I'm pretty new also, hopefully this can help you out!! (OK, I'm new to the board also...why do the codes change color like that? I know the codes work in Access 2000. Just figuring out the board I guess!)

 

[harvey]

thanks for the reply but it doesnt work properly or put it that it does not do what i want it to do ro probably you misunderstood my request.

i have got about 8 tables that are just listing the type of trainers like type(adidas), type(puma), type(nike)... etc, now anohter table that has list of them manufacturers name. what i want to do is that insert a behaviour on each of the manufacturers name in the first combo box and the second combo box list should just switch through these tables lists as the user chooses the manufacturer. so that the user dont have any option to choose a wrong type of trainer for any manufacturer. however i am also happy to tackle this problem in a different way if you guys got any other solution for this problem, because i just started and haven't fully designed the project so am open for all kind of suggestions.

 

[yhgtbfk]

thanks for the reply but it doesnt work properly or put it that it does not do what i want it to do ro probably you misunderstood my request.

i have got about 8 tables that are just listing the type of trainers like type(adidas), type(puma), type(nike)... etc, now anohter table that has list of them manufacturers name. what i want to do is that insert a behaviour on each of the manufacturers name in the first combo box and the second combo box list should just switch through these tables lists as the user chooses the manufacturer. so that the user dont have any option to choose a wrong type of trainer for any manufacturer. however i am also happy to tackle this problem in a different way if you guys got any other solution for this problem, because i just started and haven't fully designed the project so am open for all kind of suggestions.

OK, I dont know your level of knowledge so feel free to ask any questions:

You have two combo boxes: Combo1 and Combo2

What you need to do is in the "after update" code for Combo1, have:

Combo2.rowsource = "SELECT type from table WHERE manufacturer = '" & combo1 & "';"

Does this help/make sense?

 

[harvey]

hi there thanx for the reply!

i followed up what you told me to do, but now when i choose an option from one combo1 the other combo2 changes automatically with the same option as combo1 and vice versa. so i end up with having two same options on different combos, even if i choose another option from the second combo it changes the first one aswell.

is there any other code that swaps through different tables to show the options in second combo depending on something is chosen from the first combo?

well my level of understanding is very low but i am determent to learn as fast as i can.

 

[yhgtbfk]

hi there thanx for the reply!

i followed up what you told me to do, but now when i choose an option from one combo1 the other combo2 changes automatically with the same option as combo1 and vice versa. so i end up with having two same options on different combos, even if i choose another option from the second combo it changes the first one aswell.

is there any other code that swaps through different tables to show the options in second combo depending on something is chosen from the first combo?

well my level of understanding is very low but i am determent to learn as fast as i can.

Create textbox 1 and textbox2. Under the properties make both locked (true) and enabled (false). The label for the first one can be "Manufacturer", and the label for the second one can be "type" or whatever.

Create a combobox1, and using the wizard, select the manufacturer from the table.
Create a combobox2, and using the wizard, select the type from the table.

For combobox2, under the properties, make sure column count = 1, column size only has one number, and make sure the rowsource = "SELECT type FROM table"

grab the very left of the combobox 1 and 2 where it is white, and drag it (shrink it) until there is no more white, so it is impossible to type into it, only select the drop down bit.

Place Combo1 next to text1, and combo2 next to text2

Go to the form properties and open up the VB editor for the onload code.

TextBox1 = Dlookup("manufacturer", "table")
TextBox2 = Dlookup("type", "table", "manufacturer = '" & textbox1 & "'")

Go to the combobox1 properties, and open the VB editor for afterupdate

textbox1 = combobox1
combobox2.rowsource = "SELCT type FROM table WHERE manufacturer = '" & textbox1 & "'"
TextBox2 = Dlookup("type", "table", "manufacturer = '" & textbox1 & "'")
'Alternatively, you can set textbox2 to null

Go to the combobox2 properties, and open the VB editor for afterupdate

Textbox2 = combobox2

I hope this helps