Search function

[Bill]

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

The most common approach for a search is to use an unbound combo box that has
its row source based on the field you want to search in. You use the combo's
After Update event to look for the record and make it the form's current
record.

Let's say we have a form for entering and editing Employee information. We
want to be able to look up an employee by name, but the primary key of the
Employee table is the EmployeeID. So for the combo, we need both fields in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM tblEmployee;

Set the combo properites to hide the ID and Show the name, but search on the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

 

[Bill]

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill

The most common approach for a search is to use an unbound combo box that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's current
record.

Let's say we have a form for entering and editing Employee information.
We
want to be able to look up an employee by name, but the primary key of the
Employee table is the EmployeeID. So for the combo, we need both fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM tblEmployee;

Set the combo properites to hide the ID and Show the name, but search on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do you
want to filter the subform to show all that have an occurance in that field?
--
Dave Hargis, Microsoft Access MVP

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill

The most common approach for a search is to use an unbound combo box that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's current
record.

Let's say we have a form for entering and editing Employee information.
We
want to be able to look up an employee by name, but the primary key of the
Employee table is the EmployeeID. So for the combo, we need both fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM tblEmployee;

Set the combo properites to hide the ID and Show the name, but search on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

That would be an interesting approach. That is, to
create a sub-form that contains only the records
where the sub-string was found. That approach
would be more useful than PF3 to show next
find.
Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill

The most common approach for a search is to use an unbound combo box
that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee information.
We
want to be able to look up an employee by name, but the primary key of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill

The most common approach for a search is to use an unbound combo box
that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee information.
We
want to be able to look up an employee by name, but the primary key of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill

What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo box
that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary key of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

The filtering you have figured out. The way I would use it would be to modify
the subform's row source. It should not require a requery. A form will
requery when you change the row source. You can either create a different
stored query or you can create it in VBA, which ever is easiest for you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill

What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo box
that
has
its row source based on the field you want to search in. You use the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary key of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill

The filtering you have figured out. The way I would use it would be to
modify
the subform's row source. It should not require a requery. A form will
requery when you change the row source. You can either create a different
stored query or you can create it in VBA, which ever is easiest for you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill

What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do
you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo
box
that
has
its row source based on the field you want to search in. You use
the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary key
of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

The same approach will work for either a form or a subform. It is just a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill

The filtering you have figured out. The way I would use it would be to
modify
the subform's row source. It should not require a requery. A form will
requery when you change the row source. You can either create a different
stored query or you can create it in VBA, which ever is easiest for you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record, do
you
want to filter the subform to show all that have an occurance in that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo
box
that
has
its row source based on the field you want to search in. You use
the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary key
of
the
Employee table is the EmployeeID. So for the combo, we need both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill

The same approach will work for either a form or a subform. It is just a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill

The filtering you have figured out. The way I would use it would be to
modify
the subform's row source. It should not require a requery. A form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record,
do
you
want to filter the subform to show all that have an occurance in
that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo
box
that
has
its row source based on the field you want to search in. You
use
the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary
key
of
the
Employee table is the EmployeeID. So for the combo, we need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

Sorry, Bill. Record Source is correct.
It is row source for list boxes and combo boxes.
--
Dave Hargis, Microsoft Access MVP

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill

The same approach will work for either a form or a subform. It is just a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill



The filtering you have figured out. The way I would use it would be to
modify
the subform's row source. It should not require a requery. A form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one record,
do
you
want to filter the subform to show all that have an occurance in
that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one encountered.
Bill



The most common approach for a search is to use an unbound combo
box
that
has
its row source based on the field you want to search in. You
use
the
combo's
After Update event to look for the record and make it the form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the primary
key
of
the
Employee table is the EmployeeID. So for the combo, we need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name, but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

Well Dave, you got me now. The RecordSource for
my form is a query with many fields, not all of which
are displayed but are involved in code references. So,
unlike a combo or list box, I necessarily need to use
"filters" to limit or otherwise control. OR, am I missing
something?

Bill

Sorry, Bill. Record Source is correct.
It is row source for list boxes and combo boxes.
--
Dave Hargis, Microsoft Access MVP

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill

The same approach will work for either a form or a subform. It is just
a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP


:

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill



The filtering you have figured out. The way I would use it would be
to
modify
the subform's row source. It should not require a requery. A form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one
record,
do
you
want to filter the subform to show all that have an occurance in
that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one
encountered.
Bill



The most common approach for a search is to use an unbound
combo
box
that
has
its row source based on the field you want to search in. You
use
the
combo's
After Update event to look for the record and make it the
form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the
primary
key
of
the
Employee table is the EmployeeID. So for the combo, we need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name,
but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Guest]

No, I think maybe I haven't explained enough. Since this is all in a main
form, not a sub form, it may be better to use the form' s filter property.
You can try that in the After Update of the comb box. See the Filter
Property Help in VBA Editor for specific details.

--
Dave Hargis, Microsoft Access MVP

