SQL help please - (1 Viewer)

[2RGJ]

Hi All

I am trying to write a database for a friend where a single form can be used to interrogate data across two tables. The form has a subform - Master form shows tbl_llord data whilst subform shows tbl_callout data. Tables are joined one-to-many on client id (llord master table).
The code for the select case statment works for all case 1 -5. Case 6 needs to interrogate and only pull those records where the search criteria = [INV#] on subform. Try as I might, I cannot get this to wrok. I have posted the code below. Can anyone tell me where I am going wrong, and how to correct it please?

Private Sub cmdSrch_Click()
Dim strSQLHead
Dim strSQLWhere
Dim strSQLOrderBy As String
Dim strSQL
Dim strJoin As String
Dim strFilter As String

' MsgBox (Me.txtSrchCriteria.Value)
If IsNull(Me.SearchFrame) Or Me.SearchFrame = "" Then
MsgBox ("Please select Search Option button")
Me.txtSrchCriteria.SetFocus
Exit Sub
End If

strFilter = " OR "
strJoin = " AND "
strSQLHead = "SELECT * FROM tbl_llord "

If Len(Me.txtSrchCriteria & vbNullString) = 0 Then
MsgBox ("Please enter search criteria")
Me.txtSrchCriteria.Value = ""
Me.txtSrchCriteria.SetFocus
Exit Sub
Else
strSQLWhere = "WHERE [sht_cde] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " [pri_cus]= true" _
& " OR [sname] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " [pri_cus]= true" _
& " OR [Add1] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " [pri_cus]= true" _
& " OR [Add2] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " [pri_cus]= true" _
End If

Debug.Print strSQLWhere

Select Case Me.SearchFrame
Case 1 'Priority Search
strSQLWhere = "WHERE [sht_cde] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND[pri_cus]= true" _
& " OR [sname] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= true" _
& " OR [Add1] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= true" _
& " OR [Add2] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= true"
Case 2 ' Non-Priority Search
strSQLWhere = "WHERE [sht_cde] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= false" _
& " OR [sname] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= false" _
& " OR [Add1] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= false" _
& " OR [Add2] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " AND [pri_cus]= false"
Case 3 ' Short Code Search
strSQLWhere = "WHERE [sht_cde] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39)
Case 4 ' Surname Search
strSQLWhere = "WHERE [sname] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39)
Case 5 ' Address Search
strSQLWhere = "WHERE [Add1] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39) _
& " OR [Add2] Like " & Chr$(39) & "*" & _
Me.txtSrchCriteria & "*" & Chr$(39)
Case 6 ' Invoice Number Search
' make sql statement to return invoice number and job details
' also open different form?
strSQLWhere = "WHERE Forms![frmSearchLLordDetails]![frmSearchLLordDetailCallout].Form![Inv#] = " & Chr$(39) & "*" & _
Me.txtSrchCriteria & Chr$(39)