Setting RecordSource for Subform Error

[Wolfroolz]

I am trying to create a dynamic sql query. I have created a search form with many different text boxes that a user can choose to search by, and when they click search a subform comes up with the results. However when I try to set the record source of the subform I get an error 438, Object doesn't support this property or method. Any help would be appreciated. I have created the subform frame, Named SearchQuerySubform1, and my subform is SearchQuerySubform, I have tried a few different methods to set the subform, but they all error out.

Sub newsqlsearch()
Dim sqlSelect As String
Dim sqlFrom As String
Dim sqlWhere As String
Dim sqlSort As String
Dim sqlSearch As String
Dim sqllength As Integer

sqlSelect = "SELECT Requests.[CGERTicket], Items.Status, Items.Description, Items.[BudgetType], Items.[BudgetYear], Items.[UnitPrice], Items.Quantity, " & _
                " Items.Manufacturer, Items.[Model], Items.RequisitionNumber, Items.ReqDate, Items.DeliveryDate, Items.Vendor, Items.Notes, Items.PONumber, " & _
                " Items.PODate, Items.Buyer, Items.[CPRNum], Items.ReceiptedAmount, Items.ReceiptedDate, Items.ItemType, Items.SpecialProject, Items.Comments, Requests.Organization, " & _
                " Requests.Department, CheckTagged.[CountOfCPR], Requests.[RequesterName], Requests.[EndUserName], Requests.[RequestDate], Requests.[Section], Requests.Attention, " & _
                " Requests.[BudgetCoordinator], Requests.Location, Requests.[MDSCode], Requests.RequestLink, Items.ID, Left(Items.[CPRNum],7) AS ShortCPR, " & _
                " Left(Items.[Description],200) AS ShortDesc "
                
sqlFrom = "FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) INNER JOIN CheckTagged ON Items.ID = CheckTagged.ID "
sqlSort = "ORDER BY Requests.[CGERTicket] DESC"
sqlWhere = ""
If IsNull(Me.BudgetYear) = False Then sqlWhere = sqlWhere & " and ((Items.[BudgetYear]) Like [Forms]![SearchForm]![BudgetYear])"
If IsNull(Me.BudgetType) = False Then sqlWhere = sqlWhere & " and ((Items.[BudgetType]) Like [Forms]![SearchForm]![BudgetType])"
If IsNull(Me.RequestID) = False Then sqlWhere = sqlWhere & " and ((Requests.[CGERTicket]) Like [Forms]![SearchForm]![RequestID])"
If IsNull(Me.ReqNum) = False Then sqlWhere = sqlWhere & " and ((Items.[RequisitionNumber]) Like [Forms]![SearchForm]![ReqNum])"
If IsNull(Me.PoNum) = False Then sqlWhere = sqlWhere & " and ((Items.PONumber) Like [Forms]![SearchForm]![PoNum])"
If IsNull(Me.Desc) = False Then sqlWhere = sqlWhere & " and ((Items.Description) Like ""*"" & [Forms]![SearchForm]![Desc] & ""*"") "
If IsNull(Me.Model) = False Then sqlWhere = sqlWhere & " and ((Items.[Model]) Like ""*"" & [Forms]![SearchForm]![Model] & ""*"") "
If IsNull(Me.Manuf) = False Then sqlWhere = sqlWhere & " and ((Items.Manufacturer) Like ""*"" & [Forms]![SearchForm]![Manuf] & ""*"") "
If IsNull(Me.Vendor) = False Then sqlWhere = sqlWhere & " and ((Items.Vendor) Like ""*"" & [Forms]![SearchForm]![Vendor] & ""*"") "
If IsNull(Me.ItemType) = False Then sqlWhere = sqlWhere & " and ((Items.ItemType) Like [Forms]![SearchForm]![ItemType])"
If IsNull(Me.SpecProj) = False Then sqlWhere = sqlWhere & " and ((Items.SpecialProject) Like [Forms]![SearchForm]![SpecProj])"
If IsNull(Me.Org) = False Then sqlWhere = sqlWhere & " and ((Requests.Organization) Like [Forms]![SearchForm]![Org])"
If IsNull(Me.Dep) = False Then sqlWhere = sqlWhere & " and ((Requests.Department) Like [Forms]![SearchForm]![Dep])"


sqllength = Len(sqlWhere)
sqlWhere = "WHERE (" & Right(sqlWhere, (sqllength - 5)) & ")"

sqlSearch = sqlSelect & sqlFrom & sqlWhere & sqlSort & ";"


