Help with SQL pass-through to subform

[businesshippieRH]

I have a form (frm_REVFinder) with multiple unbound comboboxes. All of the comboboxes have a row source located in tbl_Records. I am trying to filter a subform (subfrm_REVSelector) using these comboboxes.

I've been trying to get this set up and have the SQL pass-through working. However, I can't seem to get the subform's recordset to work with my pass-through strings.
When I try to run it, I get a Run-time error 2467 "The expression you entered refers to an object that is closed or doesn't exist." On the line with red font. Any help would be much appreciated! My Code:

Option Compare Database

Private Function GetWhere() As String
Dim strTemp As String
'Set Null Arguments for each criteria (Added to SQL WHERE clause "on the fly")
If Not IsNull(Me!cmb_RecordName) Then
    strTemp = strTemp & " AND tbl_Records.RecordName = " & Chr(34) & Me!cmb_RecordName & Chr(34)
    End If
If Not IsNull(Me!cmb_RecordDistinction) Then
    strTemp = strTemp & " AND tbl_Records.RecordDistinction = " & Chr(34) & Me!cmb_RecordDistinction & Chr(34)
    End If
If Not IsNull(Me!cmb_Title) Then
    strTemp = strTemp & " AND tbl_Records.Title = " & Chr(34) & Me!cmb_Title & Chr(34)
    End If
If Not IsNull(Me!cmb_Author) Then
    strTemp = strTemp & " AND tbl_Records.Author = " & Chr(34) & Me!cmb_Author & Chr(34)
    End If
If Not IsNull(Me!cmb_ProjectManager) Then
    strTemp = strTemp & " AND tbl_Records.ProjectManager = " & Chr(34) & Me!cmb_ProjectManager & Chr(34)
    End If
If Not IsNull(Me!cmb_SiteName) Then
    strTemp = strTemp & " AND tbl_Records.[Site Name] = " & Chr(34) & Me!cmb_SiteName & Chr(34)
    End If
If Not IsNull(Me!cmb_ChargeCode) Then
    strTemp = strTemp & " AND tbl_Records.ChargeCode = " & Chr(34) & Me!cmb_ChargeCode & Chr(34)
    End If
If Not IsNull(Me!cmb_ContractNumber) Then
    strTemp = strTemp & " AND tbl_Records.PrimeContractNumber = " & Chr(34) & Me!cmb_ContractNumber & Chr(34)
    End If
If Not IsNull(Me!cmb_TaskOrder) Then
    strTemp = strTemp & " AND tbl_Records.TaskOrder = " & Chr(34) & Me!cmb_TaskOrder & Chr(34)
    End If
    'Set string to add
    strTemp = Mid(strTemp, 6)
    'Make sure not all null
    If Len(strTemp) > 0 Then
    GetWhere = "Where" & strTemp
    End If
End Function

Private Sub btn_Search_Click()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.RecordDateMONTH, tbl_Records.RecordDateYEAR, " & _
"FROM tbl_Records;"
Dim Finder As String
Finder = strSQL & GetWhere()

'Set subform equal to results of query
[COLOR="Red"]Me.subfrm_REVSelector.Form.RecordSource = Finder[/COLOR]
End Sub

 

[pbaldy]

That would need to be the name of the subform control, if different than the subform itself. Also, my gut is you may not be ending up with a space after WHERE.

 

[JHB]

Place a Debug.Print in your code, then have a look at what you get.

 ... 
Finder = strSQL & GetWhere() 
Debug.Print Finder
 ..

Then two things I can see it are wrong, (it could be more):

"tbl_Records.TaskOrder, tbl_Records.RecordDateMONTH, tbl_Records.RecordDateYEAR, " & _
"FROM tbl_Records;"

 

[businesshippieRH]

I think that's where I'm confused... technically, the subform doesn't have a sourceobject (I assume what you mean by control) until I set it to the SQL string. The name of the subform itself is subfrm_REVSelector.

I was missing a space after where. Thanks for catching that.

 

[businesshippieRH]

JHB,

You were right as well. Upon printing (with only my first combobox filled in and the line it is failing on above), I get:

SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, tbl_Records.TaskOrder, tbl_Records.RecordDateMONTH, tbl_Records.RecordDateYEAR FROM tbl_Records WHERE tbl_Records.RecordName = "TADC-CP-2014-0001, Rev. 1.0"

So: The SQL appears to be good now. Thanks!

However, the Run-time error still exists when I add the failing line back in.

 

[pbaldy]

I think that's where I'm confused... technically, the subform doesn't have a sourceobject (I assume what you mean by control) until I set it to the SQL string. The name of the subform itself is subfrm_REVSelector.

The subform is contained within a subform control on the main form. That control may have the same name, it may not. Your code needs the control's name if different.

 

[businesshippieRH]

Please bear with me as this is the first time I have tried to use a subform that wasn't directly tied to a query in access.

I am trying to populate the subform (subfrm_REVSelector) using the sql statement. The subform is on the form (frm_REVFinder). Neither my form nor my subform have a record source (I don't want users editing the table it's based on, just selecting a specific record).

Thanks for helping me try to understand this.

 

[pbaldy]

Can you post the db here?

 

[businesshippieRH]

Here it is. Thanks again for being willing to help with this.

 

[pbaldy]

Ah, you don't actually have a subform, just the subform control. If you look at its properties, you'll see there's nothing in Source Object, which would normally be the name of the form you want to use as the subform. Your code is trying to set the recordsource of that form, and since there is none it errors.

Create a form that displays the fields you want. You probably want it in continuous or datasheet view so you can view multiple records. Put that form's name in the Source Object property and try again. It can start with a blank recordsource so it's empty if you want, but design it with one so you get the fields right.

 

[businesshippieRH]

Thanks again. It works perfectly now... Funny that with the databases I've made, I've never actually created a subform from "scratch"... That being said, until recently forms were considered irrelevant where I work... you just bugged myself or one other guy any time you needed database work done.:banghead:

 

[pbaldy]

Happy to help!