Find Record

[Adrian]

Hello Group!

I need to incorporate into a Main Form (+ subform) a "Find Button" and a
"TextBox" to find records in
this Form. How can I do this with a Macro function (FindRecord)??????

Thanks a lot!!

 

[Steve Schapel]

Adrian,

The basic principle with using a FindRecord macro is for the focus to be
on a textbox which is bound to your data's primary key field, or some
other unique identifier, and for another control (normally an unbound
textbox or combobox) to have the value of this field for the record you
want to locate.

So, for example, let's suppose you have a form for Customers, and each
customer has a unique CustomerID. In the form header section of the
form, put an unbound combobox where the user can select a customer, and
which will return the CustomerID of the customer whose details you want
to find. Let's say the combobox is named CustomerSelect. So, make a
macro like this...
Action: GoToControl
Control Name: [CustomerID]
Action: FindRecord
Find What: =[CustomerSelect]

You can assign this macro on the After Update event property of the
CustomerSelect combobox.

 

[Guest]

You can use this code
create a button called Search, and enter this code in the on click event.
create a text box called SearchText where the input enered by the user.
create a text box called FieldType where the type of the field will be input
its important for the search
create a text box called GoBackTo where the name of the field in the table
will be entered.
fields GoBackTo, FieldType are visible false.
on the got focus in each field in the sub form assign the value to the text
boxes: FieldType and GoBackTo

pressing the button again will take you to the next record (I hope it will
work)

Private Sub Search_Click()
On Error GoTo Search_Click_err
Dim res
Dim FormMark
Dim Table_fast As Recordset
Dim Crit_fast As String

' check the value enterd
If IsNull(Me![SubFormName].SourceObject) Or IsNull(Me!SearchText) Then
GoTo Exit_Search_Click
End If

Set Table_fast = Me![PR_BrowseMainSub].Form.RecordsetClone

If Not (Me!SearchText = "") Then
Select Case Me![FieldType]
Case "Str"
res = ChkRemark(CStr(Me!SearchText))
Crit_fast = [GoBackTo] & " Like '*" & res & "*'"
Case "Num"
Crit_fast = [GoBackTo] & " = " & Me!SearchText
Case "Date"
res = ChkSpace(CStr(Me!SearchText))
res = " #" & Format(CVDate(res), "mm/dd/yyyy") & "#"
Crit_fast = [GoBackTo] & " Like " & res
End Select
Else
GoTo Exit_Search_Click
End If

If CountSearch = 0 Then
Table_fast.FindFirst Crit_fast
CountSearch = 1
Else
Table_fast.FindNext Crit_fast
End If

If Not Table_fast.NoMatch Then
FormMark = Table_fast.Bookmark
Me![PR_BrowseMainSub].Form.Bookmark = FormMark
End If

Exit_Search_Click:
Exit Sub

Search_Click_err:
MsgBox Error
Resume Exit_Search_Click
Resume
Exit Sub

End Sub

 

[DL]

What couldn't you wait?

 

[Adrian]

Dear Steve,

Thanks for reply

I understand what you explain me, but I am not able to do it to work this way.

Punctually what I need is this:

1- One TextBox
2- One Command Button

Thanks a lot!

Adrian.-

--------------------------------------------------------------------------------

Adrian,

The basic principle with using a FindRecord macro is for the focus to be
on a textbox which is bound to your data's primary key field, or some
other unique identifier, and for another control (normally an unbound
textbox or combobox) to have the value of this field for the record you
want to locate.

So, for example, let's suppose you have a form for Customers, and each
customer has a unique CustomerID. In the form header section of the
form, put an unbound combobox where the user can select a customer, and
which will return the CustomerID of the customer whose details you want
to find. Let's say the combobox is named CustomerSelect. So, make a
macro like this...
Action: GoToControl
Control Name: [CustomerID]
Action: FindRecord
Find What: =[CustomerSelect]

You can assign this macro on the After Update event property of the
CustomerSelect combobox.

--
Steve Schapel, Microsoft Access MVP

Hello Group!

I need to incorporate into a Main Form (+ subform) a "Find Button" and a
"TextBox" to find records in
this Form. How can I do this with a Macro function (FindRecord)??????

Thanks a lot!!

 

[Adrian]

I do not understand your irritation, almost I can express myself in English,
So, accept my apologyes if I said something offensive!!

 

[Steve Schapel]

Adrian,

I expect the method I outlined would work in the way you want. But I
would need a lot more information to be able to give more specific help.
What data does the form carry, what data (field?) is the basis of the
"search", what do you imagine goes into the "One Textbox", etc.

 

[Steve Schapel]

Adrian,

Ok, but what is it supposed to do? Enter 12345 in the Texto1 textbox,
click the 'Search Numbers' button, and then what? I had assumed that
you wanted to go to a particular record on the form, based on the number
entered, but now it appears I may have misunderstood. Can you give an
example of the behaviour you want this to produce? Open a form, yes?
With what data? And then what?

 

[Adrian]

Steve,

I will attempt being more clear
In two first expositions I explain that I wish to do....

I need to incorporate into a Main Form (that contain a subform) a "Command
Button" and a
"TextBox" to find records in this Form. How can I do this with a Macro
function (FindRecord)??????