Well Dave, you got me now. The RecordSource for
my form is a query with many fields, not all of which
are displayed but are involved in code references. So,
unlike a combo or list box, I necessarily need to use
"filters" to limit or otherwise control. OR, am I missing
something?

Bill

Sorry, Bill. Record Source is correct.
It is row source for list boxes and combo boxes.
--
Dave Hargis, Microsoft Access MVP

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill


The same approach will work for either a form or a subform. It is just
a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP


:

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill



The filtering you have figured out. The way I would use it would be
to
modify
the subform's row source. It should not require a requery. A form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one
record,
do
you
want to filter the subform to show all that have an occurance in
that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one
encountered.
Bill



The most common approach for a search is to use an unbound
combo
box
that
has
its row source based on the field you want to search in. You
use
the
combo's
After Update event to look for the record and make it the
form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the
primary
key
of
the
Employee table is the EmployeeID. So for the combo, we need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name,
but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill

 

[Bill]

Okay, we're tracking, as that's exactly what I'd done
last night and believed to be the way to go.
Thanks again,
Bill

No, I think maybe I haven't explained enough. Since this is all in a main
form, not a sub form, it may be better to use the form' s filter property.
You can try that in the After Update of the comb box. See the Filter
Property Help in VBA Editor for specific details.

--
Dave Hargis, Microsoft Access MVP

Well Dave, you got me now. The RecordSource for
my form is a query with many fields, not all of which
are displayed but are involved in code references. So,
unlike a combo or list box, I necessarily need to use
"filters" to limit or otherwise control. OR, am I missing
something?

Bill

Sorry, Bill. Record Source is correct.
It is row source for list boxes and combo boxes.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
I'm a little confused by your use of "RowSource" to
effect the change in display. There isn't any "RowSource"
for forms, nor the controls for text-boxes. Do you mean
to change the "RecordSource" for the form?
Bill


The same approach will work for either a form or a subform. It is
just
a
matter of what you want to do.
Hope I was able to help.
--
Dave Hargis, Microsoft Access MVP


:

I see what you mean. I didn't use a sub-form for this
application. Rather, I used the primary form and
simply set the filter and required. That, of course,
changes the contents of the display in accordance
with what was searched. Since a sub-form would
be simple to create, I'll ask the user what their
preference would be between the two approaches.

BTW, I saved your suggestion for general purpose
searches on a single record form for future use.

Thanks,
Bill



The filtering you have figured out. The way I would use it would
be
to
modify
the subform's row source. It should not require a requery. A
form
will
requery when you change the row source. You can either create a
different
stored query or you can create it in VBA, which ever is easiest
for
you.

Me.!MySubformName.Form.Rowsource = ????
--
Dave Hargis, Microsoft Access MVP


:

A little experimentation answered that question, though
it requires:

Me.Filter = "strIn(1,[myfield]," & strShow & ") > 0"

Where strShow is a string variable set upon user's
request to show all records where "MyField" contains
the substring in strShow.

Bill


What would be most meaningful, in this particular
application, would be to provide an unbound
"show" text box where a word or phrase would
be entered and the response would be to filter
the current RecordSource to:

Me.Filter = "strIn(1,myfield,strShow) > 0"

Is that a valid filter expression?

The application already has a "Filters Off" command
button that pertains to other types of filters, so it
wouldn't be anything new to the user.

Bill

It would be similar, but with some differences.
If occurances of the substring are found in more than one
record,
do
you
want to filter the subform to show all that have an occurance
in
that
field?
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Would you use that same approach on a "continuous form"?
The application in mind is one where I want to give the user
the option of finding sub-strings within one of the fields
displayed in the continuous form, e.g., book titles. The
user
would search for a word or phrase that is to be found in
one of the titles, but not necessarily the first one
encountered.
Bill



The most common approach for a search is to use an unbound
combo
box
that
has
its row source based on the field you want to search in.
You
use
the
combo's
After Update event to look for the record and make it the
form's
current
record.

Let's say we have a form for entering and editing Employee
information.
We
want to be able to look up an employee by name, but the
primary
key
of
the
Employee table is the EmployeeID. So for the combo, we
need
both
fields
in
the row source query:

SELECT EmployeeID, EmpLName & ", " & EmpFName As EmpName
FROM
tblEmployee;

Set the combo properites to hide the ID and Show the name,
but
search
on
the
ID.
Bound Column = 1
ColumnWidths = 0"; 2" (or whatever width it takes to show
the
name)
Limit To List = Yes
Column Count = 2


Now, in the After Update event of the combo:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeID] = '" & Me.cboEmployee &
"'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


:

What would be the typical approach to building
a search function?

I would assume that, unless there's a built-in facility
that I've not had an occasion to encounter, that one
would step through a RecordsetClone of a form's
RecordSource using strIn on the field of interest
inclusive of logic that keeps track of the last record
where a match was found and use of a function key
to look for the next match?

(I thought I'd seen somewhere in the past where
VBA had access to the VBA editor's Find function,
but so far I've not found any such reference.)

Thanks,
Bill