Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-30-2014, 06:53 AM   #1
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Help with SQL pass-through to subform

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
Me.subfrm_REVSelector.Form.RecordSource = Finder
End Sub

businesshippieRH is offline   Reply With Quote
Old 10-30-2014, 07:48 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Help with SQL pass-through to subform

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-30-2014, 07:52 AM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,302
Thanks: 2
Thanked 1,952 Times in 1,909 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Help with SQL pass-through to subform

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):
Quote:
"tbl_Records.TaskOrder, tbl_Records.RecordDateMONTH, tbl_Records.RecordDateYEAR, " & _
"FROM tbl_Records;"

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-30-2014, 08:26 AM   #4
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with SQL pass-through to subform

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 is offline   Reply With Quote
Old 10-30-2014, 08:35 AM   #5
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with SQL pass-through to subform

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.
businesshippieRH is offline   Reply With Quote
Old 10-30-2014, 08:59 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Help with SQL pass-through to subform

Quote:
Originally Posted by businesshippieRH View Post
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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-30-2014, 09:50 AM   #7
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with SQL pass-through to subform

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.

businesshippieRH is offline   Reply With Quote
Old 10-30-2014, 09:55 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Help with SQL pass-through to subform

Can you post the db here?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-30-2014, 10:03 AM   #9
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Re: Help with SQL pass-through to subform

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

Last edited by businesshippieRH; 10-30-2014 at 10:35 AM.
businesshippieRH is offline   Reply With Quote
Old 10-30-2014, 10:15 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Help with SQL pass-through to subform

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
businesshippieRH (10-30-2014)
Old 10-30-2014, 10:37 AM   #11
businesshippieRH
Newly Registered User
 
Join Date: Aug 2014
Posts: 60
Thanks: 17
Thanked 0 Times in 0 Posts
businesshippieRH is on a distinguished road
Thumbs up Re: Help with SQL pass-through to subform

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.
businesshippieRH is offline   Reply With Quote
Old 10-30-2014, 11:41 AM   #12
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Help with SQL pass-through to subform

Happy to help!

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
2467 , pass-through , recordset , sql , subform

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to pass value from one Navigation Subform to another subform abzalali Forms 1 03-29-2014 11:35 PM
Subform Won't Pass Values ih1920 Forms 2 09-27-2011 10:53 AM
pass value from subform to form sjohns35 Forms 1 11-12-2009 06:33 AM
Can noone do this? Pass a value to a subform??? wjmorgan Forms 1 06-04-2004 02:19 PM
Pass Value to Subform crhodus Modules & VBA 6 04-05-2004 01:07 PM




All times are GMT -8. The time now is 06:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World