Help with SQL pass-through to subform (1 Viewer)

businesshippieRH

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
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:
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

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
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

Have been here a while
Local time
Today, 22:52
Joined
Jun 17, 2012
Messages
7,732
Place a Debug.Print in your code, then have a look at what you get.
Code:
 ... 
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

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
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

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
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:

Code:
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

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
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

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
Can you post the db here?
 

businesshippieRH

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
Here it is. Thanks again for being willing to help with this.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:52
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 16:52
Joined
Aug 8, 2014
Messages
60
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:
 

Users who are viewing this thread

Top Bottom