ApplyFilter method of DoCmd object in OnOpen event property questi

[Jeff Conrad]

jeff,

i would like to enhance the user's ability to use this to include being able
to open to a record in the underlying table which matches a joint criterion

' Apply a filter to match chosen Patient Number
[Forms]![Command and Control Center]![SelectPatient] And [Cycle_] = 0

[note the interest in using just those records having a "Cycle_" value of
zero.]
it's giving us a error 13 "Type Mismatch" though.??

Try:

' Apply a filter to match chosen Patient Number and Cycle = 0
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And [Cycle] = 0"

 

[Guest]

things have gotten a little crazier jeff
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) Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' 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
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And
Cycle = " & 0
' 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

was what i came up with after (quite) a few permutations on the idea and it
seems to work; but i can substitute yours which somehow looks more 'right'.
although,

my user has come up with a new twist, which i think you'll agree is kind of
an interesting programming problem....

let's say we add another combobox on the main switchboard we'll call
'SelectCycle' and its values can go from 0,1,2,3,4,.....,199,200 w/ the
restriction there be no off menu choices available. let's also say there are
three buttons on the first page of the switchboard (there are more that don't
concern us) labelled 'Baseline', 'Treatment Cycles' and 'Follow Up'. clicking
each takes us to a switchboard having around 5 buttons each of which opens a
form, e.g. 'Diagnostic Tests', 'Physical Exam' and so forth. the form in each
case is the same whether it's opened w/in the Baseline, Treatment Cycles or
'Follow Up' switchboard, but what we're aiming to do is permit the user to
open the Baseline ones only when he chooses zero from the SelectCycle
combobox -- having done so, he will be taken to those having zero in the
Cycle control. when he chooses a value between 1 and 99, he will be permitted
to use the 'Treatment Cycle' commands which will open the very same forms as
on the other switchboard(s), but take him to the Cyclle number he's chosen.
lastly, should he choose a value between 100 and 200 then he will be allowed
to use the buttons on the 'Follow Up' switchboard which again open to the
same forms as the other switchboards. of course, he will have to have chosen
a patient number as well from the command and control center.

i guess i could clone each form twice and have a trio with slightly
different names so that there would be a way to differentiate amongst those
accessible from each of the three switchboards and therefore drop customized
variations of the filter code appropriate to the working restraints. i don't
know what that would do to the overall performance of the application (it's
already slower than my 4 cylinder five year old ford), but i thought i'd try
this mad idea out on another access junkie. namely, would there be some
klever way to instruct a2k that the form was being accessed from a particular
switchboard? if so, then i reason that that fact could be factored into a vba
code that much like the one you already rigged would tell the user that they
could not access a form from that switchboard since they had entered a value
that was inappropriate (i.e. being on the Baseline switchboard and at the
same time having entered a value other than zero in the SelectCycle would be
a 'no no'). hope i'm making sense.

-ted

jeff,

i would like to enhance the user's ability to use this to include being able
to open to a record in the underlying table which matches a joint criterion

' Apply a filter to match chosen Patient Number
[Forms]![Command and Control Center]![SelectPatient] And [Cycle_] = 0

[note the interest in using just those records having a "Cycle_" value of
zero.]
it's giving us a error 13 "Type Mismatch" though.??

Try:

' Apply a filter to match chosen Patient Number and Cycle = 0
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient] & " And [Cycle] = 0"

 

[Jeff Conrad]

in message

Hi Ted,

things have gotten a little crazier jeff

That doesn't sound good.

was what i came up with after (quite) a few permutations on the idea and it
seems to work; but i can substitute yours which somehow looks more 'right'.
although,

I would subsitiute the code I provided.

my user has come up with a new twist, which i think you'll agree is kind of
an interesting programming problem....

Interesting??
Yeah right.....

let's say we add another combobox on the main switchboard we'll call
'SelectCycle' and its values can go from 0,1,2,3,4,.....,199,200 w/ the
restriction there be no off menu choices available. let's also say there are
three buttons on the first page of the switchboard (there are more that don't
concern us) labelled 'Baseline', 'Treatment Cycles' and 'Follow Up'. clicking
each takes us to a switchboard having around 5 buttons each of which opens a
form, e.g. 'Diagnostic Tests', 'Physical Exam' and so forth. the form in each
case is the same whether it's opened w/in the Baseline, Treatment Cycles or
'Follow Up' switchboard, but what we're aiming to do is permit the user to
open the Baseline ones only when he chooses zero from the SelectCycle
combobox -- having done so, he will be taken to those having zero in the
Cycle control. when he chooses a value between 1 and 99, he will be permitted
to use the 'Treatment Cycle' commands which will open the very same forms as
on the other switchboard(s), but take him to the Cyclle number he's chosen.
lastly, should he choose a value between 100 and 200 then he will be allowed
to use the buttons on the 'Follow Up' switchboard which again open to the
same forms as the other switchboards. of course, he will have to have chosen
a patient number as well from the command and control center.

