Code for Find and Replace button

[JRF]

Hello and thank you for your valued advice.



Access 2003

Win 2K OS



I am attempting to duplicate the effects "in code" of Find and Replace, from
the Find and Replace feature within Access, specifically the Find Next
portion of the Find and Replace dialog box.



I have a form, FormA, which is bound to a table, Table1. On FormA, I
frequently do a search on one field, Field1, using the Find and Replace
feature, which is called from a command button, Button1. With Field1
highlighted, Button1 is clicked to open the Find and Replace dialog box, the
Field1 value is automatically populated to this box. I click Find Next and
when a matching record is found for Field1, the remainder of the FormA
fields populate with that matching record data.



I have now put into this form, FormA, a "Refresh" command button which I
want to perform the same function as the Find Next portion of the Find and
Replace feature, without the dialog box. My intention is to eliminate the
display of the Find and Replace dialog box, yet, achieve the same results
through code by Clicking the new Refresh command button.



I have attempted some coding on my own using Findnext and GotoRecord and
cannot seem to accomplish what I am looking for, though I will continue to
look. I think I need some way of keeping the binding to the form through
recordsets when the search value is found, at least that is the direction I
am currently pursuing. I could use to guidance, thank you.



Proccess in current live sequence:



Enter my search value in Field1 of FormA

Click Button1, Find and Replace dialog opens, Field1 value is populated to
dialog box

Click Find Next

Value is found and populates remainder fields of FormA.



Desired Sequence:



Enter my search value in Field1 of FormA

Click "Refresh" button

Field1 value is searched, found, and populates fields of FormA.





** Current Code for Button1



Private Sub Search_Click()



On Error GoTo Err_Search_Cl



Screen.PreviousControl.SetFocus

DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70



Exit_Search_Cl:

Exit Sub



Err_Search_Cl:

Errorroutine

Resume Exit_Search_Cl



End Sub

 

[JRF]

Sorry for the poor formatting of previous post

Hello and thank you for your valued advice.

Access 2003
Win 2K OS

I am attempting to duplicate the effects "in code" of Find and Replace, from
the Find and Replace feature within Access, specifically the Find Next
portion of the Find and Replace dialog box.

I have a form, FormA, which is bound to a table, Table1. On FormA, I
frequently do a search on one field, Field1, using the Find and Replace
feature, which is called from a command button, Button1. With Field1
highlighted, Button1 is clicked to open the Find and Replace dialog box, the
Field1 value is automatically populated to this box. I click Find Next and
when a matching record is found for Field1, the remainder of the FormA
fields populate with that matching record data.

I have now put into this form, FormA, a "Refresh" command button which I
want to perform the same function as the Find Next portion of the Find and
Replace feature, without the dialog box. My intention is to eliminate the
display of the Find and Replace dialog box, yet, achieve the same results
through code by Clicking the new Refresh command button.

I have attempted some coding on my own using Findnext and GotoRecord and
cannot seem to accomplish what I am looking for, though I will continue to
look. I think I need some way of keeping the binding to the form through
recordsets when the search value is found, at least that is the direction I
am currently pursuing. I could use to guidance, thank you.


Proccess in current live sequence:
Enter my search value in Field1 of FormA
Click Button1, Find and Replace dialog opens, Field1 value is populated to
dialog box
Click Find Next
Value is found and populates remainder fields of FormA.

Desired Sequence:
Enter my search value in Field1 of FormA
Click "Refresh" button
Field1 value is searched, found, and populates fields of FormA.

** Current Code for Button1
Private Sub Search_Click()

On Error GoTo Err_Search_Cl

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Cl:
Exit Sub

Err_Search_Cl:
Errorroutine
Resume Exit_Search_Cl

End Sub

 

[JRF]

So far I have been able to search an implement this type of code. While it
appears to work, I need a method of updating the FormA fields with the
associated record information, and maintain the current record source
binding of FormA to record source DATA, much the same as the Find and
Replace dialog box would do.

Private Sub btnRefresh_Click()

On error goto err_refresh_cl

Screen.PreviousControl.SetFocus
DoCmd.FindRecord "[Power Number]= " & Me![Power Number], acEntire,
False, acSearchAll, False, acCurrent, True

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

