ComboBox Help (2 Viewers)

[snirben]

Hi, need your help like always
i have a table with 3 Columns ,
and a form to put information.
i want to make a combobox
to choose a "Type"
and a "sub Type"(Depends on what i choose in type)
and then it will fill the "Kgm" auto.
and the ID (Primary Key) Auto.
Based on the table 1

Right now it works when i put a number in the ID and it feels everything.
but i want to choose a type from a combobox first and then a sub type from a combobox.


exmple:
Table 1:

Form:

 

[Minty]

Cascading Comboboxes - have a read here http://www.baldyweb.com/CascadingCombo.htm and here
http://www.utteraccess.com/wiki/index.php/Cascading_Combo_Boxes

 

[Ranman256]

You have to use a form.
The form lets you enter data as a data sheet but you can add code,
When the user picks a type, the code will filter the subtype so only THOSE options are in the combo box......

Sub cboType_afterupdate()
If isNull(cboType) then
    CboSub.rowsource = "qsAllVals"
Else
     CboSub.rowsource= "qs1TypeVals"
End if

End sub

 

[snirben]

Cascading Comboboxes - have a read here http://www.baldyweb.com/CascadingCombo.htm and here
http://www.utteraccess.com/wiki/index.php/Cascading_Combo_Boxes

i need it in 1 table.
which one will help me?

 

[snirben]

You have to use a form.
The form lets you enter data as a data sheet but you can add code,
When the user picks a type, the code will filter the subtype so only THOSE options are in the combo box......

Sub cboType_afterupdate()
If isNull(cboType) then
    CboSub.rowsource = "qsAllVals"
Else
     CboSub.rowsource= "qs1TypeVals"
End if

End sub

sry but i didnt get what i need to change here

like this: ?

Sub cboType_afterupdate()
If isNull(Type) then
    subtype.rowsource = "qsAllVals"
Else
     subtype.rowsource= "qs1TypeVals"
End if

End sub

 

[Minty]

You can create your first combo to select all the available types by using a simple group query.

You then code the second combo recordsource to only display those ID's whose type = 1stComboBox.

So your query for the first combo (Lets call it cmbType) would be something like

SELECT DISTINCT [Type] FROM Table1

Then after you have updated that cmbType you set the second cmbSubType to

SELECT  SubType, Kgm FROM Table1 WHERE Type = Forms!YourFormName!cmbTypes

Your Kgm will then be equal to the value held by Me.cmbSubType.Column(1) , Although you shouldn't really store that anywhere as you can always look it up based on the sub type.

 

[Ranman256]

CAN'T be done in a table. You must use a form.
The code goes in the cboType AFTERUPDATE event. So when the user changes 1 cbo, the other cbo reads the form.

The cboSub uses the query ,qs1SubTypeVals, that looks at the form
Select [subtype] from table where [type]=forms!myForm!cboType.

 

[snirben]

You can create your first combo to select all the available types by using a simple group query.

You then code the second combo recordsource to only display those ID's whose type = 1stComboBox.

So your query for the first combo (Lets call it cmbType) would be something like

SELECT DISTINCT [Type] FROM Table1

Then after you have updated that cmbType you set the second cmbSubType to

SELECT  SubType, Kgm FROM Table1 WHERE Type = Forms!YourFormName!cmbTypes

Your Kgm will then be equal to the value held by Me.cmbSubType.Column(1) , Although you shouldn't really store that anywhere as you can always look it up based on the sub type.

Sry but i am new to access
where do i put the "SELECT DISTINCT [Type] FROM Table1" in the combobox 1?


like that?

 

[Minty]

Yes that looks correct to me. If it doesn't work as you expected, simply click the build button ... next to the row source and it will open the query window - adjust the query as you need to get the values you want in the combo.

 

[Ranman256]

In the rowsource.

 

[snirben]

Yes that looks correct to me. If it doesn't work as you expected, simply click the build button ... next to the row source and it will open the query window - adjust the query as you need to get the values you want in the combo.

this code worked for me :

SELECT DISTINCT Materials.[Type], Materials.Type FROM Materials;

but the subtype i cant get it to work, can you try to help me?
(i have changed the table1 to my table)

 

[Minty]

You may need to requery the second combo.

On the event property for Combo19 press the build button next to the AfterUpdate event - it will open the code window. Add the following single piece of code

Me.Combo21.Requery

and see if it works.

I would strongly suggest looking through these tutorials for a good basic understanding of various aspects of Access http://www.access-programmers.co.uk/microsoft-access-tutorials/

 

[snirben]

You may need to requery the second combo.

On the event property for Combo19 press the build button next to the AfterUpdate event - it will open the code window. Add the following single piece of code

Me.Combo21.Requery

and see if it works.

I would strongly suggest looking through these tutorials for a good basic understanding of various aspects of Access http://www.access-programmers.co.uk/microsoft-access-tutorials/

sry for driving you crazy, what should be in the Row Source in the Subtype combo? because that line of code didnt work.

 

[Minty]

Try putting Type in square brackets [Type] - It is a reserved name so access gets confused. Or ideally change the name of the field to MainType or something that isn't a reserved name?
http://allenbrowne.com/AppIssueBadWord.html

This is also worth a read
http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[snirben]

You may need to requery the second combo.

On the event property for Combo19 press the build button next to the AfterUpdate event - it will open the code window. Add the following single piece of code

Me.Combo21.Requery

and see if it works.

I would strongly suggest looking through these tutorials for a good basic understanding of various aspects of Access http://www.access-programmers.co.uk/microsoft-access-tutorials/

i did that, but still it opens a blank combobox.
look it the code :

 

[Ranman256]

Type and name are reserved words and shouldn't be used as field names.

 

[Minty]

Okay to test this - copy and paste that query text from the combo into a new query. (Click on SQL View to paste it as it is).
Open your form and make a selection in the first combo box.
Now run the new query you have just created - do you get the desired results ?

And please change that field name - It WILL cause you no end of problems if it isn't at the moment.

 

[snirben]

Okay to test this - copy and paste that query text from the combo into a new query. (Click on SQL View to paste it as it is).
Open your form and make a selection in the first combo box.
Now run the new query you have just created - do you get the desired results ?

And please change that field name - It WILL cause you no end of problems if it isn't at the moment.

I have changed it to "MainType"

i still get the same name as result and not the 2nd field

can someone make a table with 3 things

1. Maintype
2. subtype
3. Kgm

ID|MainType|SubType|kgm
1 IPE 100 10
2 IPE 30 3
3 IPE 5 1
4 LOL 3 1
5 LOL 6 1

 

[Minty]

Can you post up a stripped down version of your database - we only would need the table some sample data and the form.

 

[snirben]

Can you post up a stripped down version of your database - we only would need the table some sample data and the form.

i upload it.
ok so i will try to explain what i am trying to do

so
i have a 3 tables

Materials
FirstRequest
FirstRequestMat



so i build a form with:
NumberBid(from FirstRequest-FirstRequestMat)
IDmat(IDMAT Linked to ID in Materials)
MainType(From Materials)
SubType(From Materials)
kgm(From Materials)


so i want to hide the fields NumberBid and IDmat

so when i user use the combo box on "Maintype" he see a list of the types(i have Duplicate so i want it to be Grouped)
i use this code, not sure if its good.

SELECT DISTINCT Materials.[MainType], Materials.[MainType] FROM Materials;

and then on the "subtype" combobox i want it to filterd based on what i choose in "type".

and the "kgm" field is one option so no combo box but still i want it to feel automatic

finelly it will auto fill the correct IDmat From ID in the Materials Table.


can it be done?