If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help with cascading combos
Wayne:
Again, I truly appreciate your help. Unfortunately, I don't get anyhere here. I wouldn't have thought that this seems sooo complicated. (Well, maybe not for you but for me it is). I believe when you're referring to "the control that holds the subform", you're talking about the value that is in the "Other tab" in the "Name" property, right? In your last thread, you indicated that all subform have the same control name ("subform"). That's true for the Division and Sections, but not for the BilletCode. Anyhow, it doesn't matter either way as long as it works. At this moment, I think I'm more confused that I knew before I started this thread. I'm pulling my hairs out... if I continue on this path, I'll be bald by mid of next week. Is there a remote chance that you provide me more specific information. I understand that this forum is for "learning" as well. I'm just not getting it right now. It would be truly helpful it you could provide the 2 lines of codes with the exact naming convention of the code. Again, I thank you for your help thus far!!! Tom "Wayne Morgan" wrote in message ... 1) Correct. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery The names you have listed here are the names of the forms being used as subforms, at least in the example I downloaded. However, you should be referring to the subform control on Me. This is the control that holds the subform you have named. In the example I downloaded, all of the subform controls were named "subform". If this is correct on what you're working on, change "subsubform" to "subform". The control named "subform" on the form "Subform" holds the form names "Subsubform". The control named "subform" on the form "Subsubform" holds the form named "Subsubsubform". This control is what you need to refer to NOT the name of the form. Is it safe to safe that "Me" = "Subform". "Me" refers to the form or report that the code is running on. In this case, yes, it appears that it will refer to the form named Subform. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: This subject "requery" on subforms is (in my opinion) a very abstract topic. I have tried to follow your logic but it doesn't seem to work for me. Please be patient w/ me. Okay, here's I guess where my trouble lies: 1. No combo boxes are being used on the "Mainform". Therefore I believe that this entire enity (field, controls, etc.) can be taken out of the picture, right? 2. If I open up the "Mainform" I now see all of the underlying subforms. If I click on the 1st level of subforms (Division), I can see either a) an symbol shaped like a fist + plus all of the "handles" where I could resize the form... or b) I see a small gray square and when I click on it I see a black bullet/dot in that. If I select 2a, I see that "Subform" is listed on both the "Data" and the "Other" tab. If I select 2b, I only see "Subform" listed under the "Format" tab. 3. Then If I click on the actual combo box "Division" that sits on "Subform" I see "Division" under "Data" and "Other" tab. I am as confident as I can be that I have followed your guidelines for requerying one combo based on its parent combo. Still, I get errors that indicated that "something" counldn't be found. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery Is it safe to safe that "Me" = "Subform". At this moment, I have tried all versions I could think of. I know it's me, but I can't understand the structure of this. Please bear w/ me on this. Thanks again, Tom "Wayne Morgan" wrote in message ... You will need to use the syntax mention before to refer to a control on a subform. Me.Subform.Form.cboCombobox = Null Me.Subform.Form.cboCombobox.Requery -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I have tried to requery the next level subforms... unsuccessfully though. Me.Requery won't do it, right? Also, I don't know how to set the next lower level to Null. Would you please provide me more info for that? -- Thanks, Tom "Wayne Morgan" wrote in message ... After you make a change to the upper level combo(s), requery the next lower level combo. You may also want to set the value of the next lower level combo to Null to clear the previous selection. -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks... I'll look into the syntax (I'm away from my PC where I stored the sample file). Meanwhile, I realized that "refreshing" does not always work properly. For instance, the 1st time I e.g. select "A", "AA", "AAA". All values were updated properly. However, then, if I were to switch to a different division "B" I sometimes still see "A?" or "A??" for the division and sections, respectively. Any additional pointers? Tom -----Original Message----- 1) If you refer to the query in the row source, then the query already does the joins for you. Basing a query on a query is legal. 2) The modifications I made were to get rid of the pop- ups and did when I tested it. The pop-ups happen when a query, either the one in the row source or the query it's based on, have parameters that it can't "see". This would happen if you mistype it (i.e. spelling error, syntax error, etc) or if the form being used as the source of the data is closed. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: Thanks so much for your reply and the help you've given me on this. I also appreciate your sharing the courtesy procedures used in this forum. I made the changes to the sample db, and it works fine. Now I am translating the same structure of "code" into my actual db and something doesn't seem to work out properly. I hope you don't mind me asking a few more questions: 1. Looking at the SQL of the "qryCbo"... the code suggests the following: "SELECT tbl_Divisions.Division, tbl_Sections.Sections, tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;" My questions is... here it's using INNER JOINs, primary key, foreign keys, etc. You didn't use them in the Row Source statement. Do I need to modify the RowSource to accomodate the relationships? Or isn't this necessary? 2. Currently, I get dialog boxes popping up (beginning on the 2nd tier level - Sections) that prompt me to enter the "Division" ... even though I already selected the Division from the combo. Why is that? What would I need to modify to make it work? I hope you'll have another chance to provide me more pointers so that I can achieve my goal. Thanks so much again for your advice and help, Tom "Wayne Morgan" wrote in message ... It is normally NOT polite to send the binary file without someone requesting it first. Many folks have slow, dial-up connections and don't want to have to wait for the download. Also, the potential for viruses exists. The file was small and I had a non critical computer I could open it on, so I took a look. The problem is that you need to refer to the subform control on each form. The subform control is a control that holds a subform. A subform is not open in it own right and so the name of the subform doesn't get you anywhere, you have to follow the path down from the main form. I modified the Row Source on the Division, Sections, and Billet Codes combos as follows and it appears to do what you're asking. SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division; SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!Subform.Form! Division)) ORDER BY qryCbo.Sections; SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!SubForm.Form! Division) And ((qryCbo.Sections)=Forms!MainForm!Subform.Form! Subform.Form!Sections)) ORDER BY qryCbo.BilletCode; -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Hello: I have posted multiple threads in this forum. I have received valuable feedback for my questions. Unfortunately, I have not been able to get my problem with cascading combo boxes solved. At this time, I have attached a sample file (I hope that's not considered unpolite). Maybe this will illustrate what I'm trying to achieve. What's in the db: Tables: - tblDataStorage (holds values that were selected in the combo boxes) - tblBoards (resides on a mainform - values are independent from the cascading combos) - tblDivisions (values are "parent" to values in tblSections) - tblSections (values are "parent" to values in tblBilletCodes) - tblBilletCode Query: - qryCbo; strings the relationships of the 3-tier (Division to Sections to BilletCodes) combo box values. Forms: - Mainform (contains the independent value of "Board 1" plus the subforms - Subform: this is where 1st tier (Division) resides - Subsubform: this is where 2nd tier (Section) resides - Subsubsubform: this is where 3rd tier (BilletCode) resides Here's what I need to achieve: - When selecting a value from "Division" on "Subform" - then update values "Sections" accordingly - then when selecting a value from "Sections" on "Subsubform" - then update values "BilletCode" - then select appropriate BilletCode At this time, requerying the combos between the 3 tiers don't work. Does anyone can provide me some help w/ that? Thanks so much in advance, Tom . |
#12
|
|||
|
|||
Need help with cascading combos
|
#13
|
|||
|
|||
Need help with cascading combos
Wayne:
Thanks so much for offering this... I emailed the file to the account listed below. Again, I truly appreciate your help on this!!! -- Thanks, Tom "Wayne Morgan" wrote in message ... Tom, Will you zip up the database and send it with a short explanation to ? -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Again, I truly appreciate your help. Unfortunately, I don't get anyhere here. I wouldn't have thought that this seems sooo complicated. (Well, maybe not for you but for me it is). I believe when you're referring to "the control that holds the subform", you're talking about the value that is in the "Other tab" in the "Name" property, right? In your last thread, you indicated that all subform have the same control name ("subform"). That's true for the Division and Sections, but not for the BilletCode. Anyhow, it doesn't matter either way as long as it works. At this moment, I think I'm more confused that I knew before I started this thread. I'm pulling my hairs out... if I continue on this path, I'll be bald by mid of next week. Is there a remote chance that you provide me more specific information. I understand that this forum is for "learning" as well. I'm just not getting it right now. It would be truly helpful it you could provide the 2 lines of codes with the exact naming convention of the code. Again, I thank you for your help thus far!!! Tom "Wayne Morgan" wrote in message ... 1) Correct. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery The names you have listed here are the names of the forms being used as subforms, at least in the example I downloaded. However, you should be referring to the subform control on Me. This is the control that holds the subform you have named. In the example I downloaded, all of the subform controls were named "subform". If this is correct on what you're working on, change "subsubform" to "subform". The control named "subform" on the form "Subform" holds the form names "Subsubform". The control named "subform" on the form "Subsubform" holds the form named "Subsubsubform". This control is what you need to refer to NOT the name of the form. Is it safe to safe that "Me" = "Subform". "Me" refers to the form or report that the code is running on. In this case, yes, it appears that it will refer to the form named Subform. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: This subject "requery" on subforms is (in my opinion) a very abstract topic. I have tried to follow your logic but it doesn't seem to work for me. Please be patient w/ me. Okay, here's I guess where my trouble lies: 1. No combo boxes are being used on the "Mainform". Therefore I believe that this entire enity (field, controls, etc.) can be taken out of the picture, right? 2. If I open up the "Mainform" I now see all of the underlying subforms. If I click on the 1st level of subforms (Division), I can see either a) an symbol shaped like a fist + plus all of the "handles" where I could resize the form... or b) I see a small gray square and when I click on it I see a black bullet/dot in that. If I select 2a, I see that "Subform" is listed on both the "Data" and the "Other" tab. If I select 2b, I only see "Subform" listed under the "Format" tab. 3. Then If I click on the actual combo box "Division" that sits on "Subform" I see "Division" under "Data" and "Other" tab. I am as confident as I can be that I have followed your guidelines for requerying one combo based on its parent combo. Still, I get errors that indicated that "something" counldn't be found. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery Is it safe to safe that "Me" = "Subform". At this moment, I have tried all versions I could think of. I know it's me, but I can't understand the structure of this. Please bear w/ me on this. Thanks again, Tom "Wayne Morgan" wrote in message ... You will need to use the syntax mention before to refer to a control on a subform. Me.Subform.Form.cboCombobox = Null Me.Subform.Form.cboCombobox.Requery -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I have tried to requery the next level subforms... unsuccessfully though. Me.Requery won't do it, right? Also, I don't know how to set the next lower level to Null. Would you please provide me more info for that? -- Thanks, Tom "Wayne Morgan" wrote in message ... After you make a change to the upper level combo(s), requery the next lower level combo. You may also want to set the value of the next lower level combo to Null to clear the previous selection. -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks... I'll look into the syntax (I'm away from my PC where I stored the sample file). Meanwhile, I realized that "refreshing" does not always work properly. For instance, the 1st time I e.g. select "A", "AA", "AAA". All values were updated properly. However, then, if I were to switch to a different division "B" I sometimes still see "A?" or "A??" for the division and sections, respectively. Any additional pointers? Tom -----Original Message----- 1) If you refer to the query in the row source, then the query already does the joins for you. Basing a query on a query is legal. 2) The modifications I made were to get rid of the pop- ups and did when I tested it. The pop-ups happen when a query, either the one in the row source or the query it's based on, have parameters that it can't "see". This would happen if you mistype it (i.e. spelling error, syntax error, etc) or if the form being used as the source of the data is closed. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: Thanks so much for your reply and the help you've given me on this. I also appreciate your sharing the courtesy procedures used in this forum. I made the changes to the sample db, and it works fine. Now I am translating the same structure of "code" into my actual db and something doesn't seem to work out properly. I hope you don't mind me asking a few more questions: 1. Looking at the SQL of the "qryCbo"... the code suggests the following: "SELECT tbl_Divisions.Division, tbl_Sections.Sections, tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;" My questions is... here it's using INNER JOINs, primary key, foreign keys, etc. You didn't use them in the Row Source statement. Do I need to modify the RowSource to accomodate the relationships? Or isn't this necessary? 2. Currently, I get dialog boxes popping up (beginning on the 2nd tier level - Sections) that prompt me to enter the "Division" ... even though I already selected the Division from the combo. Why is that? What would I need to modify to make it work? I hope you'll have another chance to provide me more pointers so that I can achieve my goal. Thanks so much again for your advice and help, Tom "Wayne Morgan" wrote in message ... It is normally NOT polite to send the binary file without someone requesting it first. Many folks have slow, dial-up connections and don't want to have to wait for the download. Also, the potential for viruses exists. The file was small and I had a non critical computer I could open it on, so I took a look. The problem is that you need to refer to the subform control on each form. The subform control is a control that holds a subform. A subform is not open in it own right and so the name of the subform doesn't get you anywhere, you have to follow the path down from the main form. I modified the Row Source on the Division, Sections, and Billet Codes combos as follows and it appears to do what you're asking. SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division; SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!Subform.Form! Division)) ORDER BY qryCbo.Sections; SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!SubForm.Form! Division) And ((qryCbo.Sections)=Forms!MainForm!Subform.Form! Subform.Form!Sections)) ORDER BY qryCbo.BilletCode; -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Hello: I have posted multiple threads in this forum. I have received valuable feedback for my questions. Unfortunately, I have not been able to get my problem with cascading combo boxes solved. At this time, I have attached a sample file (I hope that's not considered unpolite). Maybe this will illustrate what I'm trying to achieve. What's in the db: Tables: - tblDataStorage (holds values that were selected in the combo boxes) - tblBoards (resides on a mainform - values are independent from the cascading combos) - tblDivisions (values are "parent" to values in tblSections) - tblSections (values are "parent" to values in tblBilletCodes) - tblBilletCode Query: - qryCbo; strings the relationships of the 3-tier (Division to Sections to BilletCodes) combo box values. Forms: - Mainform (contains the independent value of "Board 1" plus the subforms - Subform: this is where 1st tier (Division) resides - Subsubform: this is where 2nd tier (Section) resides - Subsubsubform: this is where 3rd tier (BilletCode) resides Here's what I need to achieve: - When selecting a value from "Division" on "Subform" - then update values "Sections" accordingly - then when selecting a value from "Sections" on "Subsubform" - then update values "BilletCode" - then select appropriate BilletCode At this time, requerying the combos between the 3 tiers don't work. Does anyone can provide me some help w/ that? Thanks so much in advance, Tom . |
#14
|
|||
|
|||
Need help with cascading combos
I didn't go through everthing, but I got the comboboxes working. When you
select an item in Division, Section will be filtered. I didn't see where you are filtering Billet yet by the Section combo. The Requery was in the form's AfterUpdate. There was nothing to trigger it, the action was being done in the combo box. The form doesn't update until the record is saved. The combobox updates when you make the change it. One other thing I ran into is that requerying the next combobox in line at this point would generate an error unless the subdatasheet form had been displayed at least once. So, to fix this I added a command to expand the subdatasheet before doing the requery. The DoEvents command lets this happen before the requery line is executed (its a timing thing, the next line was being executed before the subdatasheet had time to expand). -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks so much for offering this... I emailed the file to the account listed below. Again, I truly appreciate your help on this!!! -- Thanks, Tom "Wayne Morgan" wrote in message ... Tom, Will you zip up the database and send it with a short explanation to ? -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Again, I truly appreciate your help. Unfortunately, I don't get anyhere here. I wouldn't have thought that this seems sooo complicated. (Well, maybe not for you but for me it is). I believe when you're referring to "the control that holds the subform", you're talking about the value that is in the "Other tab" in the "Name" property, right? In your last thread, you indicated that all subform have the same control name ("subform"). That's true for the Division and Sections, but not for the BilletCode. Anyhow, it doesn't matter either way as long as it works. At this moment, I think I'm more confused that I knew before I started this thread. I'm pulling my hairs out... if I continue on this path, I'll be bald by mid of next week. Is there a remote chance that you provide me more specific information. I understand that this forum is for "learning" as well. I'm just not getting it right now. It would be truly helpful it you could provide the 2 lines of codes with the exact naming convention of the code. Again, I thank you for your help thus far!!! Tom "Wayne Morgan" wrote in message ... 1) Correct. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery The names you have listed here are the names of the forms being used as subforms, at least in the example I downloaded. However, you should be referring to the subform control on Me. This is the control that holds the subform you have named. In the example I downloaded, all of the subform controls were named "subform". If this is correct on what you're working on, change "subsubform" to "subform". The control named "subform" on the form "Subform" holds the form names "Subsubform". The control named "subform" on the form "Subsubform" holds the form named "Subsubsubform". This control is what you need to refer to NOT the name of the form. Is it safe to safe that "Me" = "Subform". "Me" refers to the form or report that the code is running on. In this case, yes, it appears that it will refer to the form named Subform. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: This subject "requery" on subforms is (in my opinion) a very abstract topic. I have tried to follow your logic but it doesn't seem to work for me. Please be patient w/ me. Okay, here's I guess where my trouble lies: 1. No combo boxes are being used on the "Mainform". Therefore I believe that this entire enity (field, controls, etc.) can be taken out of the picture, right? 2. If I open up the "Mainform" I now see all of the underlying subforms. If I click on the 1st level of subforms (Division), I can see either a) an symbol shaped like a fist + plus all of the "handles" where I could resize the form... or b) I see a small gray square and when I click on it I see a black bullet/dot in that. If I select 2a, I see that "Subform" is listed on both the "Data" and the "Other" tab. If I select 2b, I only see "Subform" listed under the "Format" tab. 3. Then If I click on the actual combo box "Division" that sits on "Subform" I see "Division" under "Data" and "Other" tab. I am as confident as I can be that I have followed your guidelines for requerying one combo based on its parent combo. Still, I get errors that indicated that "something" counldn't be found. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery Is it safe to safe that "Me" = "Subform". At this moment, I have tried all versions I could think of. I know it's me, but I can't understand the structure of this. Please bear w/ me on this. Thanks again, Tom "Wayne Morgan" wrote in message ... You will need to use the syntax mention before to refer to a control on a subform. Me.Subform.Form.cboCombobox = Null Me.Subform.Form.cboCombobox.Requery -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I have tried to requery the next level subforms... unsuccessfully though. Me.Requery won't do it, right? Also, I don't know how to set the next lower level to Null. Would you please provide me more info for that? -- Thanks, Tom "Wayne Morgan" wrote in message ... After you make a change to the upper level combo(s), requery the next lower level combo. You may also want to set the value of the next lower level combo to Null to clear the previous selection. -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks... I'll look into the syntax (I'm away from my PC where I stored the sample file). Meanwhile, I realized that "refreshing" does not always work properly. For instance, the 1st time I e.g. select "A", "AA", "AAA". All values were updated properly. However, then, if I were to switch to a different division "B" I sometimes still see "A?" or "A??" for the division and sections, respectively. Any additional pointers? Tom -----Original Message----- 1) If you refer to the query in the row source, then the query already does the joins for you. Basing a query on a query is legal. 2) The modifications I made were to get rid of the pop- ups and did when I tested it. The pop-ups happen when a query, either the one in the row source or the query it's based on, have parameters that it can't "see". This would happen if you mistype it (i.e. spelling error, syntax error, etc) or if the form being used as the source of the data is closed. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: Thanks so much for your reply and the help you've given me on this. I also appreciate your sharing the courtesy procedures used in this forum. I made the changes to the sample db, and it works fine. Now I am translating the same structure of "code" into my actual db and something doesn't seem to work out properly. I hope you don't mind me asking a few more questions: 1. Looking at the SQL of the "qryCbo"... the code suggests the following: "SELECT tbl_Divisions.Division, tbl_Sections.Sections, tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;" My questions is... here it's using INNER JOINs, primary key, foreign keys, etc. You didn't use them in the Row Source statement. Do I need to modify the RowSource to accomodate the relationships? Or isn't this necessary? 2. Currently, I get dialog boxes popping up (beginning on the 2nd tier level - Sections) that prompt me to enter the "Division" ... even though I already selected the Division from the combo. Why is that? What would I need to modify to make it work? I hope you'll have another chance to provide me more pointers so that I can achieve my goal. Thanks so much again for your advice and help, Tom "Wayne Morgan" wrote in message ... It is normally NOT polite to send the binary file without someone requesting it first. Many folks have slow, dial-up connections and don't want to have to wait for the download. Also, the potential for viruses exists. The file was small and I had a non critical computer I could open it on, so I took a look. The problem is that you need to refer to the subform control on each form. The subform control is a control that holds a subform. A subform is not open in it own right and so the name of the subform doesn't get you anywhere, you have to follow the path down from the main form. I modified the Row Source on the Division, Sections, and Billet Codes combos as follows and it appears to do what you're asking. SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division; SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!Subform.Form! Division)) ORDER BY qryCbo.Sections; SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!SubForm.Form! Division) And ((qryCbo.Sections)=Forms!MainForm!Subform.Form! Subform.Form!Sections)) ORDER BY qryCbo.BilletCode; -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Hello: I have posted multiple threads in this forum. I have received valuable feedback for my questions. Unfortunately, I have not been able to get my problem with cascading combo boxes solved. At this time, I have attached a sample file (I hope that's not considered unpolite). Maybe this will illustrate what I'm trying to achieve. What's in the db: Tables: - tblDataStorage (holds values that were selected in the combo boxes) - tblBoards (resides on a mainform - values are independent from the cascading combos) - tblDivisions (values are "parent" to values in tblSections) - tblSections (values are "parent" to values in tblBilletCodes) - tblBilletCode Query: - qryCbo; strings the relationships of the 3-tier (Division to Sections to BilletCodes) combo box values. Forms: - Mainform (contains the independent value of "Board 1" plus the subforms - Subform: this is where 1st tier (Division) resides - Subsubform: this is where 2nd tier (Section) resides - Subsubsubform: this is where 3rd tier (BilletCode) resides Here's what I need to achieve: - When selecting a value from "Division" on "Subform" - then update values "Sections" accordingly - then when selecting a value from "Sections" on "Subsubform" - then update values "BilletCode" - then select appropriate BilletCode At this time, requerying the combos between the 3 tiers don't work. Does anyone can provide me some help w/ that? Thanks so much in advance, Tom . |
#15
|
|||
|
|||
Need help with cascading combos
Wayne:
First of all... THANKS!!! I'm not sure if I'm entirely following your comments below. This morning I fixed the "dependency logic" between SectionCodes and BilletCodes (e.g. if "AA" then "AAA", "AAB", or "AAC", etc.). I guess -- based on what you have worked on -- the main problem still persists. That problem is the following: - Currently, there's only "A" selected for a board. - If you add "B", you get the appropriate SectionCode (BA, BB, BC). - If you then select "BA", you'll get "BAA, BAB, BAC"... so far so good... Problem begins he - After having selected "BA", if I then add "BB" in the SectionCode, I still see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB, BBC" - The same holds up for selecting "C" after having entered "B". I see "BA, BB, BC" instead of "CA, CB, CC". Is there a chance to requery the SectionCode and BilletCode for each time when changing a different value. I cannot always closes the form and reopen it after having made a single selection. I'll email you the updated version (your AfterUpdate code) w/ my combined change of "SectionCode" drives "BilletCode". I hope you won't mind having another look on this. Thanks!!! Tom "Wayne Morgan" wrote in message ... I didn't go through everthing, but I got the comboboxes working. When you select an item in Division, Section will be filtered. I didn't see where you are filtering Billet yet by the Section combo. The Requery was in the form's AfterUpdate. There was nothing to trigger it, the action was being done in the combo box. The form doesn't update until the record is saved. The combobox updates when you make the change it. One other thing I ran into is that requerying the next combobox in line at this point would generate an error unless the subdatasheet form had been displayed at least once. So, to fix this I added a command to expand the subdatasheet before doing the requery. The DoEvents command lets this happen before the requery line is executed (its a timing thing, the next line was being executed before the subdatasheet had time to expand). -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks so much for offering this... I emailed the file to the account listed below. Again, I truly appreciate your help on this!!! -- Thanks, Tom "Wayne Morgan" wrote in message ... Tom, Will you zip up the database and send it with a short explanation to ? -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Again, I truly appreciate your help. Unfortunately, I don't get anyhere here. I wouldn't have thought that this seems sooo complicated. (Well, maybe not for you but for me it is). I believe when you're referring to "the control that holds the subform", you're talking about the value that is in the "Other tab" in the "Name" property, right? In your last thread, you indicated that all subform have the same control name ("subform"). That's true for the Division and Sections, but not for the BilletCode. Anyhow, it doesn't matter either way as long as it works. At this moment, I think I'm more confused that I knew before I started this thread. I'm pulling my hairs out... if I continue on this path, I'll be bald by mid of next week. Is there a remote chance that you provide me more specific information. I understand that this forum is for "learning" as well. I'm just not getting it right now. It would be truly helpful it you could provide the 2 lines of codes with the exact naming convention of the code. Again, I thank you for your help thus far!!! Tom "Wayne Morgan" wrote in message ... 1) Correct. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery The names you have listed here are the names of the forms being used as subforms, at least in the example I downloaded. However, you should be referring to the subform control on Me. This is the control that holds the subform you have named. In the example I downloaded, all of the subform controls were named "subform". If this is correct on what you're working on, change "subsubform" to "subform". The control named "subform" on the form "Subform" holds the form names "Subsubform". The control named "subform" on the form "Subsubform" holds the form named "Subsubsubform". This control is what you need to refer to NOT the name of the form. Is it safe to safe that "Me" = "Subform". "Me" refers to the form or report that the code is running on. In this case, yes, it appears that it will refer to the form named Subform. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: This subject "requery" on subforms is (in my opinion) a very abstract topic. I have tried to follow your logic but it doesn't seem to work for me. Please be patient w/ me. Okay, here's I guess where my trouble lies: 1. No combo boxes are being used on the "Mainform". Therefore I believe that this entire enity (field, controls, etc.) can be taken out of the picture, right? 2. If I open up the "Mainform" I now see all of the underlying subforms. If I click on the 1st level of subforms (Division), I can see either a) an symbol shaped like a fist + plus all of the "handles" where I could resize the form... or b) I see a small gray square and when I click on it I see a black bullet/dot in that. If I select 2a, I see that "Subform" is listed on both the "Data" and the "Other" tab. If I select 2b, I only see "Subform" listed under the "Format" tab. 3. Then If I click on the actual combo box "Division" that sits on "Subform" I see "Division" under "Data" and "Other" tab. I am as confident as I can be that I have followed your guidelines for requerying one combo based on its parent combo. Still, I get errors that indicated that "something" counldn't be found. Given the naming convention of - Mainform (general data) - Subform (Division combo) - Subsubform (Sections combo) - Subsubsubform (BilletCode) If changing the combobox "Division" on "Subform" (1st subform layer), the following doesn't work for me: Me.Subsubform.Form.Sections = Null Me.Subsubform.Form.Sections.Requery Is it safe to safe that "Me" = "Subform". At this moment, I have tried all versions I could think of. I know it's me, but I can't understand the structure of this. Please bear w/ me on this. Thanks again, Tom "Wayne Morgan" wrote in message ... You will need to use the syntax mention before to refer to a control on a subform. Me.Subform.Form.cboCombobox = Null Me.Subform.Form.cboCombobox.Requery -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I have tried to requery the next level subforms... unsuccessfully though. Me.Requery won't do it, right? Also, I don't know how to set the next lower level to Null. Would you please provide me more info for that? -- Thanks, Tom "Wayne Morgan" wrote in message ... After you make a change to the upper level combo(s), requery the next lower level combo. You may also want to set the value of the next lower level combo to Null to clear the previous selection. -- Wayne Morgan MS Access MVP "Tom" wrote in message ... Wayne: Thanks... I'll look into the syntax (I'm away from my PC where I stored the sample file). Meanwhile, I realized that "refreshing" does not always work properly. For instance, the 1st time I e.g. select "A", "AA", "AAA". All values were updated properly. However, then, if I were to switch to a different division "B" I sometimes still see "A?" or "A??" for the division and sections, respectively. Any additional pointers? Tom -----Original Message----- 1) If you refer to the query in the row source, then the query already does the joins for you. Basing a query on a query is legal. 2) The modifications I made were to get rid of the pop- ups and did when I tested it. The pop-ups happen when a query, either the one in the row source or the query it's based on, have parameters that it can't "see". This would happen if you mistype it (i.e. spelling error, syntax error, etc) or if the form being used as the source of the data is closed. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: Thanks so much for your reply and the help you've given me on this. I also appreciate your sharing the courtesy procedures used in this forum. I made the changes to the sample db, and it works fine. Now I am translating the same structure of "code" into my actual db and something doesn't seem to work out properly. I hope you don't mind me asking a few more questions: 1. Looking at the SQL of the "qryCbo"... the code suggests the following: "SELECT tbl_Divisions.Division, tbl_Sections.Sections, tbl_BilletCode.BilletCode FROM (tbl_Divisions INNER JOIN tbl_Sections ON tbl_Divisions.DivisionID = tbl_Sections.DivisionIDfk) INNER JOIN tbl_BilletCode ON tbl_Sections.SectionID = tbl_BilletCode.SectionIDfk;" My questions is... here it's using INNER JOINs, primary key, foreign keys, etc. You didn't use them in the Row Source statement. Do I need to modify the RowSource to accomodate the relationships? Or isn't this necessary? 2. Currently, I get dialog boxes popping up (beginning on the 2nd tier level - Sections) that prompt me to enter the "Division" ... even though I already selected the Division from the combo. Why is that? What would I need to modify to make it work? I hope you'll have another chance to provide me more pointers so that I can achieve my goal. Thanks so much again for your advice and help, Tom "Wayne Morgan" wrote in message ... It is normally NOT polite to send the binary file without someone requesting it first. Many folks have slow, dial-up connections and don't want to have to wait for the download. Also, the potential for viruses exists. The file was small and I had a non critical computer I could open it on, so I took a look. The problem is that you need to refer to the subform control on each form. The subform control is a control that holds a subform. A subform is not open in it own right and so the name of the subform doesn't get you anywhere, you have to follow the path down from the main form. I modified the Row Source on the Division, Sections, and Billet Codes combos as follows and it appears to do what you're asking. SELECT DISTINCT qryCbo.Division FROM qryCbo ORDER BY qryCbo.Division; SELECT DISTINCT qryCbo.Sections FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!Subform.Form! Division)) ORDER BY qryCbo.Sections; SELECT DISTINCT qryCbo.BilletCode FROM qryCbo WHERE (((qryCbo.Division)=Forms!MainForm!SubForm.Form! Division) And ((qryCbo.Sections)=Forms!MainForm!Subform.Form! Subform.Form!Sections)) ORDER BY qryCbo.BilletCode; -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Hello: I have posted multiple threads in this forum. I have received valuable feedback for my questions. Unfortunately, I have not been able to get my problem with cascading combo boxes solved. At this time, I have attached a sample file (I hope that's not considered unpolite). Maybe this will illustrate what I'm trying to achieve. What's in the db: Tables: - tblDataStorage (holds values that were selected in the combo boxes) - tblBoards (resides on a mainform - values are independent from the cascading combos) - tblDivisions (values are "parent" to values in tblSections) - tblSections (values are "parent" to values in tblBilletCodes) - tblBilletCode Query: - qryCbo; strings the relationships of the 3-tier (Division to Sections to BilletCodes) combo box values. Forms: - Mainform (contains the independent value of "Board 1" plus the subforms - Subform: this is where 1st tier (Division) resides - Subsubform: this is where 2nd tier (Section) resides - Subsubsubform: this is where 3rd tier (BilletCode) resides Here's what I need to achieve: - When selecting a value from "Division" on "Subform" - then update values "Sections" accordingly - then when selecting a value from "Sections" on "Subsubform" - then update values "BilletCode" - then select appropriate BilletCode At this time, requerying the combos between the 3 tiers don't work. Does anyone can provide me some help w/ that? Thanks so much in advance, Tom . |
#16
|
|||
|
|||
Need help with cascading combos
Tom,
I don't mind having another look, but I've checked my email and haven't seen anything. If you sent it, it got lost. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: First of all... THANKS!!! I'm not sure if I'm entirely following your comments below. This morning I fixed the "dependency logic" between SectionCodes and BilletCodes (e.g. if "AA" then "AAA", "AAB", or "AAC", etc.). I guess -- based on what you have worked on -- the main problem still persists. That problem is the following: - Currently, there's only "A" selected for a board. - If you add "B", you get the appropriate SectionCode (BA, BB, BC). - If you then select "BA", you'll get "BAA, BAB, BAC"... so far so good... Problem begins he - After having selected "BA", if I then add "BB" in the SectionCode, I still see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB, BBC" - The same holds up for selecting "C" after having entered "B". I see "BA, BB, BC" instead of "CA, CB, CC". Is there a chance to requery the SectionCode and BilletCode for each time when changing a different value. I cannot always closes the form and reopen it after having made a single selection. I'll email you the updated version (your AfterUpdate code) w/ my combined change of "SectionCode" drives "BilletCode". I hope you won't mind having another look on this. Thanks!!! Tom |
#17
|
|||
|
|||
Need help with cascading combos
Wayne:
I used a webmail account for sending the attachment. You aren't the 1st person telling me that the attachment didn't get send. As a result, I have attached the document in this thread. I understand it's not really polite doing this but since it's a small file, I thought it might be okay. But first let me "say" that I truly appreciate your help in this matter. This thread has been ongoing for some time; nevertheless, you always responded and offered your help. That's simply awesome. Okay, here's what still not working properly: 1. On the level "Divsion" and "Section" I get the error "2455" when adding duplicates. In one of the earliest version, I included a "delete record" command that was called when a user attempted to enter the same value twice. I think this error now is called since "it can not requery the child combo box if the value of the parent combo box does not exist".... but then, that's only what I'm thinking... maybe I'm wrong. Since there is no dependency below the BilletCode combo box, the "delete function" works fine on the lowest level. At any rate, I need to overcome the error 2455 on the Division and Section level. Users will attempt to enter duplicates and I simply want the db take care of this problem. 2. Is there a way to NOT expand the subdatasheet but requery the values at the same time? When entering lots of data on different levels, all expanded levels could look confusing to some users. At the same time, I must ensure that the requeries are done immediately when switching values. Also, I noticed that sometimes the requery did not always update when moving from a lower level data entry to a higher level entry. What I mean by that is that when having selected e.g. "BB" on the Section Level, then entering A (Division level) will not always show "AA, AB, AC" in the subordinate combo boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight pattern there though. Hopefully, this information was sufficient to provide you an idea as to what the remaining issues are. Again, thanks for all of your help in this matter. Tom "Wayne Morgan" wrote in message ... Tom, I don't mind having another look, but I've checked my email and haven't seen anything. If you sent it, it got lost. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: First of all... THANKS!!! I'm not sure if I'm entirely following your comments below. This morning I fixed the "dependency logic" between SectionCodes and BilletCodes (e.g. if "AA" then "AAA", "AAB", or "AAC", etc.). I guess -- based on what you have worked on -- the main problem still persists. That problem is the following: - Currently, there's only "A" selected for a board. - If you add "B", you get the appropriate SectionCode (BA, BB, BC). - If you then select "BA", you'll get "BAA, BAB, BAC"... so far so good... Problem begins he - After having selected "BA", if I then add "BB" in the SectionCode, I still see the previous values from BA (BAA, BAB, BAC) instead of "BBA, BBB, BBC" - The same holds up for selecting "C" after having entered "B". I see "BA, BB, BC" instead of "CA, CB, CC". Is there a chance to requery the SectionCode and BilletCode for each time when changing a different value. I cannot always closes the form and reopen it after having made a single selection. I'll email you the updated version (your AfterUpdate code) w/ my combined change of "SectionCode" drives "BilletCode". I hope you won't mind having another look on this. Thanks!!! Tom |
#18
|
|||
|
|||
Need help with cascading combos
1. You were on the right track with the reason for the 2455 error, I added
an error handler to take care of this. 2. I tried it without expanding the subdatasheet first, but it wouldn't see the combo on the subform until it had been expanded at least one time. Also, it appears that if there is data in the subform it expands anyway. I'll see if it can be expanded and collapsed again and still work, although to make the next selection it will need to be expanded. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I used a webmail account for sending the attachment. You aren't the 1st person telling me that the attachment didn't get send. As a result, I have attached the document in this thread. I understand it's not really polite doing this but since it's a small file, I thought it might be okay. But first let me "say" that I truly appreciate your help in this matter. This thread has been ongoing for some time; nevertheless, you always responded and offered your help. That's simply awesome. Okay, here's what still not working properly: 1. On the level "Divsion" and "Section" I get the error "2455" when adding duplicates. In one of the earliest version, I included a "delete record" command that was called when a user attempted to enter the same value twice. I think this error now is called since "it can not requery the child combo box if the value of the parent combo box does not exist".... but then, that's only what I'm thinking... maybe I'm wrong. Since there is no dependency below the BilletCode combo box, the "delete function" works fine on the lowest level. At any rate, I need to overcome the error 2455 on the Division and Section level. Users will attempt to enter duplicates and I simply want the db take care of this problem. 2. Is there a way to NOT expand the subdatasheet but requery the values at the same time? When entering lots of data on different levels, all expanded levels could look confusing to some users. At the same time, I must ensure that the requeries are done immediately when switching values. Also, I noticed that sometimes the requery did not always update when moving from a lower level data entry to a higher level entry. What I mean by that is that when having selected e.g. "BB" on the Section Level, then entering A (Division level) will not always show "AA, AB, AC" in the subordinate combo boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight pattern there though. Hopefully, this information was sufficient to provide you an idea as to what the remaining issues are. Again, thanks for all of your help in this matter. Tom |
#19
|
|||
|
|||
Need help with cascading combos
Wayne:
Thanks, I send you an email w/ a final question. Bummer, I now realize that I should have posted this question in here... unfortunately, the webbrowser I'm using does NOT have a SENT box. Hence, would you please respond to the email one more time. Thanks, Tom "Wayne Morgan" wrote in message ... 1. You were on the right track with the reason for the 2455 error, I added an error handler to take care of this. 2. I tried it without expanding the subdatasheet first, but it wouldn't see the combo on the subform until it had been expanded at least one time. Also, it appears that if there is data in the subform it expands anyway. I'll see if it can be expanded and collapsed again and still work, although to make the next selection it will need to be expanded. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I used a webmail account for sending the attachment. You aren't the 1st person telling me that the attachment didn't get send. As a result, I have attached the document in this thread. I understand it's not really polite doing this but since it's a small file, I thought it might be okay. But first let me "say" that I truly appreciate your help in this matter. This thread has been ongoing for some time; nevertheless, you always responded and offered your help. That's simply awesome. Okay, here's what still not working properly: 1. On the level "Divsion" and "Section" I get the error "2455" when adding duplicates. In one of the earliest version, I included a "delete record" command that was called when a user attempted to enter the same value twice. I think this error now is called since "it can not requery the child combo box if the value of the parent combo box does not exist".... but then, that's only what I'm thinking... maybe I'm wrong. Since there is no dependency below the BilletCode combo box, the "delete function" works fine on the lowest level. At any rate, I need to overcome the error 2455 on the Division and Section level. Users will attempt to enter duplicates and I simply want the db take care of this problem. 2. Is there a way to NOT expand the subdatasheet but requery the values at the same time? When entering lots of data on different levels, all expanded levels could look confusing to some users. At the same time, I must ensure that the requeries are done immediately when switching values. Also, I noticed that sometimes the requery did not always update when moving from a lower level data entry to a higher level entry. What I mean by that is that when having selected e.g. "BB" on the Section Level, then entering A (Division level) will not always show "AA, AB, AC" in the subordinate combo boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight pattern there though. Hopefully, this information was sufficient to provide you an idea as to what the remaining issues are. Again, thanks for all of your help in this matter. Tom |
#20
|
|||
|
|||
Need help with cascading combos
Wayne:
Your help has been superb in this matter. I received your updated combo box db... it works out great now. As a mentioned to you in the email, you truly deserve the status "MVP"... no doubt about it!!! Again, thanks so much!! -- Thanks, Tom "Tom" wrote in message ... Wayne: Thanks, I send you an email w/ a final question. Bummer, I now realize that I should have posted this question in here... unfortunately, the webbrowser I'm using does NOT have a SENT box. Hence, would you please respond to the email one more time. Thanks, Tom "Wayne Morgan" wrote in message ... 1. You were on the right track with the reason for the 2455 error, I added an error handler to take care of this. 2. I tried it without expanding the subdatasheet first, but it wouldn't see the combo on the subform until it had been expanded at least one time. Also, it appears that if there is data in the subform it expands anyway. I'll see if it can be expanded and collapsed again and still work, although to make the next selection it will need to be expanded. -- Wayne Morgan Microsoft Access MVP "Tom" wrote in message ... Wayne: I used a webmail account for sending the attachment. You aren't the 1st person telling me that the attachment didn't get send. As a result, I have attached the document in this thread. I understand it's not really polite doing this but since it's a small file, I thought it might be okay. But first let me "say" that I truly appreciate your help in this matter. This thread has been ongoing for some time; nevertheless, you always responded and offered your help. That's simply awesome. Okay, here's what still not working properly: 1. On the level "Divsion" and "Section" I get the error "2455" when adding duplicates. In one of the earliest version, I included a "delete record" command that was called when a user attempted to enter the same value twice. I think this error now is called since "it can not requery the child combo box if the value of the parent combo box does not exist".... but then, that's only what I'm thinking... maybe I'm wrong. Since there is no dependency below the BilletCode combo box, the "delete function" works fine on the lowest level. At any rate, I need to overcome the error 2455 on the Division and Section level. Users will attempt to enter duplicates and I simply want the db take care of this problem. 2. Is there a way to NOT expand the subdatasheet but requery the values at the same time? When entering lots of data on different levels, all expanded levels could look confusing to some users. At the same time, I must ensure that the requeries are done immediately when switching values. Also, I noticed that sometimes the requery did not always update when moving from a lower level data entry to a higher level entry. What I mean by that is that when having selected e.g. "BB" on the Section Level, then entering A (Division level) will not always show "AA, AB, AC" in the subordinate combo boxes. Sometimes it still shows "BA, BB, BC". I didn't see a straight pattern there though. Hopefully, this information was sufficient to provide you an idea as to what the remaining issues are. Again, thanks for all of your help in this matter. Tom |
|
Thread Tools | |
Display Modes | |
|
|