Problem with programatically changing subform properties

[postman]

I have a parent form bound to a recordsource and several controls are bound to fields in that recordsource. There are also several unbound controls that are used to display various data in a subform.

It's setup to display "Form A" in the subform control if a selection is made on "List A" (unbound) in the parent form. "Form A" has as its recordsource a Query object which uses the value of "List A" as criteria.

Similarly, if the user clicks a selection on "List B", the subform control's sourceobject changes to "Form B" in the same manner.

If I set the subform's sourceobject manually to "Form B", for example, it will display the data fine. But when the sourceobject is being programatically changed using VBA, then the queries won't retrieve the data.

If I clear out the PARENT form's recordsource, then it works as it's supposed to, but then all the other BOUND controls don't work.

Am I missing something? Any help is much appreciated. Thank you.

[DonRayner]

Hi Postman, can you post back with the code that you are currently using so that we can take a look at it.

Don

[NeoPa]

As Don implies, it's hard to say what's wrong without knowing what you have.

In case it helps, check out Referring to Items on a Sub-Form.

[postman]

Here's the code that's run when the listbox in the parent form is clicked.

Private Sub ListA_Click()
With Me.subformControl
     
     If .SourceObject <> "FormA" Then .SourceObject = "FormA"
     .Form.Requery

End With
End Sub

It works fine, unless the parent form is bound to a recordsource. Not sure why that would interfere, since the listboxes are unbound.

[FishVal]

Check whether Master/Child fields settings are relevant for both FormA and FormB.

[ChipR]

Also, verify that the Data Entry property on the Data tab is set to No. Can't think of anything else at the moment.

[NeoPa]

Check whether Master/Child fields settings are relevant for both FormA and FormB.

Now I've had a chance to look at this in more detail I understand the question a lot better.

With my better understanding I would certainly say that Fish is on the right lines. Can you let us know if this has thrown any light on the matter?

It's hard with a question like this to be able to point you in the right direction as there are so many variables we just don't know. It's the sort of question that is easier handled directly (hands-on).

[postman]

Check whether Master/Child fields settings are relevant for both FormA and FormB.

The only place I see the settings for "Link Master Fields" and "Link Child Fields" is on the subform control itself, not the subforms.

Both settings are blank. I also tried manually setting both to blank using this code:

With Me.subformControl
    .LinkChildFields = ""
    .LinkMasterFields = ""
    .Form.Requery
End With

Still same result.

And the "DataEntry" property is set to 'No' on both subforms.

[DonRayner]

Hmmm, I just whipped up some quick forms using your criteria and code and it seamed to work fine. I then added a query as the record-source to each of the subforms with the criteria based on the appropriate list-boxes, then started noticing your problem.

I fixed it up by setting the subforms record-source directly from the listbox click event.

Private Sub ListA_Click()
Dim stSQL As String
stSQL = "SELECT Table1A.* FROM Table1A WHERE (((Table1A.Field1)=" & Me.ListA & "));"
With Me.subformControl
 
     If .SourceObject <> "FormA" Then .SourceObject = "FormA"
     .Form.RecordSource = stSQL
     .Form.Requery
 
End With
End Sub

This is based on a test db that I use when I need to quickly test something so you will have to change the table and field names. Also the I was using numbers in my listbox. If you are using text you will have to change the SQL to include the quotations.

[NeoPa]

Nice solution Don.

I would just make some changes (again sorry) for reasons I'll explain.

Private Sub ListA_Click()
Dim stSQL As String
With Me.subformControl
    If .SourceObject <> "FormA" Then
        stSQL = "SELECT * " & _
                "FROM Table1A " & _
                "WHERE [Field1]=" & Me.ListA
        .SourceObject = "FormA"
        .Form.RecordSource = stSQL
    End If
End With
End Sub

Everything would be dependent on .SourceObject <> "FormA".
The SQL is mainly changed for layout here, but I also removed redundant table qualifiers and parentheses (often added by Access unnecessarily).
Lastly, if the RecordSource is changed in code, the .Requery is redundant as it gets the data anyway.

I should add that this is not to criticise, as the original is perfectly working code, I just wanted to introduce some considerations.

[DonRayner]

Nice solution Don.

I would just make some changes (again sorry) for reasons I'll explain.

No need to be sorry NeoPa, change away. I did just quickly grab the SQL out of the query that the subform was based on (lazy this evening), I also should have cleaned up the code after I found that my patch to the OP's code worked but I didn't. Thanks for cleaning it up form me.

[NeoPa]

I did just quickly grab the SQL out of the query that the subform was based on (lazy this evening), ...

Nothing wrong with that. No reason to expect any more.

... I also should have cleaned up the code after I found that my patch to the OP's code worked but I didn't.

Actually, I can't agree with that.

I'd hate to put anyone off from posting anything helpful simply because they're too busy to tidy it all up perfectly. It illustrated the point nicely.

It's not like you're expecting someone to decipher your question because you couldn't be bothered to ask it clearly. Now that's a problem ;)

[Denburt]

I'm not 100% (only 99.9) sure and unfortunately I'm a bit busy to check but if you don't have the Child/Master fields set then this would cause the problem you are having. If you want the parent (Main) form to be bound to a recordset then you will need to correlate the Child/Master Fields appropriately even if you did this in VBA when you change subforms.

ctl.LinkChildFields = srcFld(0) & ";" & srcFld(1)
ctl.LinkMasterFields = srcFld(0) & ";" & srcFld(1)

The only place I see the settings for "Link Master Fields" and "Link Child Fields" is on the subform control itself, not the subforms.

Both settings are blank. I also tried manually setting both to blank using this code:

With Me.subformControl
    .LinkChildFields = ""
    .LinkMasterFields = ""
    .Form.Requery
End With

Still same result.

And the "DataEntry" property is set to 'No' on both subforms.

[postman]

I'm not 100% (only 99.9) sure and unfortunately I'm a bit busy to check but if you don't have the Child/Master fields set then this would cause the problem you are having. If you want the parent (Main) form to be bound to a recordset then you will need to correlate the Child/Master Fields appropriately even if you did this in VBA when you change subforms.

ctl.LinkChildFi elds = srcFld(0) & ";" & srcFld(1)
ctl.LinkMasterF ields = srcFld(0) & ";" & srcFld(1)

Even if the subform has no relation to the recordset of the parent form?

[Denburt]

O.K. I just checked ignore my previous post, that shouldn't be a factor I just ran a few quick tests and after rereading your issue I have tried to duplicate it and I seem to be having no problems changing the sub form control from one to another as you described.

It sounds like something may be happening in your code that is interfering with the choice you are making, perhaps something in the oncurrent event or somewhere along the way. The only other thing I can think of is that the form may be corrupt. Try recreating the form and see if it is still an issue.