If Statement Problem

D

DS

I have an If Statement that is giving me problems.
I have two kinds of Groups.
Group and SubGroup
The Group has different levels 1,2,3,etc.
The Subgroups are attached to the groupa and they also have different
levels 1,2,3,etc.
What is suppose to happen is that the Group comes up, Level 1.
This works fine.
Then the If statement is suppose to look for any SubGroups, if any,
bring that SubGroups up Level 1 then
Look for any SubGroups Level 2 then
SubGroups Level 3, etc. Once there are no more SubGroups it then looks
for Groups now Level 2.

This all almost works fine. The problem I have is that I can't get the
Second SubGroup to come up. It all works fine unless I have a 2nd
SubGroup. Any help Appreciated.
Thanks
DS

Me.TxtSubLevel = Me.TxtSubLevel + 1
'CHECK FOR SUBGROUPS
Me.TxtForceCount = DCount("[SubGroupAction]", "InfoSubGroup",
"TerminalID =" & Forms!OrderScreen!TxtOSStation & " " & _
"And DayID = " & Forms!OrderScreen!TxtOSDay & "And MenuID =" &
Forms!OrderScreen!TxtOSMenu & " And MenuCatID = " &
Forms!OrderScreen!TxtOSSection & " " & _
"And ItemID = " & Forms!OrderScreen!TxtOSItem & "And GroupID= " &
Me.TxtGroup & "And ModID = " & Me.TxtMod & "And SubGroupLevel = " &
Me.TxtSubLevel & "")

If Me.TxtForceCount >= 1 Then
Me.TxtSub = 3
With Forms!OrderScreenForce!ListMods
..RowSource = "SELECT InfoSubGroup.TerminalID, InfoSubGroup.DayID, " & _
"InfoSubGroup.MenuID, InfoSubGroup.PriceID, InfoSubGroup.MenuCatID, " & _
"InfoSubGroup.ItemID, InfoSubGroup.GroupID, InfoSubGroup.ModID, " & _
"InfoSubMod.SubModID, InfoSubGroup.SubGroupID, InfoSubMod.SubModPrinter,
" & _
"InfoSubMod.SubModPrep, InfoSubMod.SubModOrder, Items.ItemName, " & _
"InfoSubGroup.SubGroupLevel, InfoSubGroup.SubGroupAction " & _
"FROM (InfoSubGroup INNER JOIN InfoSubMod ON InfoSubGroup.SubGroupID =
InfoSubMod.SubGroupID) " & _
"INNER JOIN Items ON InfoSubMod.SubModID = Items.ItemID " & _
"GROUP BY InfoSubGroup.TerminalID, InfoSubGroup.DayID,
InfoSubGroup.MenuID, " & _
"InfoSubGroup.PriceID, InfoSubGroup.MenuCatID, InfoSubGroup.ItemID, " & _
"InfoSubGroup.GroupID, InfoSubGroup.ModID, InfoSubMod.SubModID, " & _
"InfoSubGroup.SubGroupID, InfoSubMod.SubModPrinter, " & _
"InfoSubMod.SubModPrep, InfoSubMod.SubModOrder, Items.ItemName, " & _
"InfoSubGroup.SubGroupLevel, InfoSubGroup.SubGroupAction " & _
"HAVING (((InfoSubGroup.TerminalID) =
(Forms]![OrderScreen]![TxtOSStation]) " & _
"And ((InfoSubGroup.DayID) = [Forms]![OrderScreen]![TxtOSDay]) " & _
"And ((InfoSubGroup.MenuID) = [Forms]![OrderScreen]![TxtOSMenu]) " & _
"And ((InfoSubGroup.MenuCatID) = [Forms]![OrderScreen]![TxtOSSection]) " & _
"And ((InfoSubGroup.ItemID) = [Forms]![OrderScreen]![TxtOSItem]) " & _
"And ((InfoSubGroup.GroupID) = [Forms]![OrderScreenForce]![TxtGroup]) " & _
"And ((InfoSubGroup.ModID) = [Forms]![OrderScreenForce]![TxtMod]) " & _
"And ((InfoSubGroup.SubGroupLevel) =
[Forms]![OrderScreenForce]![TxtSubLevel]) " & _
"And ((InfoSubGroup.SubGroupAction) = ""F"")) " & _
"ORDER BY Items.ItemName;"
..ColumnCount = 16
..ColumnWidths = "0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0
in;0 in;0 in;2 in;0 in;0 in"
..Requery
End With
ElseIf Me.TxtForceCount = 0 Then
Me.TxtLevel = Me.TxtLevel + 1
Me.TxtForceCount = DCount("[GroupAction]", "InfoGroup", "TerminalID =" &
Forms!OrderScreen!TxtOSStation & " " & _
"And DayID = " & Forms!OrderScreen!TxtOSDay & "And MenuID =" &
Forms!OrderScreen!TxtOSMenu & " And MenuCatID = " &
Forms!OrderScreen!TxtOSSection & " " & _
"And ItemID = " & Forms!OrderScreen!TxtOSItem & "And GroupLevel = " &
Me.TxtLevel & "And GroupAction = ""F""")

