This is my code for the search now.
The problem is when i only use 1 of the 'IF' commands it works fine but when i use all of em. i think i should change the the search string after the first 'IF' so it says something like this;
txtband.Text & "')"
If Len(album) Then
blntest = True
search = search & " AND"
End If
search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"
would this work??
the current code is below....
Private Sub cmdSearch_Click()
Dim search As String
Dim band As String
Dim blntest As Boolean
Dim album As String
Dim genre As String
Dim cdtype As String
Dim released As String
Dim company As String
Dim serial As String
On Error GoTo damn
band = Trim(txtband.Text)
album = Trim(txtalbum.Text)
genre = Trim(txtgenre.Text)
cdtype = Trim(txttype.Text)
released = Trim(txtreleased.Text)
company = Trim(txtcompany.Text)
serial = Trim(txtserial.Text)
search = "SELECT "
search = search & "CDSERIALNUMBERS.[Band Name], CDSERIALNUMBERS.[Album Name], "
search = search & "CDSERIALNUMBERS.Genre, CDSERIALNUMBERS.[SINGLE/ALBUM], "
search = search & "CDSERIALNUMBERS.RELEASED, CDSERIALNUMBERS.COMPANY, CDSERIALNUMBERS.SERIAL"
search = search & " FROM CDSERIALNUMBERS"
If Len(band) Then 'if length >0
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.[Band Name] = '" & txtband.Text & "')"
If Len(album) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.[Album Name] = '" & txtalbum.Text & "')"
If Len(genre) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.Genre = '" & txtgenre.Text & "')"
If Len(cdtype) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.[SINGLE/ALBUM] = '" & txttype.Text & "')"
If Len(released) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.RELEASED = '" & txtreleased.Text & "')"
If Len(company) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.COMPANY = '" & txtcompany.Text & "')"
If Len(serial) Then
blntest = True
search = search & " WHERE"
End If
search = search & " (CDSERIALNUMBERS.SERIAL = '" & txtserial.Text & "')"
search = search & ";"
Debug.Print search
Adodc1.RecordSource = search
Adodc1.Refresh
damn:
MsgBox ("Something has gone wrong (duh)!")
End Sub