End Sub

 

[JRF]

I have used recordsets in the past to populate Form fields, with "unbound"
forms. I used recordset to populate the FormA fields based on the success
of the search criteria, and have been able to populate the FormA fields, and
at the same time, generate a duplicate record in the main DATA table. This
does not happen when the Find and Replace dialog box feature is used.

So far I have been able to search an implement this type of code. While it
appears to work, I need a method of updating the FormA fields with the
associated record information, and maintain the current record source
binding of FormA to record source DATA, much the same as the Find and
Replace dialog box would do.

Private Sub btnRefresh_Click()

On error goto err_refresh_cl

Screen.PreviousControl.SetFocus
DoCmd.FindRecord "[Power Number]= " & Me![Power Number], acEntire,
False, acSearchAll, False, acCurrent, True

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

End Sub

Sorry for the poor formatting of previous post

Hello and thank you for your valued advice.

Access 2003
Win 2K OS

I am attempting to duplicate the effects "in code" of Find and Replace, from
the Find and Replace feature within Access, specifically the Find Next
portion of the Find and Replace dialog box.

I have a form, FormA, which is bound to a table, Table1. On FormA, I
frequently do a search on one field, Field1, using the Find and Replace
feature, which is called from a command button, Button1. With Field1
highlighted, Button1 is clicked to open the Find and Replace dialog box, the
Field1 value is automatically populated to this box. I click Find Next and
when a matching record is found for Field1, the remainder of the FormA
fields populate with that matching record data.

I have now put into this form, FormA, a "Refresh" command button which I
want to perform the same function as the Find Next portion of the Find and
Replace feature, without the dialog box. My intention is to eliminate the
display of the Find and Replace dialog box, yet, achieve the same results
through code by Clicking the new Refresh command button.

I have attempted some coding on my own using Findnext and GotoRecord and
cannot seem to accomplish what I am looking for, though I will continue to
look. I think I need some way of keeping the binding to the form through
recordsets when the search value is found, at least that is the

direction

I
am currently pursuing. I could use to guidance, thank you.


Proccess in current live sequence:
Enter my search value in Field1 of FormA
Click Button1, Find and Replace dialog opens, Field1 value is populated to
dialog box
Click Find Next
Value is found and populates remainder fields of FormA.

Desired Sequence:
Enter my search value in Field1 of FormA
Click "Refresh" button
Field1 value is searched, found, and populates fields of FormA.

** Current Code for Button1
Private Sub Search_Click()

On Error GoTo Err_Search_Cl

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Cl:
Exit Sub

Err_Search_Cl:
Errorroutine
Resume Exit_Search_Cl

End Sub


Replace,
from box,
the Next
and

continue

to direction

populated

to

 

[Dirk Goldgar]

I have used recordsets in the past to populate Form fields, with
"unbound" forms. I used recordset to populate the FormA fields based
on the success of the search criteria, and have been able to populate
the FormA fields, and at the same time, generate a duplicate record
in the main DATA table. This does not happen when the Find and
Replace dialog box feature is used.

So far I have been able to search an implement this type of code.
While it appears to work, I need a method of updating the FormA
fields with the associated record information, and maintain the
current record source binding of FormA to record source DATA, much
the same as the Find and Replace dialog box would do.

Private Sub btnRefresh_Click()

On error goto err_refresh_cl

Screen.PreviousControl.SetFocus
DoCmd.FindRecord "[Power Number]= " & Me![Power Number],
acEntire, False, acSearchAll, False, acCurrent, True

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

End Sub

Sorry for the poor formatting of previous post

Hello and thank you for your valued advice.

Access 2003
Win 2K OS

I am attempting to duplicate the effects "in code" of Find and
Replace, from the Find and Replace feature within Access,
specifically the Find Next portion of the Find and Replace dialog
box.

I have a form, FormA, which is bound to a table, Table1. On FormA,
I frequently do a search on one field, Field1, using the Find and
Replace feature, which is called from a command button, Button1.
With Field1 highlighted, Button1 is clicked to open the Find and
Replace dialog box, the Field1 value is automatically populated to
this box. I click Find Next and when a matching record is found
for Field1, the remainder of the FormA fields populate with that
matching record data.

