The code goes in a module attached to the form; the individual Sub(routine)s
are attached to the appropriate event for the particular control. There are
several ways to get to this. I find the easiest (to enter code initially)
is via the Event tab on the from Properties dialog box - select [Event
Procedure], then choose Code Builder via the ... button which appears at the
right of the field for the event; this will open the Visual Basic editor in
the Class Module for the form, and either take you to the existing code for
the event or (if there is no code yet entered) generate the Sub and End Sub
statements in readiness for you to enter code.
When you are in the VBA editor, you can enter/edit code for more than one
event; in this case, you could cut/paste my code and enter it all in one go.
The VBA editor will add dividing lines between each routine within the
module. NOTE: if you enter code in this fashion, the editor may not
generate the link to the control's event property - you should ensure that
each control which has code for it has {Event Procedure] in the Event
Properties box. For the code I posted, there would be an Event Procedure in
the Change event for each of the four combo-boxes, and for the Click event
of the "Show All" button. The BuildFilterString sub is not attached to any
event (and you'll notice that its name does not end with "_EventName", as do
the other subs); it is called from each of the other subs.
A couple of useful tips when working in the VBA editor:
Include the line "Option Explicit" at the top of each module (it's entered
automatically if the Auto Syntax Check option is turned on); define your
variables (in Dim statements) with both upper and lower case letters, and
then enter variable names in lowercase - their case will be changed to
match the definition, if it doesn't change, you've got a typo. This also
works for all object names and keywords.
After entering/editing code, click "Compile" from the Debug menu; this will
warn you about other errors in your code that would result in run-time
errors. If nothing happens, you're OK (well, maybe ... It only gets another
sort of syntax errors; not everything that might give you a run-time error).
Another very important Option setting, this time in Access itself, rather
than the VBA editor, is to turn off Name AutoCorrect (on the General tab).
You can save your code changes directly from the VBA editor; if you don't do
so, you'll be prompted to Save Changes when you close the form in Access.
HTH,
Rob
PS. Very important: get yourself a good reference book, for the version of
Access that you're using. There was a thread in the
comp.databases.ms-access newsgroup on this topic a few days ago - you
should be able to find it if you do a Google search on that newsgroup.
Also, if you're really new, you might have problems with delimiters in If
statements and constructed strings (such as strFilter in my code) if your
date types are not numbers (as in my posted example). The basic rules are:
numbers need no delimiter - eg, if TreatmentID is a number, you would
have:
strFilter & "(TreatmentID = " & cboxShowTreatment & ")"
text needs quote mark delimiters, and if these are in a quoted string,
they should be of opposite type (single/double) - eg. if Treatment and the
bound field in cboxShowTreatment are text strings, you would have:
strFilter & "(Treatment = '" & cboxShowTreatment & "')"
dates need # delimiters - eg, if EntryDate and the bound column in
cboxEntryDate are dates, you would have:
strFilter & "(EntryDate = #" & cboxShowTreatment & "#)"