If Me.TxtForceCount >= 1 Then
Me.TxtSub = 2
Me.TxtSubLevel = Me.TxtSubLevel - 1
With Forms!OrderScreenForce!ListMods
..RowSource = "SELECT InfoMod.TerminalID, InfoMod.DayID, InfoMod.MenuID,
"& _
"InfoMod.MenuCatID, InfoMod.ItemID, InfoMod.GroupID,
InfoGroup.GroupLevel, " & _
"InfoGroup.GroupAction, InfoMod.ModID, Items.ItemName,
InfoMod.ModPrinter, " & _
"InfoMod.ModPrep, InfoMod.ModOrder " & _
"FROM InfoGroup INNER JOIN (InfoMod INNER JOIN Items ON InfoMod.ModID =
Items.ItemID) " & _
"ON InfoGroup.GroupID = InfoMod.GroupID " & _
"GROUP BY InfoMod.TerminalID, InfoMod.DayID, InfoMod.MenuID, " & _
"InfoMod.MenuCatID, InfoMod.ItemID, InfoMod.GroupID, " & _
"InfoGroup.GroupLevel, InfoGroup.GroupAction, InfoMod.ModID, " & _
"Items.ItemName, InfoMod.ModPrinter, InfoMod.ModPrep, InfoMod.ModOrder " & _
"HAVING (((InfoMod.TerminalID) = [Forms]![OrderScreen]![TxtOSStation]) " & _
"And ((InfoMod.DayID) = [Forms]![OrderScreen]![TxtOSDay]) " & _
"And ((InfoMod.MenuID) = [Forms]![OrderScreen]![TxtOSMenu]) " & _
"And ((InfoMod.MenuCatID) = [Forms]![OrderScreen]![TxtOSSection]) " & _
"And ((InfoMod.ItemID) = [Forms]![OrderScreen]![TxtOSItem]) " & _
"And ((InfoGroup.GroupLevel) = [Forms]![OrderScreenForce]![TxtLevel]) " & _
"And ((InfoGroup.GroupAction) = ""F"")) " & _
"ORDER BY Items.ItemName;"
..ColumnCount = 13
..ColumnWidths = "0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;2 in;0
in;0 in;0 in"
..Requery
End With
ElseIf Me.TxtForceCount = 0 Then
DoCmd.Close acForm, "OrderScreenForce"
End If
End If
 
D

DS

DS said:
I have an If Statement that is giving me problems.
I have two kinds of Groups.
Group and SubGroup
The Group has different levels 1,2,3,etc.
The Subgroups are attached to the groupa and they also have different
levels 1,2,3,etc.
What is suppose to happen is that the Group comes up, Level 1.
This works fine.
Then the If statement is suppose to look for any SubGroups, if any,
bring that SubGroups up Level 1 then
Look for any SubGroups Level 2 then
SubGroups Level 3, etc. Once there are no more SubGroups it then looks
for Groups now Level 2.

This all almost works fine. The problem I have is that I can't get the
Second SubGroup to come up. It all works fine unless I have a 2nd
SubGroup. Any help Appreciated.
Thanks
DS

Me.TxtSubLevel = Me.TxtSubLevel + 1
'CHECK FOR SUBGROUPS
Me.TxtForceCount = DCount("[SubGroupAction]", "InfoSubGroup",
"TerminalID =" & Forms!OrderScreen!TxtOSStation & " " & _
"And DayID = " & Forms!OrderScreen!TxtOSDay & "And MenuID =" &
Forms!OrderScreen!TxtOSMenu & " And MenuCatID = " &
Forms!OrderScreen!TxtOSSection & " " & _
"And ItemID = " & Forms!OrderScreen!TxtOSItem & "And GroupID= " &
Me.TxtGroup & "And ModID = " & Me.TxtMod & "And SubGroupLevel = " &
Me.TxtSubLevel & "")

