I have 3 dependent combo boxes.

CboService has TblService as RowSource with bound col = 2; col count = 2;
col width 0cm;2cm and the following code in after update...
Private Sub CboService_AfterUpdate()
On Error Resume Next
Select Case CboService.Value
Case "A"
CboRank.RowSource = "TblA"
Case "B"
CboRank.RowSource = "TblB"
Case "C"
CboRank.RowSource = "TblC"
End Select
End Sub

CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and the
following code in the after update event...
Private Sub CboRank_AfterUpdate()
On Error Resume Next
Select Case CboRank.Value
Case "B1"
CboFunction.RowSource = "TblFunction"
Case "B3"
CboFunction.RowSource = "TblFunction"
Case "B7"
CboFunction.RowSource = "TblFunction"
End Select
End Sub
CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm; 0cm

Combos 1&2 populate without any problem however I just can't seem to be able
to get combo 3 to populate.

I would be very grateful for any help and if there is a better way to write
the above code I would appreciate the advice.

Many thanks,

Joe
--
Joe

I think because none of the Cases are matching for cboRank, and since all of
the values sought are TblFunction anyway, why would you even want a Case
statement?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Joe" <(E-Mail Removed)> wrote in message
newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
>I have 3 dependent combo boxes.
>
> CboService has TblService as RowSource with bound col = 2; col count = 2;
> col width 0cm;2cm and the following code in after update...
> Private Sub CboService_AfterUpdate()
> On Error Resume Next
> Select Case CboService.Value
> Case "A"
> CboRank.RowSource = "TblA"
> Case "B"
> CboRank.RowSource = "TblB"
> Case "C"
> CboRank.RowSource = "TblC"
> End Select
> End Sub
>
> CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and the
> following code in the after update event...
> Private Sub CboRank_AfterUpdate()
> On Error Resume Next
> Select Case CboRank.Value
> Case "B1"
> CboFunction.RowSource = "TblFunction"
> Case "B3"
> CboFunction.RowSource = "TblFunction"
> Case "B7"
> CboFunction.RowSource = "TblFunction"
> End Select
> End Sub
> CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm; 0cm
>
> Combos 1&2 populate without any problem however I just can't seem to be
> able
> to get combo 3 to populate.
>
> I would be very grateful for any help and if there is a better way to
> write
> the above code I would appreciate the advice.
>
> Many thanks,
>
> Joe
> --
> Joe

Thanks Arvin - I think I see what you mean however I'm unsure how to achieve
the solution to my problem. Of the three services available for selection in
the first combo, one selection (B) must also have a specific role (function)
attached to some but not all of the ranks in combo2. I had hoped that this
could be achieved using cascading combo boxes.
--
Joe


"Arvin Meyer [MVP]" wrote:

> I think because none of the Cases are matching for cboRank, and since all of
> the values sought are TblFunction anyway, why would you even want a Case
> statement?
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
>
>
> "Joe" <(E-Mail Removed)> wrote in message
> newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
> >I have 3 dependent combo boxes.
> >
> > CboService has TblService as RowSource with bound col = 2; col count = 2;
> > col width 0cm;2cm and the following code in after update...
> > Private Sub CboService_AfterUpdate()
> > On Error Resume Next
> > Select Case CboService.Value
> > Case "A"
> > CboRank.RowSource = "TblA"
> > Case "B"
> > CboRank.RowSource = "TblB"
> > Case "C"
> > CboRank.RowSource = "TblC"
> > End Select
> > End Sub
> >
> > CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and the
> > following code in the after update event...
> > Private Sub CboRank_AfterUpdate()
> > On Error Resume Next
> > Select Case CboRank.Value
> > Case "B1"
> > CboFunction.RowSource = "TblFunction"
> > Case "B3"
> > CboFunction.RowSource = "TblFunction"
> > Case "B7"
> > CboFunction.RowSource = "TblFunction"
> > End Select
> > End Sub
> > CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm; 0cm
> >
> > Combos 1&2 populate without any problem however I just can't seem to be
> > able
> > to get combo 3 to populate.
> >
> > I would be very grateful for any help and if there is a better way to
> > write
> > the above code I would appreciate the advice.
> >
> > Many thanks,
> >
> > Joe
> > --
> > Joe
>
>
> .
>

It might be simpler than that. Have a look at my sample for listboxes, which
works the very same as a combo box:

