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