Select Case fails to find record

G

Guest

hi,

in this morass of code below, the Case 2 scenario fails to find a test
record in the underlying table where the values of "Patient Number" is
1097977 and (importantly) the value of "SelectCycle" is 999??!!

i don't get that. anyone have a clue?



Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Or IsNull(Forms![Command and Control Center]!SelectCycle) Then
' No Patient ID or Cycle entered on main form
MsgBox "Please select a Patient Number AND Cycle from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient/Cycle?"
' Stop the form from opening
Cancel = True
Else
Select Case Forms![Command and Control Center]![SwitchboardID]
' This is the Baseline case
Case 2
If [Forms]![Command and Control Center]![SelectCycle] <> 0 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of 0 to access this form."
Cancel = True
End If
' This is the Treatment Case
Case 21
If [Forms]![Command and Control Center]![SelectCycle] = 0 Or _
[Forms]![Command and Control Center]![SelectCycle] > 99 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle between 1 and 99 " _
& "to access this form."
Cancel = True
End If
' This is the Follow Up Case
Case 22
If [Forms]![Command and Control Center]![SelectCycle] < 100 Or _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of at least " _
& "100 to access this form."
Cancel = True
End If
Case Else
' Nothing here just ignore
End Select

' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
' Cycle to 0 the basline case
If [Forms]![Command and Control Center]![SwitchboardID] = 2 Then
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And
[Cycle] = 0"
' The treatment (21) or follow up (24) case
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 21 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 22 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
End If

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub
 
G

Guest

i just thought i'd do a little more empirical testing and chronicle the
outcome(s) i get under all possible conditions, so keeping Patient Number
constant and varying SelectCycle

when i look for and the record has i find a record
0 0 with 0
999 0 with 0
0 999 nope -- i don't find a
record
999 999 nope -- i don't find a
record

what i'm trying to do is restrict the values usable from this switchboard
page (Case 2) to just 0 (zero) and 999.

still working on it....

Ted said:
hi,

in this morass of code below, the Case 2 scenario fails to find a test
record in the underlying table where the values of "Patient Number" is
1097977 and (importantly) the value of "SelectCycle" is 999??!!

i don't get that. anyone have a clue?



Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Or IsNull(Forms![Command and Control Center]!SelectCycle) Then
' No Patient ID or Cycle entered on main form
MsgBox "Please select a Patient Number AND Cycle from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient/Cycle?"
' Stop the form from opening
Cancel = True
Else
Select Case Forms![Command and Control Center]![SwitchboardID]
' This is the Baseline case
Case 2
If [Forms]![Command and Control Center]![SelectCycle] <> 0 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of 0 to access this form."
Cancel = True
End If
' This is the Treatment Case
Case 21
If [Forms]![Command and Control Center]![SelectCycle] = 0 Or _
[Forms]![Command and Control Center]![SelectCycle] > 99 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle between 1 and 99 " _
& "to access this form."
Cancel = True
End If
' This is the Follow Up Case
Case 22
If [Forms]![Command and Control Center]![SelectCycle] < 100 Or _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of at least " _
& "100 to access this form."
Cancel = True
End If
Case Else
' Nothing here just ignore
End Select

' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
' Cycle to 0 the basline case
If [Forms]![Command and Control Center]![SwitchboardID] = 2 Then
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And
[Cycle] = 0"
' The treatment (21) or follow up (24) case
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 21 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 22 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
End If

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub
 
G

Guest

sorry guys, i shoulda looked a little further down where i would've found the
opportunity for a small re-code.



Ted said:
hi,

in this morass of code below, the Case 2 scenario fails to find a test
record in the underlying table where the values of "Patient Number" is
1097977 and (importantly) the value of "SelectCycle" is 999??!!

i don't get that. anyone have a clue?



Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Or IsNull(Forms![Command and Control Center]!SelectCycle) Then
' No Patient ID or Cycle entered on main form
MsgBox "Please select a Patient Number AND Cycle from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient/Cycle?"
' Stop the form from opening
Cancel = True
Else
Select Case Forms![Command and Control Center]![SwitchboardID]
' This is the Baseline case
Case 2
If [Forms]![Command and Control Center]![SelectCycle] <> 0 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of 0 to access this form."
Cancel = True
End If
' This is the Treatment Case
Case 21
If [Forms]![Command and Control Center]![SelectCycle] = 0 Or _
[Forms]![Command and Control Center]![SelectCycle] > 99 And _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle between 1 and 99 " _
& "to access this form."
Cancel = True
End If
' This is the Follow Up Case
Case 22
If [Forms]![Command and Control Center]![SelectCycle] < 100 Or _
[Forms]![Command and Control Center]![SelectCycle] <> 999 Then
MsgBox "You need to enter a Cycle of at least " _
& "100 to access this form."
Cancel = True
End If
Case Else
' Nothing here just ignore
End Select

' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
' Cycle to 0 the basline case
If [Forms]![Command and Control Center]![SwitchboardID] = 2 Then
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And
[Cycle] = 0"
' The treatment (21) or follow up (24) case
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 21 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
ElseIf [Forms]![Command and Control Center]![SwitchboardID] = 22 _
Then DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & _
" And [Cycle] = " & [Forms]![Command and Control
Center]![SelectCycle]
End If

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub
 

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

Top