http://accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Joe" <(E-Mail Removed)> wrote in message
news:9811C840-0529-4125-ABC1-(E-Mail Removed)...
> Thanks Arvin - I think I see what you mean however I'm unsure how to
> achieve
> the solution to my problem. Of the three services available for selection
> in
> the first combo, one selection (B) must also have a specific role
> (function)
> attached to some but not all of the ranks in combo2. I had hoped that this
> could be achieved using cascading combo boxes.
> --
> Joe
>
>
> "Arvin Meyer [MVP]" wrote:
>
>> I think because none of the Cases are matching for cboRank, and since all
>> of
>> the values sought are TblFunction anyway, why would you even want a Case
>> statement?
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.accessmvp.com
>> http://www.mvps.org/access
>>
>>
>> "Joe" <(E-Mail Removed)> wrote in message
>> newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
>> >I have 3 dependent combo boxes.
>> >
>> > CboService has TblService as RowSource with bound col = 2; col count =
>> > 2;
>> > col width 0cm;2cm and the following code in after update...
>> > Private Sub CboService_AfterUpdate()
>> > On Error Resume Next
>> > Select Case CboService.Value
>> > Case "A"
>> > CboRank.RowSource = "TblA"
>> > Case "B"
>> > CboRank.RowSource = "TblB"
>> > Case "C"
>> > CboRank.RowSource = "TblC"
>> > End Select
>> > End Sub
>> >
>> > CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and the
>> > following code in the after update event...
>> > Private Sub CboRank_AfterUpdate()
>> > On Error Resume Next
>> > Select Case CboRank.Value
>> > Case "B1"
>> > CboFunction.RowSource = "TblFunction"
>> > Case "B3"
>> > CboFunction.RowSource = "TblFunction"
>> > Case "B7"
>> > CboFunction.RowSource = "TblFunction"
>> > End Select
>> > End Sub
>> > CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm; 0cm
>> >
>> > Combos 1&2 populate without any problem however I just can't seem to be
>> > able
>> > to get combo 3 to populate.
>> >
>> > I would be very grateful for any help and if there is a better way to
>> > write
>> > the above code I would appreciate the advice.
>> >
>> > Many thanks,
>> >
>> > Joe
>> > --
>> > Joe
>>
>>
>> .
>>

Thanks Arvin. After I looked at your samples I decided to try and simplify my
database amalgamating the three rank tables into one combined table with the
following structure...

TblRank
RankID Autonum PK
ServiceID FK
Rank Text

TblService
ServiceID PK
Service Text

CboService has RowSource = TblService
Bound Col = 1
Col Count = 2
Col Width = 0cm; 2cm

CboRank has a blank RowSource
Bound Col = 1
Col Count = 3
Col Width = 0cm;0cm;2.5cm

I have changed the After Update for CboService to the following Code...

Private Sub CboService_AfterUpdate()
With Me![CboRank]
If IsNull(Me!CboService) Then
.RowSource = ""
Else
.RowSource = "SELECT [Rank] " & _
"FROM tblRank " & _
"WHERE [ServiceID]= " & Me!CboService
End If
Call .Requery
End With
End Sub

The trouble is that whereas I could previously get a population of CboRank
from a selection in CboService now I get a blank list on opening CboRank.
I'm not sure where I've gone wrong but your guidance would be appreciated.
--
Joe


"Arvin Meyer [MVP]" wrote:

