Unable to set RecordSource for a subform

[AvGuy]

I have a form that incorporates a subform space. The subform space is occupied by different subforms depending on the data that is to be displayed. The subform space is named SubFormSpace and the different subforms are connected to the SubFormSpace by setting the SourceObject property thusly:

Me!SubFormSpace.SourceObject = "Subform1"

This part works fine.

The data to be displayed on the selected Subform is delivered by certain queries that are referenced by setting the record source of the SubFormSpace object, thusly:

Me!SubFormSpace.Form.RecordSource = Me!PickQuery where PickQuery is the name of a stored query shown in the PickQuery combo box.

This code works fine in Access97, but totally crashes in Access 2002. It seems the processor can't find the SubFormSpace to assign a RecordSource to it even though it finds it just fine with ?Me!SubFormSpace.Form.Name

I suppose it's just a syntax thing, but I'm not having any luck trying to find something acceptable to A2002.

Any ideas?

AvGuy

 

[THWatson]

AvGuy
Have you tried...
Me.Form!SubFormSpace.RecordSource = etc.

Or...
Forms!YourFormName.Form!SubFormSpace.RecordSource = etc.

Tom

 

[ZmrAbdulla]

Try
Me!PickQuery.Value
or
Forms!MainFormName!PickQuery.Value

or to check the Combobox's bound column

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[AvGuy]

Me.Form!SubFormSpace.RecordSource = etc.
and
Forms!YourFormName.Form!SubFormSpace.RecordSource = etc.

"Object doesn't support this property or method"
So, no joy here.

Me!PickQuery.Value
or
Forms!MainFormName!PickQuery.Value

The combo box isn't the problem. The bound field is just a number that gets concatenated with a word and they together form the name of a query, i.e. Me!PickQuery = 1
And ???.RecordSource = "Query" & Me!PickQuery so that the record source becomes the query named "Query1" Like I say, it works fine in Access 97. It could be just something that Microsoft removed when they "improved" Access to 2000 and later.

Thanks anyway,
AvGuy

 

[ZmrAbdulla]

Both these codes work

Private Sub Combo1_AfterUpdate()
    Me.MySubFormObject.Form.RecordSource = Me.Combo1
    Me.MySubFormObject.Form.Requery
End Sub

Private Sub Combo1_AfterUpdate()
    Me.MySubFormObject.Form.RecordSource = Me.Combo1.Column(0)
    Me.MySubFormObject.Form.Requery
End Sub

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[AvGuy]

Translating your code to my objects:
Me.SubFormSpace.Form.RecordSource = Me.PickQuery
and
Me.SubformSpace.Form.RecordSource = Me.PickQuery.Column(0)

give the following error:
"The expression you entered refers to an object that is closed or doesn't exist"

I'll keep trying.
AvGuy

 

[ZmrAbdulla]

RecordSource = "Query" & Me!PickQuery so that the record source becomes the query named "Query1"

I missed that part. Also tried something like this also working. Only chance I can think is you are calling the recordsource when you have a different subform is present.

Private Sub Combo1_AfterUpdate()
    Me.MySubFormObject.Form.RecordSource = "Query" & Me.Combo1
    Me.MySubFormObject.Form.Requery
End Sub

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[AvGuy]

As I mentioned in one of these messages, the following delivers the correct subform name:

?Me!SubFormSpace.Form.Name

But when I try to set the RecordSource, Access can't find it.

AvGuy

 

[THWatson]

AvGuy
There's a hitch in here somewhere, so try this...

Copy the SQL from one of the queries you are trying to get the subform to access. Then make the record source for the subform that SQL, and see if it works. If it does, then the issue is finding the way to get to the query from which that SQL comes. If that doesn't work, then the problem lies elsewhere.

The expression would look something like...

Dim strSQL As String
strSQL = "SELECT your fields " _
& "FROM your table or query " _
& "WHERE clauses if you need them " _
& "ORDER BY whatever fields you want to order by;"
Forms!YourFormName.Form!SubFormSpace.RecordSource = strSQL

