Specify "Look In:" value for "Find" pop-up?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Access Command Button Wizard makes it very easy to add a "Find" button to
a form. It uses this code in the Click event:
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

I want the Find to "Look In:" a specific field on my form, but if the user
clicks on a different field on the form, "Look In:" changes to that field
name. Is there a way to call the Find command and specify the "Look In:"
value?

Thank you for any help you can give me,
Judy Ward
 
If you want to trigger the Find on Field1, replace the first line of your
code with:
Me.[Field1].SetFocus
 
I tried replacing the first line with:
Me.txtSAR_ID.SetFocus

But when I click the find button the "Find and Replace" pop-up has "Look In:
SAR", not "Lood In: SAR_ID". Then if I enter "Find What: 2004" it finds the
first record with 2004 in any field (which happens to be a date field, not
the SAR_ID field that I want). I can see that the drop-down for the Look In:
has SAR_ID and SAR as the choices, but it is defaulting to SAR. I am hoping
to find a way to set the Look In value to SAR_ID.

Can I call the find command with parameters?

Thank you,
Judy

Allen Browne said:
If you want to trigger the Find on Field1, replace the first line of your
code with:
Me.[Field1].SetFocus

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Judy Ward said:
The Access Command Button Wizard makes it very easy to add a "Find" button
to
a form. It uses this code in the Click event:
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

I want the Find to "Look In:" a specific field on my form, but if the user
clicks on a different field on the form, "Look In:" changes to that field
name. Is there a way to call the Find command and specify the "Look In:"
value?

Thank you for any help you can give me,
Judy Ward
Click to expand...
Click to expand...
 
Judy, what you are trying to do should work if txtSAR_ID is a text box, and
it is bound to the SAR_ID field.

If it is a combo with a zero-width bound column, the results will be
inconsistent across different versions of Access.

A better solution might be to FindFirst in the RecordsetClone of the form
instead of calling the Find dialog. This example assumes an unbound text box
named txtFindWhat, and searches for the value in the fields SAR_ID (assumed
to be a Number field, not a Text field):

Dim rs As DAO.Recordset

If Not IsNull(Me.txtFindWhat) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[SAR_ID] = " & Me.txtFindWhat
If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Judy Ward said:
I tried replacing the first line with:
Me.txtSAR_ID.SetFocus

But when I click the find button the "Find and Replace" pop-up has "Look
In:
SAR", not "Lood In: SAR_ID". Then if I enter "Find What: 2004" it finds
the
first record with 2004 in any field (which happens to be a date field, not
the SAR_ID field that I want). I can see that the drop-down for the Look
In:
has SAR_ID and SAR as the choices, but it is defaulting to SAR. I am
hoping
to find a way to set the Look In value to SAR_ID.

Can I call the find command with parameters?

Thank you,
Judy

Allen Browne said:
If you want to trigger the Find on Field1, replace the first line of your
code with:
Me.[Field1].SetFocus

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Judy Ward said:
The Access Command Button Wizard makes it very easy to add a "Find"
button
to
a form. It uses this code in the Click event:
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

I want the Find to "Look In:" a specific field on my form, but if the
user
clicks on a different field on the form, "Look In:" changes to that
field
name. Is there a way to call the Find command and specify the "Look
In:"
value?

Thank you for any help you can give me,
Judy Ward
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen,

Please correct me if I'm in error but I believe you can use the
RecordSetClone without Set or reset code. ie: See in line

This Dim statement not needed.
Dim rs As DAO.Recordset ' Not needed!!

If Not IsNull(Me.txtFindWhat) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set. Following Set not needed.
Set rs = Me.RecordsetClone
rs.FindFirst "[SAR_ID] = " & Me.txtFindWhat
Click to expand...
The above line can be written as:
Me.RecordSetClone.FindFirst "[SAR_ID] = " & Me.txtFindWhat
If rs.NoMatch Then
Click to expand...
Then: If Me.RecordSetClone.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
Click to expand...
Of course: Me.Bookmark = Me.RecordSetClone.Bookmark
End If Following line not needed:
Set rs = Nothing
Click to expand...

Your code on your site is always tight and neat so there is probably a very
good reason to *not* do as I suggest. You've forgotten more about Access
than I'll ever learn. Your opinion is always valued by me.
 
Yes, your suggestion is workable.

In Access 97, that approach (relying on Access to destroy the reference)
could cause Access to leave a reference open and then Access would not shut
down, so we got into the habit of declaring the variable and explicitly
cleaning up.

In Access 2000 and later, VBA doesn't know whether the form's recordset is
DAO or ADO, and so does not offer the Intellisense options for the
properties unless you explicitly declare your type, there is still some
advantage.

In all versions from Access 95 onwards, you cannot assume the RecordsetClone
is where it was last time you worked with it. It is extremely unlikely
(probably impossible) that this should cause any problem within one
procedure, but in any case it would be more efficient to use:
With Me.RecordsetClone
rather than multiple referencces to it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
RuralGuy said:
Hi Allen,

Please correct me if I'm in error but I believe you can use the
RecordSetClone without Set or reset code. ie: See in line

This Dim statement not needed.
Dim rs As DAO.Recordset ' Not needed!!

If Not IsNull(Me.txtFindWhat) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set. Following Set not needed.
Set rs = Me.RecordsetClone
rs.FindFirst "[SAR_ID] = " & Me.txtFindWhat
Click to expand...
The above line can be written as:
Me.RecordSetClone.FindFirst "[SAR_ID] = " & Me.txtFindWhat
If rs.NoMatch Then
Click to expand...
Then: If Me.RecordSetClone.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
Click to expand...
Of course: Me.Bookmark = Me.RecordSetClone.Bookmark
End If Following line not needed:
Set rs = Nothing
Click to expand...

Your code on your site is always tight and neat so there is probably a
very
good reason to *not* do as I suggest. You've forgotten more about Access
than I'll ever learn. Your opinion is always valued by me.
Click to expand...
 
Allen Browne said:
Yes, your suggestion is workable.

In Access 97, that approach (relying on Access to destroy the reference)
could cause Access to leave a reference open and then Access would not
shut down, so we got into the habit of declaring the variable and
explicitly cleaning up.

In Access 2000 and later, VBA doesn't know whether the form's recordset
is DAO or ADO, and so does not offer the Intellisense options for the
properties unless you explicitly declare your type, there is still some
advantage.

In all versions from Access 95 onwards, you cannot assume the
RecordsetClone is where it was last time you worked with it. It is
extremely unlikely (probably impossible) that this should cause any
problem within one procedure, but in any case it would be more efficient
to use:
With Me.RecordsetClone
rather than multiple referencces to it.
Click to expand...

Thanks Allen. I was certain there was a valid reason for your style and
thought I'd take the opportunity to have you explain. Now I'm wiser than I
was before you answered. 8^)
 
Back
Top