Is it possible....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Myers - 27 Jul 2004 00:40 GMT

Hello,
I need to know, is it possible to do a find or a search in
multiple fields in a table. I've got 4 fields of serial
numbers in my table and I am having to search each field
one at a time. Can I do a search by just highlighting all
4 fields and do a find that way?
Can I create a customized search using a command button to
search all 4 fields?

Thanks
Dan Myers
Transource Computers

Reply to this Message

John Vinson - 27 Jul 2004 03:35 GMT

>Hello,
>I need to know, is it possible to do a find or a search in
[quoted text clipped - 4 lines]
>Can I create a customized search using a command button to
>search all 4 fields?

Two answers:

Yes, you can search DOZENS of fields. In the query grid, you can put a
criterion

[Enter serial number:]

under each of the four serial number fields; put the criterion on a
new line under each field, and Access will use "OR" logic, returning a
record if the serial number entered by the user in response to the
"Enter serial number:" prompt is found in any one of the fields.

Deeper answer:

CONSIDER CHANGING YOUR TABLE STRUCTURE. If you have four serial
numbers per record, you are "committing spreadsheet upon a database" -
a venial sin, with penance consisting of a requirement to read some
good publication on relational theory. <g> Embedding a one to many
relationship within a record IS BAD DESIGN; you would be wise to split
this table into two tables in a one to many relationship, so each
record in this table can be related to zero, one, four... or even five
or six... serial numbers. With this "normalized" design, you can
create a Query joining the two tables and search the single serial
number field.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

RK - 30 Jul 2004 00:05 GMT

Create a command button on your form and add this code:

Private Sub cmdFindRecord_Click()
On Error GoTo Err_cmdFindRecord_Click

' %e = "Search Only Current Field" set to no ; %ha = "Any
Part of Field" ; %n = Set Cusor at first field
' %l{END} = "Changes for current Field to current
Database"
SendKeys "%e%l{END}%ha%n", False

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

Exit_cmdFindRecord_Click:
Exit Sub

Err_cmdFindRecord_Click:
MsgBox Err.Description
Resume Exit_cmdFindRecord_Click

End Sub

Hope this helps
Watch for word wrap in the above code!

RK

>-----Original Message-----
>
[quoted text clipped - 36 lines]
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.

Reply to this Message