I have now put into this form, FormA, a "Refresh" command button
which I want to perform the same function as the Find Next portion
of the Find and Replace feature, without the dialog box. My
intention is to eliminate the display of the Find and Replace
dialog box, yet, achieve the same results through code by Clicking
the new Refresh command button.

I have attempted some coding on my own using Findnext and
GotoRecord and cannot seem to accomplish what I am looking for,
though I will continue to look. I think I need some way of keeping
the binding to the form through recordsets when the search value is
found, at least that is the direction I am currently pursuing. I
could use to guidance, thank you.


Proccess in current live sequence:
Enter my search value in Field1 of FormA
Click Button1, Find and Replace dialog opens, Field1 value is
populated to dialog box
Click Find Next
Value is found and populates remainder fields of FormA.

Desired Sequence:
Enter my search value in Field1 of FormA
Click "Refresh" button
Field1 value is searched, found, and populates fields of FormA.

** Current Code for Button1
Private Sub Search_Click()

On Error GoTo Err_Search_Cl

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Cl:
Exit Sub

Err_Search_Cl:
Errorroutine
Resume Exit_Search_Cl

End Sub

It looks to me like you're trying to find a record with a particular
value in a particular field. Is that right? If so, you can do it more
easily than you seem to be attempting. Try code like this:

'----- start of suggested code -----
Private Sub btnRefresh_Click()

On Error GoTo Err_refresh_cl

If IsNull(Me![Power Number]) Then
MsgBox "Please specify the number to search for."
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Power Number] = " & Me![Power Number]
If .NoMatch Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

'----- end of suggested code -----

Note that the above assumes that [Power Number] is a numeric field. If
it's a text field, quotes must be inserted into the search expression
surrounding the value from Me![Power Number].

 

[JRF]

That is correct, a particular record in a particular field. On success of
the search, I want all FormA fields to populate with the associated record
information of that particular record in the particular field.

I have used recordsets in the past to populate Form fields, with
"unbound" forms. I used recordset to populate the FormA fields based
on the success of the search criteria, and have been able to populate
the FormA fields, and at the same time, generate a duplicate record
in the main DATA table. This does not happen when the Find and
Replace dialog box feature is used.

So far I have been able to search an implement this type of code.
While it appears to work, I need a method of updating the FormA
fields with the associated record information, and maintain the
current record source binding of FormA to record source DATA, much
the same as the Find and Replace dialog box would do.

Private Sub btnRefresh_Click()

On error goto err_refresh_cl

Screen.PreviousControl.SetFocus
DoCmd.FindRecord "[Power Number]= " & Me![Power Number],
acEntire, False, acSearchAll, False, acCurrent, True

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

End Sub
Sorry for the poor formatting of previous post

Hello and thank you for your valued advice.

Access 2003
Win 2K OS

I am attempting to duplicate the effects "in code" of Find and
Replace, from the Find and Replace feature within Access,
specifically the Find Next portion of the Find and Replace dialog
box.

I have a form, FormA, which is bound to a table, Table1. On FormA,
I frequently do a search on one field, Field1, using the Find and
Replace feature, which is called from a command button, Button1.
With Field1 highlighted, Button1 is clicked to open the Find and
Replace dialog box, the Field1 value is automatically populated to
this box. I click Find Next and when a matching record is found
for Field1, the remainder of the FormA fields populate with that
matching record data.

I have now put into this form, FormA, a "Refresh" command button
which I want to perform the same function as the Find Next portion
of the Find and Replace feature, without the dialog box. My
intention is to eliminate the display of the Find and Replace
dialog box, yet, achieve the same results through code by Clicking
the new Refresh command button.

I have attempted some coding on my own using Findnext and
GotoRecord and cannot seem to accomplish what I am looking for,
though I will continue to look. I think I need some way of keeping
the binding to the form through recordsets when the search value is
found, at least that is the direction I am currently pursuing. I
could use to guidance, thank you.


Proccess in current live sequence:
Enter my search value in Field1 of FormA
Click Button1, Find and Replace dialog opens, Field1 value is
populated to dialog box
Click Find Next
Value is found and populates remainder fields of FormA.

