Search all fields on database

[Lacrecen]

hello!~
i have a search button on my form and i need to search all the information inside the "information" table. i have 10 fields.
what i've code is:

adoInfo.RecordSource = "select * from information where name+age+score+birthdate+gender+civilstatus+citizenship+contactnumber+address+email like '%" & txtSearch.Text & "%'"
adoInfo.Refresh
If adoInfo.Recordset.RecordCount = 0 Then
 MsgBox "Data Not Found", vbCritical, "Search Result"
End if

this code works fine but i want to know if there's a shorter code for this. instead of enumerating the fields to search.. is there a shorter code for "search all fields".

[glenn_3]

if txtSearch.Text is freeform then you are stuffed.
you assume (from the code above) that txtSearch.Text follows a specific pattern , ie derived from .... 'name+age+score+birthdate+gender+civilstatus+citizenship+contactnumber+address+email'

what happens if the freeform text isn't in that pattern ? ....
example ... 'birthdate+gender+address+email+age+contactnumber'

recommend you tailor the search capability to match individual fields and construct the query on the fly.

[slavica.k]

i solved this problem by using combo box with a list view property, so i can select the field i wanted to search, and then input the value trough a text box. and finaly get resoult in datagrid view by pressing a button. i had to use two combo boxes, two text boxes and two buttons, since searching integer and string requiers two diferent sintaxes in sql. here is the code, try it! its vb6.

Private Sub Searchnum_Click()
Call clear
If Text6.Text = "" Then
Call Form_Load
Me.Show
Else
Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database1.mdb;Persist Security Info=False"
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
rs.Open ("SELECT * FROM accesstable WHERE " + Me.combnumeric + " = " + Me.Text6 + "")
Set DataGrid1.DataSource = rs
Set Text1.DataSource = rs
  Text1.DataField = "ID"
  Set Text2.DataSource = rs
  Text2.DataField = "fname"
  Set Text3.DataSource = rs
  Text3.DataField = "sname"
  Set Text4.DataSource = rs
  Text4.DataField = "number"
  Set Text5.DataSource = rs
  Text5.DataField = "adress"
  Text6.SetFocus
End If
Text6.Text = ""
Exit Sub
End Sub

Private Sub Searchstr_Click()
Call clear
If Text7.Text = "" Then
Me.Show
Else
Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database1.mdb;Persist Security Info=False"
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
rs.Open ("SELECT * FROM accesstable WHERE " + Me.combstring.Text + " LIKE '" + Me.Text7.Text + "'")
Set DataGrid1.DataSource = rs
Set Text1.DataSource = rs
  Text1.DataField = "ID"
  Set Text2.DataSource = rs
  Text2.DataField = "fname"
  Set Text3.DataSource = rs
  Text3.DataField = "sname"
  Set Text4.DataSource = rs
  Text4.DataField = "number"
  Set Text5.DataSource = rs
  Text5.DataField = "adress"
  Text7.SetFocus
End If
Text7.Text = ""
Exit Sub
End Sub