> It might be simpler than that. Have a look at my sample for listboxes, which
> works the very same as a combo box:
>
> http://accessmvp.com/Arvin/Combo.zip
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
>
>
> "Joe" <(E-Mail Removed)> wrote in message
> news:9811C840-0529-4125-ABC1-(E-Mail Removed)...
> > Thanks Arvin - I think I see what you mean however I'm unsure how to
> > achieve
> > the solution to my problem. Of the three services available for selection
> > in
> > the first combo, one selection (B) must also have a specific role
> > (function)
> > attached to some but not all of the ranks in combo2. I had hoped that this
> > could be achieved using cascading combo boxes.
> > --
> > Joe
> >
> >
> > "Arvin Meyer [MVP]" wrote:
> >
> >> I think because none of the Cases are matching for cboRank, and since all
> >> of
> >> the values sought are TblFunction anyway, why would you even want a Case
> >> statement?
> >> --
> >> Arvin Meyer, MCP, MVP
> >> http://www.datastrat.com
> >> http://www.accessmvp.com
> >> http://www.mvps.org/access
> >>
> >>
> >> "Joe" <(E-Mail Removed)> wrote in message
> >> newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
> >> >I have 3 dependent combo boxes.
> >> >
> >> > CboService has TblService as RowSource with bound col = 2; col count =
> >> > 2;
> >> > col width 0cm;2cm and the following code in after update...
> >> > Private Sub CboService_AfterUpdate()
> >> > On Error Resume Next
> >> > Select Case CboService.Value
> >> > Case "A"
> >> > CboRank.RowSource = "TblA"
> >> > Case "B"
> >> > CboRank.RowSource = "TblB"
> >> > Case "C"
> >> > CboRank.RowSource = "TblC"
> >> > End Select
> >> > End Sub
> >> >
> >> > CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and the
> >> > following code in the after update event...
> >> > Private Sub CboRank_AfterUpdate()
> >> > On Error Resume Next
> >> > Select Case CboRank.Value
> >> > Case "B1"
> >> > CboFunction.RowSource = "TblFunction"
> >> > Case "B3"
> >> > CboFunction.RowSource = "TblFunction"
> >> > Case "B7"
> >> > CboFunction.RowSource = "TblFunction"
> >> > End Select
> >> > End Sub
> >> > CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm; 0cm
> >> >
> >> > Combos 1&2 populate without any problem however I just can't seem to be
> >> > able
> >> > to get combo 3 to populate.
> >> >
> >> > I would be very grateful for any help and if there is a better way to
> >> > write
> >> > the above code I would appreciate the advice.
> >> >
> >> > Many thanks,
> >> >
> >> > Joe
> >> > --
> >> > Joe
> >>
> >>
> >> .
> >>
>
>
> .
>

Look at the code:

First run your select statement from the query window, substituting a value
from cboservice. Do you get any records? If not, that's your problem, make
sure that the bound column matches the value you are trying to find, and
that you don't have a type mismatch. Your code calls for a numeric datatype.

Secondly, what is:

Call .Requery

If you want to requery the cboService combo box, use:

Me.cboService.Requery