Desired Sequence:
Enter my search value in Field1 of FormA
Click "Refresh" button
Field1 value is searched, found, and populates fields of FormA.

** Current Code for Button1
Private Sub Search_Click()

On Error GoTo Err_Search_Cl

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Cl:
Exit Sub

Err_Search_Cl:
Errorroutine
Resume Exit_Search_Cl

End Sub

It looks to me like you're trying to find a record with a particular
value in a particular field. Is that right? If so, you can do it more
easily than you seem to be attempting. Try code like this:

'----- start of suggested code -----
Private Sub btnRefresh_Click()

On Error GoTo Err_refresh_cl

If IsNull(Me![Power Number]) Then
MsgBox "Please specify the number to search for."
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Power Number] = " & Me![Power Number]
If .NoMatch Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

'----- end of suggested code -----

Note that the above assumes that [Power Number] is a numeric field. If
it's a text field, quotes must be inserted into the search expression
surrounding the value from Me![Power Number].

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

That is correct, a particular record in a particular field. On
success of the search, I want all FormA fields to populate with the
associated record information of that particular record in the
particular field.

It looks to me like you're trying to find a record with a particular
value in a particular field. Is that right? If so, you can do it
more easily than you seem to be attempting. Try code like this:

'----- start of suggested code -----
Private Sub btnRefresh_Click()

On Error GoTo Err_refresh_cl

If IsNull(Me![Power Number]) Then
MsgBox "Please specify the number to search for."
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Power Number] = " & Me![Power Number]
If .NoMatch Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

'----- end of suggested code -----

Note that the above assumes that [Power Number] is a numeric field.
If it's a text field, quotes must be inserted into the search
expression surrounding the value from Me![Power Number].

So did you try the suggested code? Did it do what you want?

 

[JRF]

It would appear that something completes with the code segment, no error
messages are generated. However, the remaining fields on the FormA
associated with the record source where [Power Number] resides, do not
populate the FormA with the information related to a particular record with
a value of [Power Number]

Form record source = DATA
Form fields bound to table fields in table DATA
[Power Number] resides in table DATA as data type number

That is correct, a particular record in a particular field. On
success of the search, I want all FormA fields to populate with the
associated record information of that particular record in the
particular field.

It looks to me like you're trying to find a record with a particular
value in a particular field. Is that right? If so, you can do it
more easily than you seem to be attempting. Try code like this:

'----- start of suggested code -----
Private Sub btnRefresh_Click()

On Error GoTo Err_refresh_cl

If IsNull(Me![Power Number]) Then
MsgBox "Please specify the number to search for."
Exit Sub
End If

With Me.RecordsetClone
.FindFirst "[Power Number] = " & Me![Power Number]
If .NoMatch Then
MsgBox "Not found!"
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_refresh_cl:
Exit Sub

Err_refresh_cl:
Errorroutine
Resume Exit_refresh_cl

End Sub

'----- end of suggested code -----

Note that the above assumes that [Power Number] is a numeric field.
If it's a text field, quotes must be inserted into the search
expression surrounding the value from Me![Power Number].

So did you try the suggested code? Did it do what you want?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

It would appear that something completes with the code segment, no
error messages are generated. However, the remaining fields on the
FormA associated with the record source where [Power Number] resides,
do not populate the FormA with the information related to a
particular record with a value of [Power Number]

Form record source = DATA
Form fields bound to table fields in table DATA
[Power Number] resides in table DATA as data type number

I'm confused. Is this FormA a different form from the form where the
search code resides? I was assuming it was the same form.

 

[JRF]

The search code resides behind FormA, and is associated with a command
button on FormA. It is the same form.

It would appear that something completes with the code segment, no
error messages are generated. However, the remaining fields on the
FormA associated with the record source where [Power Number] resides,
do not populate the FormA with the information related to a
particular record with a value of [Power Number]

Form record source = DATA
Form fields bound to table fields in table DATA
[Power Number] resides in table DATA as data type number

I'm confused. Is this FormA a different form from the form where the
search code resides? I was assuming it was the same form.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The search code resides behind FormA, and is associated with a command
button on FormA. It is the same form.

It would appear that something completes with the code segment, no
error messages are generated. However, the remaining fields on the
FormA associated with the record source where [Power Number]
resides, do not populate the FormA with the information related to a
particular record with a value of [Power Number]