If Me.TxtForceCount >= 1 Then
Me.TxtSub = 3
With Forms!OrderScreenForce!ListMods
.RowSource = "SELECT InfoSubGroup.TerminalID,
InfoSubGroup.DayID, " & _
"InfoSubGroup.MenuID, InfoSubGroup.PriceID, InfoSubGroup.MenuCatID, " & _
"InfoSubGroup.ItemID, InfoSubGroup.GroupID, InfoSubGroup.ModID, " & _
"InfoSubMod.SubModID, InfoSubGroup.SubGroupID, InfoSubMod.SubModPrinter,
" & _
"InfoSubMod.SubModPrep, InfoSubMod.SubModOrder, Items.ItemName, " & _
"InfoSubGroup.SubGroupLevel, InfoSubGroup.SubGroupAction " & _
"FROM (InfoSubGroup INNER JOIN InfoSubMod ON InfoSubGroup.SubGroupID =
InfoSubMod.SubGroupID) " & _
"INNER JOIN Items ON InfoSubMod.SubModID = Items.ItemID " & _
"GROUP BY InfoSubGroup.TerminalID, InfoSubGroup.DayID,
InfoSubGroup.MenuID, " & _
"InfoSubGroup.PriceID, InfoSubGroup.MenuCatID, InfoSubGroup.ItemID, " & _
"InfoSubGroup.GroupID, InfoSubGroup.ModID, InfoSubMod.SubModID, " & _
"InfoSubGroup.SubGroupID, InfoSubMod.SubModPrinter, " & _
"InfoSubMod.SubModPrep, InfoSubMod.SubModOrder, Items.ItemName, " & _
"InfoSubGroup.SubGroupLevel, InfoSubGroup.SubGroupAction " & _
"HAVING (((InfoSubGroup.TerminalID) =
(Forms]![OrderScreen]![TxtOSStation]) " & _
"And ((InfoSubGroup.DayID) = [Forms]![OrderScreen]![TxtOSDay]) " & _
"And ((InfoSubGroup.MenuID) = [Forms]![OrderScreen]![TxtOSMenu]) " & _
"And ((InfoSubGroup.MenuCatID) = [Forms]![OrderScreen]![TxtOSSection]) "
& _
"And ((InfoSubGroup.ItemID) = [Forms]![OrderScreen]![TxtOSItem]) " & _
"And ((InfoSubGroup.GroupID) = [Forms]![OrderScreenForce]![TxtGroup]) " & _
"And ((InfoSubGroup.ModID) = [Forms]![OrderScreenForce]![TxtMod]) " & _
"And ((InfoSubGroup.SubGroupLevel) =
[Forms]![OrderScreenForce]![TxtSubLevel]) " & _
"And ((InfoSubGroup.SubGroupAction) = ""F"")) " & _
"ORDER BY Items.ItemName;"
.ColumnCount = 16
.ColumnWidths = "0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0
in;0 in;0 in;2 in;0 in;0 in"
.Requery
End With
ElseIf Me.TxtForceCount = 0 Then
Me.TxtLevel = Me.TxtLevel + 1
Me.TxtForceCount = DCount("[GroupAction]", "InfoGroup", "TerminalID =" &
Forms!OrderScreen!TxtOSStation & " " & _
"And DayID = " & Forms!OrderScreen!TxtOSDay & "And MenuID =" &
Forms!OrderScreen!TxtOSMenu & " And MenuCatID = " &
Forms!OrderScreen!TxtOSSection & " " & _
"And ItemID = " & Forms!OrderScreen!TxtOSItem & "And GroupLevel = " &
Me.TxtLevel & "And GroupAction = ""F""")

If Me.TxtForceCount >= 1 Then
Me.TxtSub = 2
Me.TxtSubLevel = Me.TxtSubLevel - 1
With Forms!OrderScreenForce!ListMods
.RowSource = "SELECT InfoMod.TerminalID, InfoMod.DayID, InfoMod.MenuID,
"& _
"InfoMod.MenuCatID, InfoMod.ItemID, InfoMod.GroupID,
InfoGroup.GroupLevel, " & _
"InfoGroup.GroupAction, InfoMod.ModID, Items.ItemName,
InfoMod.ModPrinter, " & _
"InfoMod.ModPrep, InfoMod.ModOrder " & _
"FROM InfoGroup INNER JOIN (InfoMod INNER JOIN Items ON InfoMod.ModID =
Items.ItemID) " & _
"ON InfoGroup.GroupID = InfoMod.GroupID " & _
"GROUP BY InfoMod.TerminalID, InfoMod.DayID, InfoMod.MenuID, " & _
"InfoMod.MenuCatID, InfoMod.ItemID, InfoMod.GroupID, " & _
"InfoGroup.GroupLevel, InfoGroup.GroupAction, InfoMod.ModID, " & _
"Items.ItemName, InfoMod.ModPrinter, InfoMod.ModPrep, InfoMod.ModOrder "
& _
"HAVING (((InfoMod.TerminalID) = [Forms]![OrderScreen]![TxtOSStation]) "
& _
"And ((InfoMod.DayID) = [Forms]![OrderScreen]![TxtOSDay]) " & _
"And ((InfoMod.MenuID) = [Forms]![OrderScreen]![TxtOSMenu]) " & _
"And ((InfoMod.MenuCatID) = [Forms]![OrderScreen]![TxtOSSection]) " & _
"And ((InfoMod.ItemID) = [Forms]![OrderScreen]![TxtOSItem]) " & _
"And ((InfoGroup.GroupLevel) = [Forms]![OrderScreenForce]![TxtLevel]) " & _
"And ((InfoGroup.GroupAction) = ""F"")) " & _
"ORDER BY Items.ItemName;"
.ColumnCount = 13
.ColumnWidths = "0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;0 in;2 in;0
in;0 in;0 in"
.Requery
End With
ElseIf Me.TxtForceCount = 0 Then
DoCmd.Close acForm, "OrderScreenForce"
End If
End If
Click to expand...
Problem Solved. It was the DCount referencing the wrong fields. Thanks
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Rowsource 2
DLookUp Syntax 5
SQL Woes 4
INSERT Syntax Problem 2
Sum of SQL Statement 1
SQL Rowsource 1
Combo box display first item in list 4
SubQuery Problem 7

Top