ClaraBarton
Registered User.
- Local time
- Today, 08:46
- Joined
- Oct 14, 2019
- Messages
- 461
I have a list form of contacts, some with sales and some without. I'd like to be able to limit this form by option: ALL; With Sales; Without Sales. I do not want the recordset query to have the sales info on it because it limits editability. So... I probably took a simple problem and made it complicated but here is what I have. The problem is it doesn't work. I THINK the problem might be with the last line "strSql.ContactsID" or maybe not. How do I do this?
Code:
Private Sub optSales_Click()
On Error GoTo errHandler
Dim strWhere As String
Dim strSql As String
Dim strSQL2 As String
Select Case Me.optSales
Case 1 'Default--All Contacts
strWhere = "(tblSales.SalesID) Is Null" _
& " OR (tblSales.SalesID)>" & 0
Case 2 'Contacts with no sales
strWhere = "IsNull(qryContacts.SalesID)"
Case 3 'Contacts with Sales
strWhere = "(qryContacts.SalesID)>" & 0
End Select
strSql = "SELECT Contacts.ContactsID, tblSales.SalesID " & _
"FROM Contacts " & _
"LEFT JOIN tblSales " & _
"ON (Contacts.ContactsID = tblSales.ContactsID) " & _
"AND (Contacts.ContactsID = tblSales.ContactsID) " & _
"strWhere"
strSQL2 = "SELECT qryContacts.* " & _
"FROM qryContacts " & _
"INNER JOIN strSql " & _
"ON qryContacts.ContactsID = strSql.ContactsID"
Me.RecordSource = strSQL2