 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / January 2005notinlist with multiple combo boxes
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. 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. > I hope I could express myself. > > Many thanks in advance for your suggestions. 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. 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 >I'd like to rephrase the above in English to make it clear: > [quoted text clipped - 5 lines] > >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.  SignatureMarsh MVP [MS Access] 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. Thanks for your support. I will try the "Me.combo2 = Null" before the "Me.combo2.Requery". Hope it works.. Thanks, Celal 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 >I guess this will work with some touches. Many thanks for your assistance: > [quoted text clipped - 11 lines] > End If >End Sub Right! That's the way to do this. Glad to have been able to help out. SignatureMarsh MVP [MS Access] Thanks a lot 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. 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 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.