Logic
Using With pForm and End With enhances performance and makes code easier to read.
Anything starting with a dot (.) between these two statements
is a property or method of the passed form.
FindRecordN is often called on the AfterUpdate event of an
unbound combo box or listbox
to pick a record to find.
In these cases, the key ID to find will be read from the active control.
If the active control is Null then the code exits because there is nothing to look for.
Unless pbClear is set to False,
the combo box (or whatever control is active) will be cleared after the value is read.
When pnKeyID is -99,
the active control has the key value to find, which is the default.
So if you might want to actually find a record whose ID is -99,
then modify the code to use a different number as an indicator.
If a value (other than -99) for pnKeyID is sent,
then the active control does not matter,
and nothing will be set to Null either.
So FindRecordN can be called to find a record
where the key ID to find is determined in code.
It can also be used to find a record on a subform,
or another open form.
In most cases, the key ID will be a Long Integer,
but you can make another version
where it is a string
and add delimiters where necessary.
Name it something like
FindRecordStr.
Or perhaps make a function called FindRecordWhere
that takes a Where clause with anything you want.
A copy of the form recordset, the RecordsetClone, is searched for the key field being equal to the key value.
If a record is found, RecordsetClone.NoMatch is False ...
and Not False is True.
That record is made active on the form by setting the Bookmark
to be the same as the bookmark found in the RecordsetClone.
You could also search the form recordset directly (instead of using RecordsetClone).
However using the clone leaves the current record active
in the event what you're looking for isn't found.
And its considered better practice.
If the record to find is found in the RecordsetClone,
if there are unsaved changes to the record on the form (Dirty),
they are saved before leaving the active record.
It is a good idea to specifically save a dirty record
before moving the record pointer.
If a control name to set focus to is specified,
that control is made active and the code exits.
Although this is defined to be a function that returns True or False,
I rarely use the return value and normally treat this like a Sub
(that doesn't return a value).
Parameters
Required
-
pForm is the passed Form reference
in code behind a form,
which will usually be Me
-
psKeyFieldname is the name of the key field in the recordset.
If a value to look for isn't passed,
the active control is assumed to have it.
Optional
-
pCtrlName_SetFocus is the name of the control on the form to set focus to after a record is found.
-
pnKeyID is the value of the key field to find. If not passed, value in ActiveControl will be used.
-
pbClear: True (default) to clear value in ActiveControl after reading.
If pnKeyID is specified, this won't be done.
Return
Returns True if the record is found or False if it isn't.