You relate EXACTLY I need ......Enter 12345 in the Texto1 textbox,
click the 'Search Numbers' button, and then I want to go to a particular
record on the MAIN form, based on the number
entered ......

And the same thing with the others buttons + TextBox ......with other kind
of Data Type (Text, Memo, Numbers, etc)

Thanks in Advance!

 

[Steve Schapel]

Adrian,

Which field should contain the 12345 value for the record selection?

 

[Adrian]

Hi

"12345" is just an example of a numeric field type ..........

The field containing this value name is for example "CustomerNumber"

 

[Steve Schapel]

Adrian,

Ok, so that's what I thought. So, going back to my original answer, the
macro on the Click event of the Search Numbers command button will be
like this...
Action: GoToControl
Control Name: [CustomerNumber]
Action: FindRecord
Find What: =[Texto1]

 

[Adrian]

Steve, Thanks a lot!!! Work Perfect!!

What it would happen if, instead of searching in a only one field, I want to search at all of the fields of the Form?

Where can I read be more than enough how do we modify those parameters?

Thanks!

Adrian,

Ok, so that's what I thought. So, going back to my original answer, the
macro on the Click event of the Search Numbers command button will be
like this...
Action: GoToControl
Control Name: [CustomerNumber]
Action: FindRecord
Find What: =[Texto1]

--
Steve Schapel, Microsoft Access MVP

Hi

"12345" is just an example of a numeric field type ..........

The field containing this value name is for example "CustomerNumber"

 

[Steve Schapel]

Adrian,

You can't really use this same FindRecord approach if you are looking
for the search data in more than one field. If this was your
requirement, I would consider doing it by making another form, in
continuous view, to list the records (as there may be more than one?)
that have the search text in more than one field. Then you could use an
OpenForm action in the macro on your button, and in the Where Condition
argument of the OpenForm action, the equivalent of something like this...
[OneField] Like "*" & [Forms]![YourFormName]![Texto2] & "*" Or
[AnotherField] Like "*" & [Forms]![YourFormName]![Texto2] & "*"
That's if you want the records returned to *contain* the search data in
the dwesignmated fields. If you want an exact match, then it would be
morwe like this...
[OneField]=[Forms]![YourFormName]![Texto2] Or
[AnotherField]=[Forms]![YourFormName]![Texto2]

 

[Adrian]

Steve. thanks a lot for your help!!!!
Hugs

Adrian.-

Adrian,

You can't really use this same FindRecord approach if you are looking for
the search data in more than one field. If this was your requirement, I
would consider doing it by making another form, in continuous view, to
list the records (as there may be more than one?) that have the search
text in more than one field. Then you could use an OpenForm action in the
macro on your button, and in the Where Condition argument of the OpenForm
action, the equivalent of something like this...
[OneField] Like "*" & [Forms]![YourFormName]![Texto2] & "*" Or
[AnotherField] Like "*" & [Forms]![YourFormName]![Texto2] & "*"
That's if you want the records returned to *contain* the search data in
the dwesignmated fields. If you want an exact match, then it would be
morwe like this...
[OneField]=[Forms]![YourFormName]![Texto2] Or
[AnotherField]=[Forms]![YourFormName]![Texto2]

--
Steve Schapel, Microsoft Access MVP

Steve, Thanks a lot!!! Work Perfect!!
What it would happen if, instead of searching in a only one field, I
want to search at all of the fields of the Form?

Where can I read be more than enough how do we modify those parameters?

Thanks!

 

[Larry Linson]

I do not understand your irritation,
almost I can express myself in English,
So, accept my apologyes if I said
something offensive!!

Don't be offended by DL. His "What couldn't you wait?" is meaningless in
English, too. There are many meaningless posts in newsgroups, and my advice
is "just ignore them". For good suggestions on effective use of newsgroups,
see the FAQ at http://www.mvps.org/access/netiquette.htm.

I did not have any trouble understanding what you were asking. Your English
is much easier to understand than my Spanish would be.

Larry Linson
Microsoft Access MVP

 

[Guest]

i'm intrested in your discussion can you help me how to use textbox/combobox
with command button instead of findrecord button to search for data/record.

Adrian,

You can't really use this same FindRecord approach if you are looking
for the search data in more than one field. If this was your
requirement, I would consider doing it by making another form, in
continuous view, to list the records (as there may be more than one?)
that have the search text in more than one field. Then you could use an
OpenForm action in the macro on your button, and in the Where Condition
argument of the OpenForm action, the equivalent of something like this...
[OneField] Like "*" & [Forms]![YourFormName]![Texto2] & "*" Or
[AnotherField] Like "*" & [Forms]![YourFormName]![Texto2] & "*"
That's if you want the records returned to *contain* the search data in
the dwesignmated fields. If you want an exact match, then it would be
morwe like this...
[OneField]=[Forms]![YourFormName]![Texto2] Or
[AnotherField]=[Forms]![YourFormName]![Texto2]

--
Steve Schapel, Microsoft Access MVP

Steve, Thanks a lot!!! Work Perfect!!


What it would happen if, instead of searching in a only one field, I
want to search at all of the fields of the Form?

Where can I read be more than enough how do we modify those parameters?

Thanks!