Why doesnt this work???

[RHY5]

You gotta help me Im Going mental!!

I need to search through a database for a colour but if its not in the first Coloumn I need VB to Check The other 3 can anyone help

What I created is below!!!

Stop laughing!!

Private Sub Command4_Click()
prompt$ = "Enter the full (complete) Carpet Colour."

SearchStr$ = InputBox(prompt$, "Carpet Search")
Data2.Recordset.Index = "colour 1"
Data2.Recordset.Seek "=", SearchStr$
If Data2.Recordset.NoMatch Then

Data2.Recordset.Index = "Colour 2"
Data2.Recordset.Seek "=", SearchStr$
If Data2.Recordset.NoMatch Then

Data2.Recordset.Index = "Colour 3"
Data2.Recordset.Seek "=", SearchStr$
If Data2.Recordset.NoMatch Then

Data2.Recordset.Index = "Colour 4"
Data2.Recordset.Seek "=", SearchStr$
If Data2.Recordset.NoMatch Then


Data2.Recordset.MoveFirst
End If

End If
End If
End If

End Sub

[Mark Sreeves]

I already suspected that you were going mental but thanks for confirming it anyway!


heres what I suggest:

are you listening?

TAKE THE SPACES OUT OF YOUR FILED NAMES

it just complicates things.


use this with the code I posted earlier

Private Function SearchAllFields(strSearch As String) As String
Dim rst As Recordset
Dim strSQL As String
Dim i As Integer
SearchAllFields = "Not Found!"

For i = 1 To 4
  strSQL = "SELECT * FROM table1 WHERE colour" & i & "='" & strSearch & "';"
  Set rst = db.OpenRecordset(strSQL)
  With rst
    If Not .EOF Then
      SearchAllFields = .Fields(i - 1)
      .Close
      Exit For
    End If
  .Close

End With

Next i

Set rst = Nothing

End Function