jeff, i do have you on the speed dial.....everytime i click the 'does this
answer your question' button
i re-wrote your code just a little because i was having a problem with the
part(s) that do the testing of the appropriateness of the Cycle values w/in
each SwitchboardID.
i guess the big design issue comes from the fact that from time to time
there may not exist a record which has the combination of Patient Number and
Cycle numbers the user selects.
what i notice is that the behavior when this seems to be so is not uniform
throughout the three possible SwitchboardIDs
looking at patient number 011126976 who has records w/ cycle 0, 2, 3
i don't have a problem w/ the baseline part. here's what happens when i
select a cycle of 1 (which is theoretically plausible) w/in the treatment
menu. i get a blank screen of this form's.
when i enter a cycle of 100 from the follow-up menu, i don't get a blank
screen... what does happen is that i get to see all twenty nine records in
the underlying 'Diagnostics' table behind the 'Diagnostics' form!!!
i can sort of understand the behavior of cycle 1's -- we would know to enter
a Patient Number and Cycle in the Diagnostic form because it does not exist.
however, i can't understand why i get what happens from the follow up menu
screen. since that combination of Patient Number and Cycle doesn't exist
either, why don't i just see a blank form??
here's the code
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 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 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 Then
MsgBox "You need to enter a Cycle greater 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] = 24 _
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
-ted
Jeff Conrad said:
jeff,
i hate to keep returning to this but after some hacking around with your code
Do you have me on speed dial yet Ted?
i came up with the following solution which is resulting in an a ms a2k
message viz,
"An unanticipated error has occurred. The error number is 20 and the
description is 'Resume without error'. Please contact your System
Administrator"
The error is occurring because of this line Ted:
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/or Cycle from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient/Cycle?"
' Stop the form from opening
Cancel = True
****** Resume ExitPoint ******
That should be GoTo ExitPoint
You only use the Resume statement if an error has occurred, and
in this case no error has occurred so Access coughs up a hairball.
Make that one change and all should be OK or try this revised code
that will not require a need for that line.
This is untested, but should be OK I believe:
***********Start Of Code***************
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)
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/or 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 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] <= 1 Or _
[Forms]![Command and Control Center]![SelectCycle] >= 99 Then
MsgBox "You need to enter a Cycle between 1 and 100 " _
& "to access this form."
Cancel = True
End If
' This is the Follow Up Case
Case 22
If [Forms]![Command and Control Center]![SelectCycle] < 100 Then
MsgBox "You need to enter a Cycle greater 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 _
Or [Forms]![Command and Control Center]![SwitchboardID] = 24 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
***********End Of Code***************
That should do it.