If this works, you could set the subform record source dynamically, by putting different code behind each of several option buttons.

Anyway, still trying to get to the bottom of what is causing the difficulty.

Tom

 

[AvGuy]

Thanks for your response. The problem does not lie in the query itself - the process never gets that far. The RecordSource for the subform is supposed to reference the query name as the data source. That's the part that's failing; I can't dynamically set the RecordSource to the query name.

The process in concept is really quite simple. Suppose you have 10 different queries that you want to use as RecordSource for a subform. Number the queries 1 through 10. Set a combo box with a description of each query and the numbers 1 through 10 bound to the control. The user selects a query description, the query number is then set as the value of the combo control. The word "Query" is concatenated with the combo box value to yield the name of the stored query, e.g. Query1, Query2, etc. You then set the subform RecordSource to this value:

Me!SubFormSpace.Form.RecordSource = "Query" & Me!combobox

The process in reality is slightly more complicated because I need to use different subform structures. That's why a subform space is used because the actual subform is set dynamically depending on the structure of the information to be delivered. That, however, is just a different wrinkle since the problem would exist if even only one subform structure were being used. I add this only to explain why I'm using a subform space rather than a subform itself.

It works just fine in Access97 and I've used it many times in many applications. It just doesn't work in A2002. The question is why and what, if anything, can be done about it?

AvGuy

 

[THWatson]

AvGuy
I'm more or less hitting a blank wall here.

However, it seems to me the problem lies with the subform structures not being identical in terms of the SourceObject.

What I did was create a form with 2 option buttons, and then a subform without a record source. The option buttons referenced separate queries in order to dynamically create the RowSource for the subform. I was able to click back and forth with no trouble.

When I attempted to do this same thing with a blank subform (which I assume your subform space is) I ended up with an error message that the source wasn't open or wasn't available.

I don't know of anything else to try.
Tom

 

[AvGuy]

Tom:
Yeah, that's what I'm running in to also. The Subform space has to be empty because more than one subform is used depending on the data to be displayed. The subform is set to the space by:

Me!SubFormSpace.SourceObject = "Subform1"

This part works OK. It's the next step of trying to assign a datasource to the subform where it crashes. I'll keep messing with it until I get it to work or figure out some other approach. If I do get it to work I'll post another message to this thread.

Thanks for all your time and effort at looking into this - it's appreciated.

Avguy

 

[THWatson]

AvGuy
Are you saying that you can get different subforms to show up in the blank subform space?

Seems to me that if a subform will show up without a record source...probably showing #Name errors etc. in place of the fields...you should be able to set its record source through SQL.

Tom

 

[AvGuy]

Well, yes and no. If I start with a subform already in the SubFormSpace object then queries that deliver data to that subform work fine. If I invoke a query that uses a different subform then the routine fails. Same if I start with the SubFormSpace vacant. However, the debug command:

?Me!SubFormSpace.SourceObject

Gives me the correct name of the subform I want to see in the space, but the debug command of:

?Me!SubFormSpace.Form.name is illegal so I can't really tell if the subform is properly referenced as the occupant of the space.

On the form itself starting with a subform already assigned to the space shows the data fields when viewed in form view. Attempt to switch subforms and only blank shows up. So it now appears that what is failing is the assignment of the subform to the space notwithstanding the ?Me!SubFormSpace.SourceObject command results.

At least this is some progress as the error now seems to be ahead of where I thought it was.

I'll keep plugging.

AvGuy

 

[AvGuy]

Problem solved; boy do I feel stupid.

The subforms assigned to the SubFormSpace each had an entry on the RecordSource line. I just figured Access was smart enough to change this; apparently not. If I remove the RecordSource entries from the subforms leaving that line blank then the entire process works fine.

Well, live and learn. Thanks to all who contributed.

AvGuy

 

[THWatson]

AvGuy
Hey, you got it !!

That's the solution I was working up to. Because I constructed my model with a blank record source, allowing Access to populate depending upon the SQL that was invoked.

In any event, the problem is solved. The needle in the haystack has been located.

Tom