i guess i could clone each form twice and have a trio with slightly
different names so that there would be a way to differentiate amongst those
accessible from each of the three switchboards and therefore drop customized
variations of the filter code appropriate to the working restraints. i don't
know what that would do to the overall performance of the application (it's
already slower than my 4 cylinder five year old ford), but i thought i'd try
this mad idea out on another access junkie. namely, would there be some
klever way to instruct a2k that the form was being accessed from a particular
switchboard? if so, then i reason that that fact could be factored into a vba
code that much like the one you already rigged would tell the user that they
could not access a form from that switchboard since they had entered a value
that was inappropriate (i.e. being on the Baseline switchboard and at the
same time having entered a value other than zero in the SelectCycle would be
a 'no no'). hope i'm making sense.

Well I'm not afraid to admit you lost me big time in all that Ted.

One strong word of advice that you should seriously consider:
"You have outlasted the usefullness of the Switchboard Manager."
Trust me.

The SBM is great for simple things, but this is a *classic* example
of when it now becomes a hinderance. All of your forms are opened
via the SBM form with a generic DoCmd.OpenForm command. This
SEVERELY limits what you can do when opening forms. Unless you
want to do some MAJOR re-coding of the SBM form, that is what
you are stuck with. The advantage of having your own unbound main
form is that you can have a command button to open a specific form,
but provide an almost limitless amount of coding *before* you even
open the form. You can check things, verify entries, and even pass
information from the main form to the opening form. This would make
things SO much easier for what you are trying to do.

Having said the above, you can still use your current setup, it will just
require you to do a lot of coding to make this work.

Keep in mind I'm still very unclear about what *exactly* you want to
do, but here is something that might steer you in the right direction.

1. Open your Command And Control Center form in Design View.
This should be the form that the SBM created. Correct?

2. Click View on the Toolbar and then select Field List. This will
bring up a list of fields from the Switchboard Items table. Click on
the SwitchboardID field and drag it onto your form somewhere.
You can delete the associated label it will create. This new text box
will be bound to the SwitchboardID field and Access will name it
SwithboardID. That is fine, just leave it.

3. Now right click on that new text box, go to Properties and change
the visible property to No (on the Format or All tabs). This way the
text box will not show when the form is in Normal View.

4. Save and close the form.

5. You can now reference this text box in the Open event of your
other forms to determine *what* switchboard menu you are on.

Something like so:

Private Sub Form_Open(Cancel As Integer)
Select Case Forms![Command And Control Center]![SwitchboardID]
Case 1
' Most likely the main page
' Do something according to this switchboard page
Case 2
' A different page
' Do something according to this switchboard page
...etc....
Case Else
' Nothing here just ignore
' We should not get to this area of the code
End Select
End Sub

Hopefully you should be able to integrate something like the above with
your exisiting code.

 

[Guest]

hey jeff,

y'know after i wrote up my task objective i kind of got thinking about
having seen a reference to the s'board's source table so i looked it up and
found what i thought to be a pattern -- i noted the way one row mapped to the
others so i kind of started to wonder if it'd be possible to create an
nonvisible textbox control having the value of the appropriate parameter upon
the s'board itself and then create filters that'd be able to read the value
of the page being used when testing for the legitimacy of the user's request.
i have to admit i know how to use a select case statement when i see it but
don't usually get around to using it in code i need to write. you're dead
right, something like your sketched out code could be integrated into this.

once again, you guys are GREAT!!

-ted

in message

Hi Ted,

things have gotten a little crazier jeff

That doesn't sound good.

was what i came up with after (quite) a few permutations on the idea and it
seems to work; but i can substitute yours which somehow looks more 'right'.
although,

I would subsitiute the code I provided.

my user has come up with a new twist, which i think you'll agree is kind of
an interesting programming problem....

Interesting??
Yeah right.....

let's say we add another combobox on the main switchboard we'll call
'SelectCycle' and its values can go from 0,1,2,3,4,.....,199,200 w/ the
restriction there be no off menu choices available. let's also say there are
three buttons on the first page of the switchboard (there are more that don't
concern us) labelled 'Baseline', 'Treatment Cycles' and 'Follow Up'. clicking
each takes us to a switchboard having around 5 buttons each of which opens a
form, e.g. 'Diagnostic Tests', 'Physical Exam' and so forth. the form in each
case is the same whether it's opened w/in the Baseline, Treatment Cycles or
'Follow Up' switchboard, but what we're aiming to do is permit the user to
open the Baseline ones only when he chooses zero from the SelectCycle
combobox -- having done so, he will be taken to those having zero in the
Cycle control. when he chooses a value between 1 and 99, he will be permitted
to use the 'Treatment Cycle' commands which will open the very same forms as
on the other switchboard(s), but take him to the Cyclle number he's chosen.
lastly, should he choose a value between 100 and 200 then he will be allowed
to use the buttons on the 'Follow Up' switchboard which again open to the
same forms as the other switchboards. of course, he will have to have chosen
a patient number as well from the command and control center.

