Another cascading combo challenge
in [Form]
|
From: Joe on 18 May 2010 02:39 Hi - I have a subform with two combos CboMechanism with row source = TblMechanism and CboSubMechanism dependent on selection in CboMechanism using the following code in CboMechanism Afer Update event... Private Sub CboMechanism_AfterUpdate() On Error Resume Next Select Case CboMechanism.Value Case "7" CboSubMechanism.RowSource = "tbl_SubMechEquipt" Case "9" CboSubMechanism.RowSource = "tbl_SubMechFall" Case "10" CboSubMechanism.RowSource = "tbl_SubMechLift" Case "11" CboSubMechanism.RowSource = "tbl_SubMechParachute" Case "14" CboSubMechanism.RowSource = "tbl_SubMechSlip" Case "16" End Select This works well when the cases are selected but does not clear CboSubMechanism when a non-case selection is made. I tried adding CboSubMechanism.RowSource = "tbl_SubMechMTA" in the After Update but it doesn't really resolve the problem. I've tried requerying in after update event without it resolving the issue. I would be grateful for any guidance offered. -- Joe
From: Douglas J. Steele on 18 May 2010 08:08 What do you mean by "does not clear CboSubMechanism"? Are you saying that CboSubMechanism still has a row selected in it, or are you simply that CboSubMechanism still has content? To unselect from CboSubMechanism, use Me!CboSubMechanism = Null To ensure CboSubMechanism has no data in it, use Me!CboSubMechanism.RowSource = vbNullString Me!CboSubMechanism.Requery -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Joe" <Joe(a)discussions.microsoft.com> wrote in message news:9B6DBAAF-F2CE-4BE0-A18D-2894C52D4B3E(a)microsoft.com... > Hi - > > I have a subform with two combos CboMechanism with row source = > TblMechanism > and CboSubMechanism dependent on selection in CboMechanism using the > following code in CboMechanism Afer Update event... > > Private Sub CboMechanism_AfterUpdate() > On Error Resume Next > Select Case CboMechanism.Value > Case "7" > CboSubMechanism.RowSource = "tbl_SubMechEquipt" > Case "9" > CboSubMechanism.RowSource = "tbl_SubMechFall" > Case "10" > CboSubMechanism.RowSource = "tbl_SubMechLift" > Case "11" > CboSubMechanism.RowSource = "tbl_SubMechParachute" > Case "14" > CboSubMechanism.RowSource = "tbl_SubMechSlip" > Case "16" > End Select > This works well when the cases are selected but does not clear > CboSubMechanism when a non-case selection is made. > > I tried adding CboSubMechanism.RowSource = "tbl_SubMechMTA" in the After > Update but it doesn't really resolve the problem. I've tried requerying > in > after update event without it resolving the issue. > > I would be grateful for any guidance offered. > -- > Joe
From: Joe on 19 May 2010 03:33 Doug, Thanks for your help. Using Me!CboSubMechanism = Null 1. If 'crush' (not one of the 'case' options) is selected, second combo initially shows correct empty of options. 2. When “fall” (a 'case' option) is selected second combo lists options related to 'fall' as required. 3. If “parachuting' ('case' option' is selected second combo properly lists options related to “parachuting” as expected. 4. If “Stretch” (not 'case' option) is then selected CboSubMechanism still retains the options for the last 'case' selected. No options should be available for this selection! When Me!CboSubMechanism.RowSource = VbNullString Me!CboSubMechanism.Requery are used none of the options populate for the 'case' selections in CboMechanism. I'm not sure where to go from here. I've tried combinations of the above just to eliminate that option but no go. -- Joe "Douglas J. Steele" wrote: > What do you mean by "does not clear CboSubMechanism"? Are you saying that > CboSubMechanism still has a row selected in it, or are you simply that > CboSubMechanism still has content? > > To unselect from CboSubMechanism, use > > Me!CboSubMechanism = Null > > To ensure CboSubMechanism has no data in it, use > > Me!CboSubMechanism.RowSource = vbNullString > Me!CboSubMechanism.Requery > > -- > Doug Steele, Microsoft Access MVP > http://www.AccessMVP.com/DJSteele > (no e-mails, please!) > > "Joe" <Joe(a)discussions.microsoft.com> wrote in message > news:9B6DBAAF-F2CE-4BE0-A18D-2894C52D4B3E(a)microsoft.com... > > Hi - > > > > I have a subform with two combos CboMechanism with row source = > > TblMechanism > > and CboSubMechanism dependent on selection in CboMechanism using the > > following code in CboMechanism Afer Update event... > > > > Private Sub CboMechanism_AfterUpdate() > > On Error Resume Next > > Select Case CboMechanism.Value > > Case "7" > > CboSubMechanism.RowSource = "tbl_SubMechEquipt" > > Case "9" > > CboSubMechanism.RowSource = "tbl_SubMechFall" > > Case "10" > > CboSubMechanism.RowSource = "tbl_SubMechLift" > > Case "11" > > CboSubMechanism.RowSource = "tbl_SubMechParachute" > > Case "14" > > CboSubMechanism.RowSource = "tbl_SubMechSlip" > > Case "16" > > End Select > > This works well when the cases are selected but does not clear > > CboSubMechanism when a non-case selection is made. > > > > I tried adding CboSubMechanism.RowSource = "tbl_SubMechMTA" in the After > > Update but it doesn't really resolve the problem. I've tried requerying > > in > > after update event without it resolving the issue. > > > > I would be grateful for any guidance offered. > > -- > > Joe > > > . >
From: Douglas J. Steele on 19 May 2010 21:12 Please the entire routine as you've changed it. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/djsteele (no e-mails, please!) "Joe" <Joe(a)discussions.microsoft.com> wrote in message news:72181255-F7A3-4F64-90E2-0C36BD129E4A(a)microsoft.com... > Doug, Thanks for your help. > > Using Me!CboSubMechanism = Null > > 1. If 'crush' (not one of the 'case' options) is selected, second combo > initially shows correct empty of options. > > 2. When “fall” (a 'case' option) is selected second combo lists options > related to 'fall' as required. > > 3. If “parachuting' ('case' option' is selected second combo properly > lists > options related to “parachuting” as expected. > > 4. If “Stretch” (not 'case' option) is then selected CboSubMechanism still > retains the options for the last 'case' selected. No options should be > available for this selection! > > When > Me!CboSubMechanism.RowSource = VbNullString > Me!CboSubMechanism.Requery > are used none of the options populate for the 'case' selections in > CboMechanism. > > I'm not sure where to go from here. I've tried combinations of the above > just to eliminate that option but no go. > -- > Joe > > > "Douglas J. Steele" wrote: > >> What do you mean by "does not clear CboSubMechanism"? Are you saying that >> CboSubMechanism still has a row selected in it, or are you simply that >> CboSubMechanism still has content? >> >> To unselect from CboSubMechanism, use >> >> Me!CboSubMechanism = Null >> >> To ensure CboSubMechanism has no data in it, use >> >> Me!CboSubMechanism.RowSource = vbNullString >> Me!CboSubMechanism.Requery >> >> -- >> Doug Steele, Microsoft Access MVP >> http://www.AccessMVP.com/DJSteele >> (no e-mails, please!) >> >> "Joe" <Joe(a)discussions.microsoft.com> wrote in message >> news:9B6DBAAF-F2CE-4BE0-A18D-2894C52D4B3E(a)microsoft.com... >> > Hi - >> > >> > I have a subform with two combos CboMechanism with row source = >> > TblMechanism >> > and CboSubMechanism dependent on selection in CboMechanism using the >> > following code in CboMechanism Afer Update event... >> > >> > Private Sub CboMechanism_AfterUpdate() >> > On Error Resume Next >> > Select Case CboMechanism.Value >> > Case "7" >> > CboSubMechanism.RowSource = "tbl_SubMechEquipt" >> > Case "9" >> > CboSubMechanism.RowSource = "tbl_SubMechFall" >> > Case "10" >> > CboSubMechanism.RowSource = "tbl_SubMechLift" >> > Case "11" >> > CboSubMechanism.RowSource = "tbl_SubMechParachute" >> > Case "14" >> > CboSubMechanism.RowSource = "tbl_SubMechSlip" >> > Case "16" >> > End Select >> > This works well when the cases are selected but does not clear >> > CboSubMechanism when a non-case selection is made. >> > >> > I tried adding CboSubMechanism.RowSource = "tbl_SubMechMTA" in the >> > After >> > Update but it doesn't really resolve the problem. I've tried >> > requerying >> > in >> > after update event without it resolving the issue. >> > >> > I would be grateful for any guidance offered. >> > -- >> > Joe >> >> >> . >>
From: Joe on 20 May 2010 04:04 Hi Doug - You are a champion! When I reviewed the code I found that I had just appended the Me!CboSubMechanism.RowSource = VbNullString Me!CboSubMechanism.Requery after the End Select. I forgot to look at what the code was actually doing - doh! All is well now that I have put the code at the beginning of the routine . Many thanks for your patient help. You MVP's are always so helpful and it is greatly appreciated. -- Joe "Douglas J. Steele" wrote: > Please the entire routine as you've changed it. > > -- > Doug Steele, Microsoft Access MVP > http://www.AccessMVP.com/djsteele > (no e-mails, please!) > > > > "Joe" <Joe(a)discussions.microsoft.com> wrote in message > news:72181255-F7A3-4F64-90E2-0C36BD129E4A(a)microsoft.com... > > Doug, Thanks for your help. > > > > Using Me!CboSubMechanism = Null > > > > 1. If 'crush' (not one of the 'case' options) is selected, second combo > > initially shows correct empty of options. > > > > 2. When “fall” (a 'case' option) is selected second combo lists options > > related to 'fall' as required. > > > > 3. If “parachuting' ('case' option' is selected second combo properly > > lists > > options related to “parachuting” as expected. > > > > 4. If “Stretch” (not 'case' option) is then selected CboSubMechanism still > > retains the options for the last 'case' selected. No options should be > > available for this selection! > > > > When > > Me!CboSubMechanism.RowSource = VbNullString > > Me!CboSubMechanism.Requery > > are used none of the options populate for the 'case' selections in > > CboMechanism. > > > > I'm not sure where to go from here. I've tried combinations of the above > > just to eliminate that option but no go. > > -- > > Joe > > > > > > "Douglas J. Steele" wrote: > > > >> What do you mean by "does not clear CboSubMechanism"? Are you saying that > >> CboSubMechanism still has a row selected in it, or are you simply that > >> CboSubMechanism still has content? > >> > >> To unselect from CboSubMechanism, use > >> > >> Me!CboSubMechanism = Null > >> > >> To ensure CboSubMechanism has no data in it, use > >> > >> Me!CboSubMechanism.RowSource = vbNullString > >> Me!CboSubMechanism.Requery > >> > >> -- > >> Doug Steele, Microsoft Access MVP > >> http://www.AccessMVP.com/DJSteele > >> (no e-mails, please!) > >> > >> "Joe" <Joe(a)discussions.microsoft.com> wrote in message > >> news:9B6DBAAF-F2CE-4BE0-A18D-2894C52D4B3E(a)microsoft.com... > >> > Hi - > >> > > >> > I have a subform with two combos CboMechanism with row source = > >> > TblMechanism > >> > and CboSubMechanism dependent on selection in CboMechanism using the > >> > following code in CboMechanism Afer Update event... > >> > > >> > Private Sub CboMechanism_AfterUpdate() > >> > On Error Resume Next > >> > Select Case CboMechanism.Value > >> > Case "7" > >> > CboSubMechanism.RowSource = "tbl_SubMechEquipt" > >> > Case "9" > >> > CboSubMechanism.RowSource = "tbl_SubMechFall" > >> > Case "10" > >> > CboSubMechanism.RowSource = "tbl_SubMechLift" > >> > Case "11" > >> > CboSubMechanism.RowSource = "tbl_SubMechParachute" > >> > Case "14" > >> > CboSubMechanism.RowSource = "tbl_SubMechSlip" > >> > Case "16" > >> > End Select > >> > This works well when the cases are selected but does not clear > >> > CboSubMechanism when a non-case selection is made. > >> > > >> > I tried adding CboSubMechanism.RowSource = "tbl_SubMechMTA" in the > >> > After > >> > Update but it doesn't really resolve the problem. I've tried > >> > requerying > >> > in > >> > after update event without it resolving the issue. > >> > > >> > I would be grateful for any guidance offered. > >> > -- > >> > Joe > >> > >> > >> . > >> > > . >
|
Pages: 1 Prev: Adding lookup to a exsisting form Next: Combo box values for existing and new records |