-
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
-
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
Code:
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
