update combo box

[jvman]

Hi all,

I have 2 combo box that list family products and product names and the
list come from the same table, lets say Table product. Now i want to
synchronize between this two box such that everytime i choose different
option on family product box, the list on product name box will also change,
because at the moment eventhough i pick another from the family box, it wont
change anything on product name box. by the way this is the table(simplified)
ID family prod prod name
1 A pen
2 A pencil
3 A ruler
4 B eraser
5 B bag
6 C phone
7 C calculator
so for this example there are 3 items on the family prod box and 7 items on
prod name box. let say now i choose A and in prod name box will list out 3
items of it.Then when i change A to B, prod name box still lists out the item
belong to A.

So how am i going to solve this problem? i know it deal with macro or module
but i couldn't find it anywhere.Please help!

regards,
jv

 

[Graham R Seach]

The following page shows you how:
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[jvman]

thank you so much! really appreciate your kind help

jv

 

[jvman]

Hallo again Graham,

I already tried your method and this is the code i made;


Private Sub Combo0_AfterUpdate()
Dim sSQL As String

sSQL = "SELECT ProdNo " _
& " FROM Pricebook WHERE Subfamily = " & Me.Combo0 _
& " ORDER BY ProdNo"

Me.Combo2.RowSource = sSQL

End Sub


combo0 = subfamily combo
combo2 = Product number combo
both combo using "table/query" as row source. when i try to change list in
prod no box, i got "syntac error (missing operator) in query expression
'Sub family =...' ".
How to solve this problem? thanks

regards,
jv

 

[Graham R Seach]

I assume Subfamily is text. Try enclosing Me.Combo0 in double-quotes:

sSQL = "SELECT ProdNo " _
& " FROM Pricebook WHERE Subfamily = """ & Me.Combo0 & """" & _
& " ORDER BY ProdNo"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[jvman]

Hi again,

I tried it but still has an error as it has syntac error.I sorry but im
really new to this.can you please point where the error is?

regards,
jv

 

[Graham R Seach]

Sorry. My mistake.

sSQL = "SELECT ProdNo " & _
"FROM Pricebook WHERE Subfamily = """ & Me.Combo0 & """ " & _
"ORDER BY ProdNo"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[jvman]

Thank you! it works

 

[Access101]

All of this works quite well, but when I move to record two, the AfterUpdate
routine changes the combo boxes in both record 2 and record 1.

I had been on a continuous form, and went to a Single thinking that was the
problem. But it's still happening.

Any help is appreciated.