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]

@FishVal
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]

@FishVal
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]

@NeoPa
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]

@DonRayner
Nothing wrong with that. No reason to expect any more.
@DonRayner
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)

@postman

[postman]

@Denburt
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.

[ChipR]

Did Don's suggestion of manually setting the Form.RecordSource after the .SourceObject and then .Requery not work for you?

[Denburt]

Oh and looking this over again are you sure that when you set your query to retrieve the values from the listbox that you are designating quotes for text and no quotes for numbers # for dates etc.?

[postman]

Sorry, I'm just getting opportunity to get back to this project today....
Don, I will try your suggestion now and post the results soon.

Thank you for all your assistance. It is much appreciated!

[postman]

It's working now...using the original setup I had (see post #4). In the course of implementing Don's solution, I found the root problem:

If both the subform and the parent form's recordsources are pulling data from their respective tables including "ID" fields that are named the same, the subform will not display data.
NOTE: This only happens when programatically switching the subform control's "Sourceobject".

Here are the details....

The 3 tables all have an ID field with the same name "ID" (it's an autonumber field & Primary key on all 3 tables). We'll call them "ParentForm_Tbl", "FormA_Tbl", "FormB_Tbl".

The Parent form's recordsource was set to the table "ParentForm_Tbl".

The Query for the subform's recordsource pulls data from "FormA_Tbl" (or "B"). I had the "ID" field as the criteria (WHERE statement), but also included in the SELECT statement as a field to retrieve data from. I removed it from the SELECT statement, then all worked fine!

So I did some more testing and put it back into the SELECT statement, but renamed the "ID" field in the Subform table to something different (e.g. "IDformA") than the ID field in the Parentform table. It also worked fine!

I don't know why this is happening. I tried recreating the problem by renaming other fields to have the same name in both the Parentform table and Subform tables, but did not have any problem. Maybe it has something to do with the ID fields all being Primary keys.

So, this issue appears solved. I don't understand the problem, but it's kind of a moot point now, I guess.

Thanks again for all your assistance. I'm sure I would not have found the solution without your help!

[NeoPa]

Wow!

Nice post Postie :)

It's great to have such a complicated situation properly answered.