HTH,
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Joe" <(E-Mail Removed)> wrote in message
news:22A214E3-E1B3-46EB-81F8-(E-Mail Removed)...
> Thanks Arvin. After I looked at your samples I decided to try and simplify
> my
> database amalgamating the three rank tables into one combined table with
> the
> following structure...
>
> TblRank
> RankID Autonum PK
> ServiceID FK
> Rank Text
>
> TblService
> ServiceID PK
> Service Text
>
> CboService has RowSource = TblService
> Bound Col = 1
> Col Count = 2
> Col Width = 0cm; 2cm
>
> CboRank has a blank RowSource
> Bound Col = 1
> Col Count = 3
> Col Width = 0cm;0cm;2.5cm
>
> I have changed the After Update for CboService to the following Code...
>
> Private Sub CboService_AfterUpdate()
> With Me![CboRank]
> If IsNull(Me!CboService) Then
> .RowSource = ""
> Else
> .RowSource = "SELECT [Rank] " & _
> "FROM tblRank " & _
> "WHERE [ServiceID]= " & Me!CboService
> End If
> Call .Requery
> End With
> End Sub
>
> The trouble is that whereas I could previously get a population of CboRank
> from a selection in CboService now I get a blank list on opening CboRank.
> I'm not sure where I've gone wrong but your guidance would be appreciated.
> --
> Joe
>
>
> "Arvin Meyer [MVP]" wrote:
>
>> It might be simpler than that. Have a look at my sample for listboxes,
>> which
>> works the very same as a combo box:
>>
>> http://accessmvp.com/Arvin/Combo.zip
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.accessmvp.com
>> http://www.mvps.org/access
>>
>>
>> "Joe" <(E-Mail Removed)> wrote in message
>> news:9811C840-0529-4125-ABC1-(E-Mail Removed)...
>> > Thanks Arvin - I think I see what you mean however I'm unsure how to
>> > achieve
>> > the solution to my problem. Of the three services available for
>> > selection
>> > in
>> > the first combo, one selection (B) must also have a specific role
>> > (function)
>> > attached to some but not all of the ranks in combo2. I had hoped that
>> > this
>> > could be achieved using cascading combo boxes.
>> > --
>> > Joe
>> >
>> >
>> > "Arvin Meyer [MVP]" wrote:
>> >
>> >> I think because none of the Cases are matching for cboRank, and since
>> >> all
>> >> of
>> >> the values sought are TblFunction anyway, why would you even want a
>> >> Case
>> >> statement?
>> >> --
>> >> Arvin Meyer, MCP, MVP
>> >> http://www.datastrat.com
>> >> http://www.accessmvp.com
>> >> http://www.mvps.org/access
>> >>
>> >>
>> >> "Joe" <(E-Mail Removed)> wrote in message
>> >> newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
>> >> >I have 3 dependent combo boxes.
>> >> >
>> >> > CboService has TblService as RowSource with bound col = 2; col count
>> >> > =
>> >> > 2;
>> >> > col width 0cm;2cm and the following code in after update...
>> >> > Private Sub CboService_AfterUpdate()
>> >> > On Error Resume Next
>> >> > Select Case CboService.Value
>> >> > Case "A"
>> >> > CboRank.RowSource = "TblA"
>> >> > Case "B"
>> >> > CboRank.RowSource = "TblB"
>> >> > Case "C"
>> >> > CboRank.RowSource = "TblC"
>> >> > End Select
>> >> > End Sub
>> >> >
>> >> > CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and
>> >> > the
>> >> > following code in the after update event...
>> >> > Private Sub CboRank_AfterUpdate()
>> >> > On Error Resume Next
>> >> > Select Case CboRank.Value
>> >> > Case "B1"
>> >> > CboFunction.RowSource = "TblFunction"
>> >> > Case "B3"
>> >> > CboFunction.RowSource = "TblFunction"
>> >> > Case "B7"
>> >> > CboFunction.RowSource = "TblFunction"
>> >> > End Select
>> >> > End Sub
>> >> > CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm;
>> >> > 0cm
>> >> >
>> >> > Combos 1&2 populate without any problem however I just can't seem to
>> >> > be
>> >> > able
>> >> > to get combo 3 to populate.
>> >> >
>> >> > I would be very grateful for any help and if there is a better way
>> >> > to
>> >> > write
>> >> > the above code I would appreciate the advice.
>> >> >
>> >> > Many thanks,
>> >> >
>> >> > Joe
>> >> > --
>> >> > Joe
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

Hi Arvin -
Success at last! It has taken a while but I found that my main problem was
that I was attempting to use my code in a subform.

New code is now as a query in row source of second combo (CboRank) with
CboService with row source as TblService.

Code reads:
SELECT Tbl_Rank.Rank_ID, Tbl_Rank.Rank, Tbl_Rank.Service_ID
FROM Tbl_Rank
WHERE (((Tbl_Rank.Service_ID)=[Forms]![Frm_Discharge Summary
Main]![SubFrm_Disch Summary].[Form]![CboService]))
ORDER BY Tbl_Rank.Rank;

And I have a requery in the after update event in CboService. All working
fine now - Many thanks for your help.
--
Joe


"Arvin Meyer [MVP]" wrote:

