Hi dear all, I have a Query now, and then I want to make the user to select the level of the data they want in my query, but if any data in the selection level is not available (which represent by "NA"), I want to write sone VBA code to push the selection to a higher level which do not contains "NA". Can someone help me to fix my code?
Here is part of my Query:
SmallestArea SmallerArea SmallArea TotalArea count
NA NA NA A1 5
NA NA B2 A2 27
D3 C3 B3 A3 28
NA NA B4 A4 19
NA NA B5 A5 24
. . . . .
. . . . .
. . . . .
And then I have a button that let user to choose which level of area they want. If user choose SmallArea with the "count" bigger then 25, my list box will return B2 and B3, but here is the problem: when user choose SmallArea with count smaller then 20, the list box will return "NA" and B4. but what I want is under this situation, a message box would pops up ("this level of area is not avaliable, the selection will use the upper level"), then all the data would automatically query by the selection of TotalArea
Is that possible to use Loop statment on this one? Here is my code:
'when user click the Area Zone selection, they can make the option to choose lower level area or not.
Private Sub Zone_slc_Click()
Dim QstAr As String
Dim stDocName As String
QstAr = MsgBox("Choose the SubArea", vbYesNo, "Message")
If QstAr = vbNo Then
DoCmd.CancelEvent
Else
Me.ArLevl.Visible = True 'Enable the SubArea option if choose "yes".
Me.ArLevl.Enabled = True
End If
Do While QsrAr = vbYes
stDocName = "LoopQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallArea] = "NA" Then
[Queries]![LoopQuery]![SmallArea] = [Queries]![LoopQuery]![TotalArea]
Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallerArea] = "NA" Then
[Queries]![LoopQuery]![SmallerArea] = [Queries]![LoopQuery]![SmallArea]
Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallestArea] = "NA" Then
[Queries]![LoopQuery]![SmallestArea] = [Queries]![LoopQuery]![SmallerArea]
Else: Exit Do
End If
End If
End If
Loop
End Sub
please help me to change and complete this piece of code, Thanks a lot !
Here is part of my Query:
SmallestArea SmallerArea SmallArea TotalArea count
NA NA NA A1 5
NA NA B2 A2 27
D3 C3 B3 A3 28
NA NA B4 A4 19
NA NA B5 A5 24
. . . . .
. . . . .
. . . . .
And then I have a button that let user to choose which level of area they want. If user choose SmallArea with the "count" bigger then 25, my list box will return B2 and B3, but here is the problem: when user choose SmallArea with count smaller then 20, the list box will return "NA" and B4. but what I want is under this situation, a message box would pops up ("this level of area is not avaliable, the selection will use the upper level"), then all the data would automatically query by the selection of TotalArea
Is that possible to use Loop statment on this one? Here is my code:
'when user click the Area Zone selection, they can make the option to choose lower level area or not.
Private Sub Zone_slc_Click()
Dim QstAr As String
Dim stDocName As String
QstAr = MsgBox("Choose the SubArea", vbYesNo, "Message")
If QstAr = vbNo Then
DoCmd.CancelEvent
Else
Me.ArLevl.Visible = True 'Enable the SubArea option if choose "yes".
Me.ArLevl.Enabled = True
End If
Do While QsrAr = vbYes
stDocName = "LoopQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallArea] = "NA" Then
[Queries]![LoopQuery]![SmallArea] = [Queries]![LoopQuery]![TotalArea]
Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallerArea] = "NA" Then
[Queries]![LoopQuery]![SmallerArea] = [Queries]![LoopQuery]![SmallArea]
Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallestArea] = "NA" Then
[Queries]![LoopQuery]![SmallestArea] = [Queries]![LoopQuery]![SmallerArea]
Else: Exit Do
End If
End If
End If
Loop
End Sub
please help me to change and complete this piece of code, Thanks a lot !
