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 |
#1
|
|||
|
|||
Cascading Combo Box - help needed please.
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 |
#2
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message news 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 |
#3
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message news 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 . |
#4
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message ... 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" wrote in message news 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 . |
#5
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message ... 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" wrote in message news 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 . . |
#6
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message ... 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" wrote in message ... 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" wrote in message news 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 . . |
#7
|
|||
|
|||
Cascading Combo Box - help needed please.
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" wrote in message ... 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" wrote in message ... 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" wrote in message news 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 . . . |
Thread Tools | |
Display Modes | |
|
|