Hi Ted, comments inline....
i pasted the batch into the OnEvent property and tried it out.....it does
seem to do what we wanted, i am happy to report
I assume you meant to say the Open event of the form??
Glad to hear the revised code is working.
i would really like to be on the receiving end of a 'fishing pole' vs. just
a 'fish' here, so to speak, so i'd like to try to understand what the guding
principle to getting it to work in the OnOpen event (and with the
pre-existing code) is. it'll make a better vba-er out of me, if you have the
time to go into it.
Ahhh the old, "teach a man to fish kinda thing, huh?"
Ok, Ted, put on yer waders, and grab yer gear, cause we're goin' fishin!
Private Sub Form_Open(Cancel As Integer)
Notice that the Open event in this line of code has a Cancel argument.
This means if we ever want to cancel the form from opening for some
reason we should use the Open event.
On Error GoTo Error_Handler
We use this line to tell Access to go to the Error_Handler part of the
code if we encounter any strange errors. As a good practice you should
ALWAYS have error handling in EVERY code procedure. Period.
If IsNull(Forms![Command and Control Center]!SelectPatient) Then
This line uses the built-in Access function called IsNull to test to see
if anything was entered was selected from the combo box on our main
form. Since we are testing this on a different form from the one running
this code, we must use the syntax of:
Forms!NameOfFormHere!ControlNameHere
If, however, this control was on this form we could have just used:
Me.ControlNameHere
As good programming practice I always believe you should avoid
spaces in names of objects, fields, controls, etc.
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
This displays a message box to the user. Always present
meaningful message boxes to your users if they do something
wrong. The _ character denotes a line continuation in VBA.
You can put all that code on one line, but for newsgroup posts
it is usually easier to make smaller blocks of code for copy/paste.
' Stop the form from opening
Cancel = True
Here we tell Access to prevent this form from opening since the
sure did not select a Patient ID from the main form combo box.
This is the second "half "of our If-Else-End If statement. Think of
this part as the False area of the test.
The basic construct of the If-Else-End If block is this in its most
simplest terms.
If Something yadda, yadda Then
' Do something here
Else
' It didn't happen so do something else here
End If
When writing a If-Else-End If block it is a good idea to immediately
put in all the parts (If, Then, Else, End If) so you do not forget them
later on. This is quite easy to forget when making very long blocks
of code with multiple If-Else-End If tests.
' Patient ID was selected on main form
' Run Security Code
LAS_EnableSecurity Me
This code is obviously something custom made in your application, most
likely in a module. I have no idea what it does so I cannot comment it.
' Maximize the form
DoCmd.Maximize
This code maximizes the form to fill the entire screen area.
' Apply a filter to match chosen Patient Number
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]
Here we Filter the current form's record source to limit the display.
We only show the record(s) that match the criteria selected from
the combo box. Since there are spaces in your field and object
names, we have to enclose them in brackets []. Otherwise, Access
will get confused and cough up a hairball on your keyboard. Not
a pretty sight let me tell you!
' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")
' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
Here you appear to be filling in some text box values on the form with
existing information in a table called tblDefaults.
' 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!"
Here you appear to be making one last test before making a final
commitment to open the form. If your test proves true (meaning
FinalAnswer is False in the table) you display a message to the user
and stop the form from opening. Again, this is all wrapped up
inside another If-Else-End If block.
' Stop the form from opening
Cancel = True
You prevent the form from opening here.
Else
' All clear, continue with form opening
When this part of the code is reached it means we have passed
all of our tests and we can now proceed with opening the form.
This End If is the closing block for the second test.
This End If is the closing block for the first test.
Since we should always be having proper error handling in our
code, we naturally have to have a proper exit area for the code.
As a good rule, we should only have one spot to enter code, and
one spot to exit code. The exit code is also the place we should
perform any cleanup of this procedure. If we have opened and/or
used objects we need to close them and release their memory.
"If you open something: close it. If you take something out: put it back
when finished." Your mother was right all along.
Error_Handler:
If Err.Number = 2467 Then
' Ignore
Here you appear to be possibly expecting Error number 2467 at
some point. This error will be raised if Access cannot find an object
you are referring to in some code somewhere. I'm not sure why
you think you may be getting this error, but I can only guess it has
something to do with the custom security routine. At any rate, you
are telling Access to ignore this error.
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
Here we tell Access to display a nice message box to the user if
we encounter something we had not planned. This type of code
should be in most of your error handling routines. There are times
when we have On Error Resume Next procedures so this would
not be needed, but in most cases we want to know what went
wrong with our code. This is obviously something we did not plan
for so we want to display a custom message that would be easier
for users to report rather than a scary looking Access message.
At the end of every error handling routine we want to tell Access
to exit out of our code through the one and *only* one exit door.
This way the code will jump up to the exit area, do any cleanup
stuff, and gracefully exit out of the procedure. No "leaving the seat up"
or other bad stuff will result.
Finally, our ending statement for the code.
In summary, to adapt this code for other data entry forms in your
application you simply need to follow the same logic. If you want
the user to make a selection in the combo box just follow this
generic coding:
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
' Whatever existing code you already have present in the Open event.
End If
As a side note, since you had two If tests in this particular form, you
could also have coded it like so:
'************Code Start************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler
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]
' 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
'************Code End************
Just to show an alternative.
in either event, i gotta thank you guys for all your great help and
enthusiasm.
one day i hope to be in a position to do the same.
Study these links and you should be there in no time:
http://www.ltcomputerdesigns.com/JCReferences.html
You're very welcome, class dismissed.