i guess i could clone each form twice and have a trio with slightly
different names so that there would be a way to differentiate amongst those
accessible from each of the three switchboards and therefore drop customized
variations of the filter code appropriate to the working restraints. i don't
know what that would do to the overall performance of the application (it's
already slower than my 4 cylinder five year old ford), but i thought i'd try
this mad idea out on another access junkie. namely, would there be some
klever way to instruct a2k that the form was being accessed from a particular
switchboard? if so, then i reason that that fact could be factored into a vba
code that much like the one you already rigged would tell the user that they
could not access a form from that switchboard since they had entered a value
that was inappropriate (i.e. being on the Baseline switchboard and at the
same time having entered a value other than zero in the SelectCycle would be
a 'no no'). hope i'm making sense.

Well I'm not afraid to admit you lost me big time in all that Ted.

One strong word of advice that you should seriously consider:
"You have outlasted the usefullness of the Switchboard Manager."
Trust me.

The SBM is great for simple things, but this is a *classic* example
of when it now becomes a hinderance. All of your forms are opened
via the SBM form with a generic DoCmd.OpenForm command. This
SEVERELY limits what you can do when opening forms. Unless you
want to do some MAJOR re-coding of the SBM form, that is what
you are stuck with. The advantage of having your own unbound main
form is that you can have a command button to open a specific form,
but provide an almost limitless amount of coding *before* you even
open the form. You can check things, verify entries, and even pass
information from the main form to the opening form. This would make
things SO much easier for what you are trying to do.

Having said the above, you can still use your current setup, it will just
require you to do a lot of coding to make this work.

Keep in mind I'm still very unclear about what *exactly* you want to
do, but here is something that might steer you in the right direction.

1. Open your Command And Control Center form in Design View.
This should be the form that the SBM created. Correct?

2. Click View on the Toolbar and then select Field List. This will
bring up a list of fields from the Switchboard Items table. Click on
the SwitchboardID field and drag it onto your form somewhere.
You can delete the associated label it will create. This new text box
will be bound to the SwitchboardID field and Access will name it
SwithboardID. That is fine, just leave it.

3. Now right click on that new text box, go to Properties and change
the visible property to No (on the Format or All tabs). This way the
text box will not show when the form is in Normal View.

4. Save and close the form.

5. You can now reference this text box in the Open event of your
other forms to determine *what* switchboard menu you are on.

Something like so:

Private Sub Form_Open(Cancel As Integer)
Select Case Forms![Command And Control Center]![SwitchboardID]
Case 1
' Most likely the main page
' Do something according to this switchboard page
Case 2
' A different page
' Do something according to this switchboard page
...etc....
Case Else
' Nothing here just ignore
' We should not get to this area of the code
End Select
End Sub

Hopefully you should be able to integrate something like the above with
your exisiting code.

 

[Jeff Conrad]

in message

hey jeff,

y'know after i wrote up my task objective i kind of got thinking about
having seen a reference to the s'board's source table so i looked it up and
found what i thought to be a pattern -- i noted the way one row mapped to the
others so i kind of started to wonder if it'd be possible to create an
nonvisible textbox control having the value of the appropriate parameter upon
the s'board itself and then create filters that'd be able to read the value
of the page being used when testing for the legitimacy of the user's request.
i have to admit i know how to use a select case statement when i see it but
don't usually get around to using it in code i need to write. you're dead
right, something like your sketched out code could be integrated into this.

Yep, give it a shot. You should be able to piece together the various code
bits we came up with to do all of your tests.

once again, you guys are GREAT!!

Thanks for the kind words.
You're very welcome.

 

[Guest]

jeff,

i hate to keep returning to this but after some hacking around with your code
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"

this code is meant to be used on the same form; the form can be accessed
from w/in a 'Baseline', 'Treatments' or 'Follow Up' switchboard page.

i want the user to be forced to enter both the patient number AND cycle
number from the main s'board page. also, when the inappropriate value of the
cycle number (given the switchboard page the form's being opened from) is
entered in the main s'board page, i want the user to be prompted to enter the
correct one.

can you see where the fatal flaw in my logic lies here?

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
Resume ExitPoint
End If

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

MsgBox "Nothing here just ignore - we should never get to this
switchboard page"

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

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

 

[Jeff Conrad]

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 ******

End If

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.

 

[Guest]

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,

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 ******

End If

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.

 

[Jeff Conrad]

Hi Ted,

jeff, i do have you on the speed dial.....everytime i click the 'does this
answer your question' button

<g>
Thanks.
You could also try 1-800-Ask-Jeff

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.
Ok.

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.

Well here is where it will get really confusing for me as you are now
bringing in many elements of the database which I cannot obviously see.
I will have to make some guesses here.

Are you seeing a completely blank form with no controls in the Detail
section? If so, this will occur when a form's record source does not
return any records and the form's Allow Addition's property is set to "No."
Sine no records can be added Access will show a completely blank form.
Is that what you are experiencing? What happens if you change Allow
Additions to "Yes?"

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

<code snipped>

Well again, I can only speculate, but something jumped out at me looking over
the code. In one spot you have this:

' 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

but in another spot you have this:

' 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

You have SwitchboardID 22 in one spot and 24 in another.
Was that by mistake??