Forms![searchform]![SearchQuerySubform1].SourceObject = "SearchQuerySubform"
Forms![searchform]![SearchQuerySubform1].SearchQuerySubform.RecordSource = sqlSearch
'Forms![searchform]![SearchQuerySubform1].RecordSource = sqlSearch
'Me.SearchQuerySubform.Form.RecordSource = sqlSearch

 

[theDBguy]

Hi. Just guessing here but try adding Form to your syntax. For example:

Forms!searchform.SearchQuerySubform1.Form.SourceObject=

Hope that helps...

 

[Wolfroolz]

Thanks, the source object seems to set correctly, its when I try to set the record source that I have the error.

I tried
Forms![searchform]![SearchQuerySubform1].SearchQuerySubform.Form.RecordSource = sqlSearch
Forms![searchform]![SearchQuerySubform1].Form.RecordSource = sqlSearch

and those both error out as well.

When I do a print out of the sqlsearch in the immediate window, I get this, which looks correct to me.

SELECT Requests.[CGERTicket], Items.Status, Items.Description, Items.[BudgetType], Items.[BudgetYear], Items.[UnitPrice], Items.Quantity,  Items.Manufacturer, Items.[Model], Items.RequisitionNumber, Items.ReqDate, Items.DeliveryDate, Items.Vendor, Items.Notes, Items.PONumber,  Items.PODate, Items.Buyer, Items.[CPRNum], Items.ReceiptedAmount, Items.ReceiptedDate, Items.ItemType, Items.SpecialProject, Items.Comments, Requests.Organization,  Requests.Department, CheckTagged.[CountOfCPR], Requests.[RequesterName], Requests.[EndUserName], Requests.[RequestDate], Requests.[Section], Requests.Attention,  Requests.[BudgetCoordinator], Requests.Location, Requests.[MDSCode], Requests.RequestLink, Items.ID, Left(Items.[CPRNum],7) AS ShortCPR,  Left(Items.[Description],200) AS ShortDesc
FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) INNER JOIN CheckTagged ON Items.ID = CheckTagged.ID
WHERE (((Items.[BudgetYear]) Like [Forms]![SearchForm]![BudgetYear]) and ((Items.[BudgetType]) Like [Forms]![SearchForm]![BudgetType]) and ((Items.Description) Like "*" & [Forms]![SearchForm]![Desc] & "*") )
ORDER BY Requests.[CGERTicket] DESC;

 

[Deleted member 147267]

Object doesn't support this property or method.

The error suggests you are trying to set a property or method that the object doesn't have in either of those collections. I would have thought one of the syntaxes in your 2nd post would be OK but without knowing the names of your form or subform control it's not possible to comment more on that.

 

[arnelgp]

you may also need to Debug the code to find out on which line you got the error.

 

[Wolfroolz]

The code breaks on any of the attempts to set the record source. Runs fine until then.

The name of the main form is SearchForm
The subform is SearchQuerySubform
I call it a frame, but I guess it might be the control, but the holder of the subform on the mainform is SearchQuerySubform1 and its unbound, that is why I am setting the source object first.

 

[Deleted member 147267]

Based on that info, this should have worked and you have it in a post:
Forms![searchform]![SearchQuerySubform1].Form.RecordSource = sqlSearch

However, I don't see that in the code your posted. Rather, I see

Forms![searchform]![SearchQuerySubform1].SearchQuerySubform.RecordSource = sqlSearch
'Forms![searchform]![SearchQuerySubform1].RecordSource = sqlSearch
'Me.SearchQuerySubform.Form.RecordSource = sqlSearch

And the error is raised at the line that attempts to set the subform recordsource property, yes? Then if the line I posted doesn't work, double check name spelling because you are correct that the container ("frame") for the subform is a control - a subform control. If that line doesn't work and you find no spelling error, then I would rem out the line that sets the recordsource and allow the forms to open, then go to the immediate window and start digging into properties of the form and subform. Checking properties like Name will tell you if the object is loaded, you have the hierarchy correct in your mind, and if in fact their names are what you think they are.

Lastly, that code is being called by a button click and it exists in the main form module?

 

[Wolfroolz]

Correct, there is a search button on the main form.
The line it breaks at is:
Forms![SearchForm]![SearchQuerySubform1].Form.RecordSource = sqlSearch

When I use that code I get a different error. Run Time Error 2467, the expression you entered refers to an object that is closed or doesn't exist.

I copied and pasted directly from the control, and SearchForm is definitely the name of the main form.

 

[Deleted member 147267]

Well I don't know where you're at now. You originally posted