Form record source = DATA
Form fields bound to table fields in table DATA
[Power Number] resides in table DATA as data type number

I'm confused. Is this FormA a different form from the form where the
search code resides? I was assuming it was the same form.

That doesn't make sense to me. That code ought to do one of the
following:

(a) find the record,
(b) give you one of its message boxes,
(c) raise an error that would be result in Errorroutine being
called, or
(d) raise a compile error.

With the code module open, click Debug -> Compile, and tell me if any
compile errors are reported.

 

[JRF]

I got it to work, I had to comment out part of the original code seqment
that was associated with previous code applied to the command button, which
was actually associated with the macro that used to call the Find and
Replace dialog box. The solution runs very well.

Thank you very much for the assistance and patience.

'**Screen.PreviousControl.SetFocus

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Me![Power Number]
rst.FindFirst "[Power Number] = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

The search code resides behind FormA, and is associated with a command
button on FormA. It is the same form.

It would appear that something completes with the code segment, no
error messages are generated. However, the remaining fields on the
FormA associated with the record source where [Power Number]
resides, do not populate the FormA with the information related to a
particular record with a value of [Power Number]

Form record source = DATA
Form fields bound to table fields in table DATA
[Power Number] resides in table DATA as data type number

I'm confused. Is this FormA a different form from the form where the
search code resides? I was assuming it was the same form.

That doesn't make sense to me. That code ought to do one of the
following:

(a) find the record,
(b) give you one of its message boxes,
(c) raise an error that would be result in Errorroutine being
called, or
(d) raise a compile error.

With the code module open, click Debug -> Compile, and tell me if any
compile errors are reported.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I got it to work, I had to comment out part of the original code
seqment that was associated with previous code applied to the command
button, which was actually associated with the macro that used to
call the Find and Replace dialog box. The solution runs very well.

Thank you very much for the assistance and patience.

You're welcome. But look at this:

[...]

Set rst = Me.RecordsetClone [...]
rst.Close

DON'T close the form's RecordsetClone.

 

[JRF]

One last question. If my FormA record source is the table DATA, what harm
would be caused by closing the recordset in the below code. The FormA
record source will continue to be table DATA.

I got it to work, I had to comment out part of the original code
seqment that was associated with previous code applied to the command
button, which was actually associated with the macro that used to
call the Find and Replace dialog box. The solution runs very well.

Thank you very much for the assistance and patience.

You're welcome. But look at this:

[...]

Set rst = Me.RecordsetClone [...]
rst.Close

DON'T close the form's RecordsetClone.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

One last question. If my FormA record source is the table DATA, what
harm would be caused by closing the recordset in the below code. The
FormA record source will continue to be table DATA.

No actual harm will be done in this case, but it serves no purpose. In
Access 2000 and later, the call to close the recordsetclone will be
ignored. The handling of the recordsetclone was different in Access 97,
and I'm not sure whether the call to close it would be ignored or not,
but even if it were honored it wouldn't affect the form itself, only
subsequent references to the recordsetclone.

However, the rule to follow with respect to objects is, "If you open it,
close it. If you didn't open it, don't close it." This can keep you
out of trouble in other circumstances where it does make a difference.
People failing to follow that rule have caused Microsoft to add special
code to the Close logic of a number of critical objects to ignore
improper calls to close them, but you wouldn't want to get in the habit
of relying on Microsoft to save you from yourself.

 

[JRF]

Thank you, makes perfect sense.

No actual harm will be done in this case, but it serves no purpose. In
Access 2000 and later, the call to close the recordsetclone will be
ignored. The handling of the recordsetclone was different in Access 97,
and I'm not sure whether the call to close it would be ignored or not,
but even if it were honored it wouldn't affect the form itself, only
subsequent references to the recordsetclone.

However, the rule to follow with respect to objects is, "If you open it,
close it. If you didn't open it, don't close it." This can keep you
out of trouble in other circumstances where it does make a difference.
People failing to follow that rule have caused Microsoft to add special
code to the Close logic of a number of critical objects to ignore
improper calls to close them, but you wouldn't want to get in the habit
of relying on Microsoft to save you from yourself.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)