> Look at the code:
>
> First run your select statement from the query window, substituting a value
> from cboservice. Do you get any records? If not, that's your problem, make
> sure that the bound column matches the value you are trying to find, and
> that you don't have a type mismatch. Your code calls for a numeric datatype.
>
> Secondly, what is:
>
> Call .Requery
>
> If you want to requery the cboService combo box, use:
>
> Me.cboService.Requery
>
> HTH,
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
>
>
> "Joe" <(E-Mail Removed)> wrote in message
> news:22A214E3-E1B3-46EB-81F8-(E-Mail Removed)...
> > Thanks Arvin. After I looked at your samples I decided to try and simplify
> > my
> > database amalgamating the three rank tables into one combined table with
> > the
> > following structure...
> >
> > TblRank
> > RankID Autonum PK
> > ServiceID FK
> > Rank Text
> >
> > TblService
> > ServiceID PK
> > Service Text
> >
> > CboService has RowSource = TblService
> > Bound Col = 1
> > Col Count = 2
> > Col Width = 0cm; 2cm
> >
> > CboRank has a blank RowSource
> > Bound Col = 1
> > Col Count = 3
> > Col Width = 0cm;0cm;2.5cm
> >
> > I have changed the After Update for CboService to the following Code...
> >
> > Private Sub CboService_AfterUpdate()
> > With Me![CboRank]
> > If IsNull(Me!CboService) Then
> > .RowSource = ""
> > Else
> > .RowSource = "SELECT [Rank] " & _
> > "FROM tblRank " & _
> > "WHERE [ServiceID]= " & Me!CboService
> > End If
> > Call .Requery
> > End With
> > End Sub
> >
> > The trouble is that whereas I could previously get a population of CboRank
> > from a selection in CboService now I get a blank list on opening CboRank.
> > I'm not sure where I've gone wrong but your guidance would be appreciated.
> > --
> > Joe
> >
> >
> > "Arvin Meyer [MVP]" wrote:
> >
> >> It might be simpler than that. Have a look at my sample for listboxes,
> >> which
> >> works the very same as a combo box:
> >>
> >> http://accessmvp.com/Arvin/Combo.zip
> >> --
> >> Arvin Meyer, MCP, MVP
> >> http://www.datastrat.com
> >> http://www.accessmvp.com
> >> http://www.mvps.org/access
> >>
> >>
> >> "Joe" <(E-Mail Removed)> wrote in message
> >> news:9811C840-0529-4125-ABC1-(E-Mail Removed)...
> >> > Thanks Arvin - I think I see what you mean however I'm unsure how to
> >> > achieve
> >> > the solution to my problem. Of the three services available for
> >> > selection
> >> > in
> >> > the first combo, one selection (B) must also have a specific role
> >> > (function)
> >> > attached to some but not all of the ranks in combo2. I had hoped that
> >> > this
> >> > could be achieved using cascading combo boxes.
> >> > --
> >> > Joe
> >> >
> >> >
> >> > "Arvin Meyer [MVP]" wrote:
> >> >
> >> >> I think because none of the Cases are matching for cboRank, and since
> >> >> all
> >> >> of
> >> >> the values sought are TblFunction anyway, why would you even want a
> >> >> Case
> >> >> statement?
> >> >> --
> >> >> Arvin Meyer, MCP, MVP
> >> >> http://www.datastrat.com
> >> >> http://www.accessmvp.com
> >> >> http://www.mvps.org/access
> >> >>
> >> >>
> >> >> "Joe" <(E-Mail Removed)> wrote in message
> >> >> newsE80DA07-59FF-4822-A2EC-(E-Mail Removed)...
> >> >> >I have 3 dependent combo boxes.
> >> >> >
> >> >> > CboService has TblService as RowSource with bound col = 2; col count
> >> >> > =
> >> >> > 2;
> >> >> > col width 0cm;2cm and the following code in after update...
> >> >> > Private Sub CboService_AfterUpdate()
> >> >> > On Error Resume Next
> >> >> > Select Case CboService.Value
> >> >> > Case "A"
> >> >> > CboRank.RowSource = "TblA"
> >> >> > Case "B"
> >> >> > CboRank.RowSource = "TblB"
> >> >> > Case "C"
> >> >> > CboRank.RowSource = "TblC"
> >> >> > End Select
> >> >> > End Sub
> >> >> >
> >> >> > CboRank has bound col = 1; col count = 2; col width 0cm;2.5cm and
> >> >> > the
> >> >> > following code in the after update event...
> >> >> > Private Sub CboRank_AfterUpdate()
> >> >> > On Error Resume Next
> >> >> > Select Case CboRank.Value
> >> >> > Case "B1"
> >> >> > CboFunction.RowSource = "TblFunction"
> >> >> > Case "B3"
> >> >> > CboFunction.RowSource = "TblFunction"
> >> >> > Case "B7"
> >> >> > CboFunction.RowSource = "TblFunction"
> >> >> > End Select
> >> >> > End Sub
> >> >> > CboFunction has bound col = 1; col count = 3; col width 0cm; 2.5cm;
> >> >> > 0cm
> >> >> >
> >> >> > Combos 1&2 populate without any problem however I just can't seem to
> >> >> > be
> >> >> > able
> >> >> > to get combo 3 to populate.
> >> >> >
> >> >> > I would be very grateful for any help and if there is a better way
> >> >> > to
> >> >> > write
> >> >> > the above code I would appreciate the advice.
> >> >> >
> >> >> > Many thanks,
> >> >> >
> >> >> > Joe
> >> >> > --
> >> >> > Joe
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>