DoCmd.SearchForRecord

[Chuck]

Can anyone tell me what is wrong with the following statement?

DoCmd.SearchForRecord acDataForm, DetailForm, acFirst, WhereCond

DetailForm = "frmDynDfltsDetail" frmDynDfltsDetail is a form that is
currently open.

WhereCond = "kcoSinkTable = "PsgrService" AND kcoSinkField = "LoadFactor"
AND kcoSourceTable = "Options" AND kcoSourceField = "PsgrLoadFactor" "

kcoSinkTable, kcoSinkField, kcoSourceTable, and kcoSourceField are Combo
Boxes in frmDynDfltsDetail.

I get no error message when the DoCmd.SearchForRecord runs; it just doesn't
seem to be able to find the specified record. I suspect the problem is
something simple but I can't see it.

 

[Paolo]

Hi Chuck,
I think it's your where condition that doesn't work.
If PsgrService, LoadFactor etc. are constant put them between simple
quotations i.e.
WhereCond = "kcoSinkTable = 'PsgrService' AND kcoSinkField = 'LoadFactor'
AND kcoSourceTable = 'Options' AND kcoSourceField = 'PsgrLoadFactor'"
If they are variable you have to concatenate them to the where cond with
ampersand i.e.
WhereCond = "kcoSinkTable = " & PsgrService & " AND kcoSinkField = " &
LoadFactor & " AND kcoSourceTable = " & Options & " AND kcoSourceField = " &
PsgrLoadFactor

HTH, Merry Xmas Paolo

 

[Chuck]

Thanks for the help, Paolo, but your suggestion didn’t quite do it. The
problem turned out to be use of form Control names instead of table column
names in the WhereCond string. For example, where I used kcoSinkTable (the
name of a Key COmbo box), I should have used SinkTable (the name of the Combo
Box’s ControlSource). When I made that change in the generation of the
WhereCond string, the SearchForRecord did what I was expecting.

What finally put me on the right track was reading (for the thousandth
time!) the line in the Remarks section of the SearchForRecord Help that says:
“You can refer to fields that are in the record source of a form or report
but are not displayed on the form or report. In the preceding example,
neither Description nor CategoryID must be displayed on the form or report
for the criteria to work.â€

The “… can refer to…†part is misleading in that it makes this sound
optional; the “…neither…must be displayed†part is what really suggested the
solution to me but it is still a bit ambiguous. There should be a line that
says something like, “Regardless of the ObjectType argument, the
WhereCondition argument must refer to fields in the underlying table.â€

 

[Crystal (strive4peace)]

Hi Chuck,

Yes, the expressions to search much be in the RecordSource

if you use the .RecordsetClone.FindFirst method to search, you do not have
to worry about the form being active because it is specified.

in the header of almost every main form, I create one or more combos to Find
a record. The display part of the combo does not have to be more than a few
characters -- however many you think they might need to see when they use the
TypeAhead to find an item in the list.

If you have filter controls, rather than filtering the form, filter the Find
combos

Make one or more unbound (no ControlSource) combos on your form (like in the
header). Let the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event, put this in the
[Event Procedure] code:

FindRecord

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

With Me
'find the first value that matches
.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the RecordSource
of the form -- assuming your primary key is a Long Integer data type
(autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the first
column is the primary key ID of the table you want to search (and that field
is part of the RecordSource for the form you are searching).

For instance, if your main form is People, you could have a combo to find a
person using their phone number by linking to the Phones table (assuming your
data is normalized) and putting the PeopleID in the first column (hidden) and
the Phone in the column that displays. In this case, I use a procedure to
strip non-numeric characters so the user does not have to type parentheses or
dashes since I store phone numbers with symbols. The width of this column is
set tiny so it is greater than zero, but is small enough so the user does not
see it -- and this is the second column. The user actually sees the third
column -- the one with the mask symbols.

If you are searching the recordset on another form, change the FindRecord
name to be specific (like FindRecord_Order) and, substitute

With Me --> With forms!formname

If the record you are looking for is on a subform, change the FindRecord
name to be specific (like FindRecord_Whatever) and, substitute:

With Me --> with Me.subform_controlname.form

'~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal

Learn Access on YouTube
http://www.youtube.com/user/LearnAccessByCrystal

*
have an awesome day!
*