Search all feilds of a table?

Quote:

Originally posted by paul
Hi I have a database program I have been using seek to find records but i would like to start using SQL to find the records.
I would like to search the entire table for a string that equals TxtSearch.text the string is not always in a feild by itself. How would I do this.Here is some code of what I have been trying to use. this is long but it will give you an idea what ime doing,

Private Sub cmdSeekAID()
rsAID.Index = "PartNumber"
sSeek = UCase(txtSearch.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
AIDPartNumber = ""
AIDDescription = ""
AIDCost = ""
Call SeekAID1
Else
AIDPartNumber = "" & rsAID!PartNumber
AIDDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub

Private Sub SeekAID1()
rsAID.Index = "PartNumber"
sSeek = UCase(txtSearch.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID2
Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = Format(rsAID!Cost, "$#######.00")

AIDList = Format(rsAID!List, "$#######.00")

AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID2()
rsAID.Index = "PartNumber"
sSeek = UCase(Text1.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID3

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID3()
rsAID.Index = "PartNumber"
sSeek = UCase(Text2.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID4

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID4()
rsAID.Index = "PartNumber"
sSeek = UCase(Text3.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID5

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID5()
rsAID.Index = "PartNumber"
sSeek = UCase(Text4.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID6

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID6()
rsAID.Index = "PartNumber"
sSeek = UCase(Text5.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID7

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID7()
rsAID.Index = "PartNumber"
sSeek = UCase(Text6.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
Call SeekAID8

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID8()
rsAID.Index = "PartNumber"
sSeek = UCase(Text7.Text)
rsAID.Seek "=", sSeek
If rsAID.NoMatch Then
AIDPartNumber = ""
AIDDescription = ""
AIDCost = ""
AIDList = ""
Call SeekAID9
Else
AIDPartNumber = "" & rsAID!PartNumber
AIDescription = "" & rsAID!Description
AIDCost = "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF&
AIDDescription.BackColor = &HFFFF&
AIDCost.BackColor = &HFFFF&
AIDList.BackColor = &HFFFF&
End If
End Sub
Private Sub SeekAID9()
rsAID.Index = "PartNumber"
sSeek = UCase(txtSearch.Text)
rsAID.Seek ">=", sSeek
If rsAID.NoMatch Then
AIDPartNumber = ""
AIDDescription = ""
AIDCost = ""
AIDList = ""

Else
AIDPartNumber = "" & rsAID!PartNumber
AIDDescription = "" & rsAID!Description
AIDCost = "" & "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF00
AIDDescription.BackColor = &HFFFF00
AIDCost.BackColor = &HFFFF00
AIDList.BackColor = &HFFFF00
End If
End Sub

Man I hope you type really fast! You need to consolodate all of those routines into a single routine. Anyway, Here is the cheap and easy:

Private Function SeekPart(Part as String) as boolean
'(Assumes that you have opened a database declared globally as db)

Dim rs as RecordSet
Dim strSQL as String

strSQL= "SELECT TableName.* FROM TableName WHERE TableName.PartNumber = " & Part & ";"

Set rs=db.OpenRecordset(txtSQL,dbOpenDynaset)

if rs.RecordCount < 1 then
SeekPart=False
Else
SeekPart=True
AIDPartNumber = "" & rsAID!PartNumber
AIDDescription = "" & rsAID!Description
AIDCost = "" & "$" & rsAID!Cost
'AIDList = "" & rsAID!List
AIDPartNumber.BackColor = &HFFFF00
AIDDescription.BackColor = &HFFFF00
AIDCost.BackColor = &HFFFF00
AIDList.BackColor = &HFFFF00

End if

If you need all of these text boxes (Text1, Text2...)you should use control arrays( Text1(0), Text1(1), Text1(2)...)and pass the index array number to the routine that needs the value. If you want to do a large number of seeks like you appear to be doing then use a Sub to do it for you:

Dim Sub Find1of8Parts()

Dim intX as Integer
Dim boolValid as Boolean
boolValid=False

For intX=0 to 7
boolValid=SeekPart(Text1(intX).Text)
if boolValid then EXIT FOR
next intX

Hope this helps,

Hunter