notinlist with multiple combo boxes

[Celal Ozturk via AccessMonster.com]

Hello, sorry if this is not the right place to post this question, i an just new here. I have been working with Access for some time but new to VBA.

The project is too complicated to explain here but briefing on the question is, I have to identify the SITC (Standard International Trade Classification) code for each product of customers. The SITC system is a 5 digit system where the 1st digits represents the main commodity, 2nd digit reperesents commodities under it. As an example, an SITC code starting with 0 would represent Food and Live Animals and 01 would be Meat And Meat Preparations and so on.

What I needed to do has, when the 1st category was selected by the 1st category combo, the 2nd category combo would only give the data from the selected category in the 1st combo. I've done that with each combo's requery and works fine.

Works fine when entering data but not as so when editing it.
We don't have anything wrong with the 1st combo as it's "limittolist" propery is set to yes (like all others", but when editing, when I change the value of the 1st combo, the 2nd remains same even though the combo box is populated with the 1st combo's values. What I want to do is, if user leaves the 2nd combo same even though the 1st combo is changed, i need the program to interfere.

I've seen many questions and how-to's regarding adding an item to the combo but in my case i just want to restrict and have the user select what is defined.

I hope I could express myself.

Many thanks in advance for your suggestions.

 

[Alp]

Sayin Ozturk,

Sorunuz maalesef pek fazla acik degil. Editing ile neyi degistirmek
isteniyor? Kullanicilari ne ile kisitlamak arzusundasiniz? Buradaki
experlerden biri degilim ancak konu ve sorunuz dikkatimi cekti, yardimci
olmaya calisirim.

Alp

Hello, sorry if this is not the right place to post this question, i an

just new here. I have been working with Access for some time but new to VBA.

The project is too complicated to explain here but briefing on the

question is, I have to identify the SITC (Standard International Trade
Classification) code for each product of customers. The SITC system is a 5
digit system where the 1st digits represents the main commodity, 2nd digit
reperesents commodities under it. As an example, an SITC code starting with
0 would represent Food and Live Animals and 01 would be Meat And Meat
Preparations and so on.

What I needed to do has, when the 1st category was selected by the 1st

category combo, the 2nd category combo would only give the data from the
selected category in the 1st combo. I've done that with each combo's requery
and works fine.

Works fine when entering data but not as so when editing it.
We don't have anything wrong with the 1st combo as it's "limittolist"

propery is set to yes (like all others", but when editing, when I change the
value of the 1st combo, the 2nd remains same even though the combo box is
populated with the 1st combo's values. What I want to do is, if user leaves
the 2nd combo same even though the 1st combo is changed, i need the program
to interfere.

I've seen many questions and how-to's regarding adding an item to the

combo but in my case i just want to restrict and have the user select what
is defined.

 

[Celal Ozturk via AccessMonster.com]

Selamlar ve te?ekk?rler.

Editingden kast?m ?uydu: Kay?t girerken 2. combodaki se?enekler 1. ye g?re k?s?tland???ndan sorun yok. Ancak kay?t d?zeltmek istendi?inde ?rne?in 1. combo de?i?tirildi?inde 2. combo i?indeki kay?tlar de?i?ti?i halde, kullan?c? bunu da de?i?tirmeden ge?ebiliyor. ?rne?in ilk giri?te 1. combo 0 ve 2.si 01 diyelim. D?zeltme yap?ld???nda 1. combo 1 yap?lsa bile 2. combo 01 olarak kalabiliyor halbuki sorgu k?mesinde 01 olamaz ??nk? 1 ile ba?lamas? gerek. Yapmak istedi?im bu noktada bir uyar? vermek.

Sonradan notinlist de?il de lostfocus kullanmay? ve notinlist i de bir if ko?uluna koymay? d???nd?m ama hen?z denemedim.

 

[Celal Ozturk via AccessMonster.com]

I'd like to rephrase the above in English to make it clear:

What I meant by editing was:

When entering records, there is no problem as the 2nd combo updates according to the value selected in the 1st combo. But when you want to change the value of the 1st combo, the previously entered value in the 2nd combo CAN remain same which I want to prevent.

For example, when you first enter the new record, let's say you selected 0 in 1st combo and 01 in the 2nd combo. You come back to change the 1st combo value to 1. The 2nd combo could be left as 01 even though 01 is not in its query anymore as the values in the 2nd combo should now start with 1. I want the user to be forced to change the 2nd,3rd, 4th and 5th combo values accordingly.

I thought of using beforeupdate or lostfocus instead of notinlist and put the notinlist in an if condition. I haven't tried yet and not sure if I can use something like "if notinlist then". Any ideas on how I can do this???

Many thanks again
celal
cozturk [at] nova-trade.com

 

[Alp]

Ilk combo'nun degismesine bagli olarak "Me.Recalc" denediniz mi?

Alp

Not: Uzun yillar once DB IV (artik tarih oldu) ile bu konuda bir girisime
baslamistim, konu o nedenle ilgimi cekti. Aslinda halen de ilgiliyim bu
konuda calismakta.

Selamlar ve te?ekk?rler.

Editingden kast?m ?uydu: Kay?t girerken 2. combodaki se?enekler 1. ye g?re

k?s?tland???ndan sorun yok. Ancak kay?t d?zeltmek istendi?inde ?rne?in 1.
combo de?i?tirildi?inde 2. combo i?indeki kay?tlar de?i?ti?i halde,
kullan?c? bunu da de?i?tirmeden ge?ebiliyor. ?rne?in ilk giri?te 1. combo 0
ve 2.si 01 diyelim. D?zeltme yap?ld???nda 1. combo 1 yap?lsa bile 2. combo
01 olarak kalabiliyor halbuki sorgu k?mesinde 01 olamaz ??nk? 1 ile
ba?lamas? gerek. Yapmak istedi?im bu noktada bir uyar? vermek.

Sonradan notinlist de?il de lostfocus kullanmay? ve notinlist i de bir if

ko?uluna koymay? d???nd?m ama hen?z denemedim.

 

[Marshall Barton]

I'd like to rephrase the above in English to make it clear:

What I meant by editing was:

When entering records, there is no problem as the 2nd combo updates according to the value selected in the 1st combo. But when you want to change the value of the 1st combo, the previously entered value in the 2nd combo CAN remain same which I want to prevent.

For example, when you first enter the new record, let's say you selected 0 in 1st combo and 01 in the 2nd combo. You come back to change the 1st combo value to 1. The 2nd combo could be left as 01 even though 01 is not in its query anymore as the values in the 2nd combo should now start with 1. I want the user to be forced to change the 2nd,3rd, 4th and 5th combo values accordingly.

I thought of using beforeupdate or lostfocus instead of notinlist and put the notinlist in an if condition. I haven't tried yet and not sure if I can use something like "if notinlist then". Any ideas on how I can do this???

When dealing with dependent combo boxes using form reference
parameters, the first one generally uses its AfterUpdate
event to Requery the second combo box. At the same time,
you should clear the second combo's value by setting it to
Null. E.g.

Sub combo1_AfterUpdate()
Me.combo2 = Null
Me.combo2.Requery

If the dependency is using a constructed SQL statement
instead of a reference parameter, then the Requery line
above would be replaced by the assingment to combo2's
RowSource, but setting the value to Null would still be
appropriate as the old value is no longer consistent with
the selevtion in combo1.

If you have a chain of dependent combo boxes, each combo box
should requery only the next one down the chain, but it
should set the value to Null for **all** the lower ones.

 

[Celal Ozturk via AccessMonster.com]

Dear Marshall,

Thank you for you interest and advices.

First of all, i must state that the afterupdate() is already set and tied to the prevous combo, and requeried. So, no problem there. The problem is when updateding the prev record.

 

[Celal Ozturk via AccessMonster.com]

Mesajlar?n?z? ?ngilizce yazarsan?z buradaki herkes yararlanabilir. Do?rudan bana T?rk?e yazmak isterseniz: nova [AT} nova-trade.com.

Te?ekk?rler

 

[Celal Ozturk via AccessMonster.com]

A translation to above:

If you write your messages in English, all here could benefit. If you want to write directly to me in Turkish, please use email

 

[Celal Ozturk via AccessMonster.com]

Thanks for your support. I will try the "Me.combo2 = Null" before the "Me.combo2.Requery". Hope it works..

Thanks,
Celal

 

[Celal Ozturk via AccessMonster.com]

I guess this will work with some touches. Many thanks for your assistance:

Private Sub cbo_cat1_AfterUpdate()
Me!cbo_cat2 = Null
Me!cbo_cat2.Requery

End Sub

Private Sub cbo_cat2_LostFocus()
If IsNull(Me!cbo_cat2) Then
MsgBox "Please update Category 2"
Me!cbo_cat2.SetFocus

End If
End Sub

 

[Marshall Barton]

I guess this will work with some touches. Many thanks for your assistance:

Private Sub cbo_cat1_AfterUpdate()
Me!cbo_cat2 = Null
Me!cbo_cat2.Requery

End Sub

Private Sub cbo_cat2_LostFocus()
If IsNull(Me!cbo_cat2) Then
MsgBox "Please update Category 2"
Me!cbo_cat2.SetFocus

End If
End Sub

Right! That's the way to do this.

Glad to have been able to help out.

 

[Celal Ozturk via AccessMonster.com]

Thanks a lot