Solved Recordsource does not exist (1 Viewer)

[ClaraBarton]

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?

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

 

[plog]

I didn't understand a lot of the words you wrote, but I do understand code:

"INNER JOIN strSql " & _

Unless you have a table/query named "strSql", to get the data from the variable strSql into strSQL2 you need to escape out of your string:

"INNER JOIN (" & strSql & ") & _

And just because I like clean code:

            "ON (Contacts.ContactsID = tblSales.ContactsID) " & _
             "AND (Contacts.ContactsID = tblSales.ContactsID) " & _

Those are redundant and you don't need the AND line.

 

[Pat Hartman]

Use one query. This is a case where you need a domain function in a query. Use DCount() to count the number of sales for a customer.

Then you don't need joins to the child tables which will either cause "duplication" or prevent the form from being updateable.

The three options would then be:

Select Case Me.cboOption
    Case "None"
        Select * from qryContacts where SalesCount = 0
    Case "Any"
        Select * from qryContacts where SalesCount > 0
    Case Else
        Select * from qryContacts
End Select

 

[plog]

And if that doesn't do it, let me give you the key to all coding---divide, conquer and verify. Make the problem simpler and make sure what you think you are working with is what you are working with.

Practically, that means you need to find out what is in strSQL2 and strSql by debug.print them so you can see the actual values they hold. Also, make sure the query in strSql produces the results you want--paste it into a query object and verify the results it produces are the results you want. If its broken, the strSQL2 has no chance.

 

[ClaraBarton]

That's where I got it. I made test queries and copied the sql. I appreciate the advice, Plog

 

[ClaraBarton]

Thank you so much, Plog. You steered me in the right direction. This code works great:

Dim strWhere As String
Dim strSql 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(tblSales.SalesID)"
            Case 3 'Contacts with Sales
                strWhere = "(tblSales.SalesID)>" & 0
            End Select

strSql = "SELECT Contacts.* " & _
             "FROM Contacts " & _
             "LEFT JOIN tblSales " & _
             "ON (Contacts.ContactsID = tblSales.ContactsID) " & _
             "WHERE " & strWhere
           
Debug.Print strSql
Me.RecordSource = strSql

 

[Minty]

Just as an aside you do know you can simplify this
& " OR (tblSales.SalesID)>" & 0
to just
& " OR (tblSales.SalesID) > 0 "