However when I try to set the record source of the subform I get an error 438, Object doesn't support this property or method.

If you could post a db copy I suspect it would eliminate a lot of uncertainty and guesswork.

 

[Wolfroolz]

See attached. The main page opens, click on search, If you enter a year of 2020 and a description of unitec it should return a record, however it errors out. I had to recombine a split database and cannibalize the whole thing to get it to fit.

With some of the attempts to set the recordsource I get the first error, with the new code I was given I got the second error.

 

[Deleted member 147267]

You're trying to open a form that has an invalid recordsource (sqlSearch). I would have thought that this would raise an error but I guess it just refuses to open, thus you get the message that the source object isn't open or doesn't exist. Because of the invalid source, all the controls in the subform have errors as they're bound to fields that don't exist. Assuming you simply renamed the query at some point, I tried changing the source name to sqlSearch1, but discovered that this query only has one field.

If you try to open this subform from the nav pane, it will not open and will raise an error message. So why no error raised in your code? I see that you are turning warnings on and off but have no error handlers in those subs. Thus when Access encounters an error afterwards, it just carries on if it can. It seems to me that you need to at least fix the invalid recordsource and make sure that the controls are bound to the correct fields so that at least things will open before trying to swap out sources. I don't know if your intent is to swap the subform recordsource property value as well. I hope not.

 

[Wolfroolz]

sqlSearch = sqlSelect & sqlFrom & sqlWhere & sqlSort & ";"

sqlsearch is the variable that is defined in the above code. I thought I should be able to set a sql query as defined in the code as the record source, am I wrong? There is no separate query called sqlsearch

As previously posted before it breaks the print out for sqlsearch is
SELECT Requests.[CGERTicket], Items.Status, Items.Description, Items.[BudgetType], Items.[BudgetYear], Items.[UnitPrice], Items.Quantity, Items.Manufacturer, Items.[Model], Items.RequisitionNumber, Items.ReqDate, Items.DeliveryDate, Items.Vendor, Items.Notes, Items.PONumber, Items.PODate, Items.Buyer, Items.[CPRNum], Items.ReceiptedAmount, Items.ReceiptedDate, Items.ItemType, Items.SpecialProject, Items.Comments, Requests.Organization, Requests.Department, CheckTagged.[CountOfCPR], Requests.[RequesterName], Requests.[EndUserName], Requests.[RequestDate], Requests.[Section], Requests.Attention, Requests.[BudgetCoordinator], Requests.Location, Requests.[MDSCode], Requests.RequestLink, Items.ID, Left(Items.[CPRNum],7) AS ShortCPR, Left(Items.[Description],200) AS ShortDesc
FROM (Requests INNER JOIN Items ON Requests.[CGER Ticket] = Items.[CGER Ticket]) INNER JOIN CheckTagged ON Items.ID = CheckTagged.ID
WHERE (((Items.[BudgetYear]) Like [Forms]![SearchForm]![BudgetYear]) and ((Items.[BudgetType]) Like [Forms]![SearchForm]![BudgetType]) and ((Items.Description) Like "*" & [Forms]![SearchForm]![Desc] & "*") )
ORDER BY Requests.[CGERTicket] DESC;

 

[Deleted member 147267]

sqlsearch is the variable that is defined in the above code.

It is more than just that. Did you try opening your subform from the nav pane?

 

[Wolfroolz]

Thanks for the help Micron.
I originally thought I would create a static query, which is why the record source was set to that, but then I realized it would have to be a dynamic query. I didn't bother to change the record source because I figured that I was setting it in the code. Appreciate any advice you could offer. I have been asked to upgrade the database to make it more efficient and one of the slowest things is the search screen, which is why I am trying to build it dynamically.

 

[Deleted member 147267]

What is dynamic about the query, the criteria or the fields - or both? If just the criteria, you can provide the criteria from the form controls. Or you can rewrite the entire sql in code and assign it to the sql property of that query before using it. Or you can have completely unbound controls and write the sql in code and run it. There are probably other things not coming to mind at the moment, but whatever you do with respect to modifying sources is that you obviously can't have bound controls on a form and start swapping out fields in their underlying recordsource. You also need to worry about when the user doesn't enter info in controls and then your query is referencing them. Usually it results in no records. That's why I usually build the sql according to the controls that have valid data in them.

Does this give you any sort of guidance?

Microsoft Access tips: Search criteria

Free sample database for Microsoft Access 2000 and later, demonstrating how to build criteria from many optional entries, and handle different field types, exact matches, partial matches, and ranges.

allenbrowne.com