Linking Subforms - urgent please help!

[JamesMcS]

Hi guys

I've got a bit of a problem. I've got a subform in continuous view, showing manufacturers. I've made it so that double clicking on a manufacturer changes the source object of the form to the "detail" subform. In Access 2003, I used a text box to hold the manufacturer number, so that when the subform's source object changed it could still link on the mfr number. It worked OK - the text box held the value between subform changes, and everything was groovy.

Now I'm using Access 07. Annoyingly, the text box becomes blank during the move from one subform to the other, and the "detail" subform is blank becuase there's nothing to link to. When going back to the main maunfacturer subform, the text box is repopulated and everything is fine.

Help!! I could do with an urgent solution as my boss is expecting to see this in an hour and a bit....

 

[vbaInet]

Double-clicking to change the source object happens in the subform or on the main form?

 

[JamesMcS]

It's on the subform, a double click event on one of the text boxes to say change the source object to the "detail" form.

 

[vbaInet]

Not a good idea to do. I'm not sure if you're changing the source object of the main form or the subform? If it's the subform, try:

1. Setting focus to the main form
2. Change the source object.
3. Set focus back to the control.

Have a look at this link. The last 3 columns on the table are relevant:
http://www.mvps.org/access/forms/frm0031.htm

 

[JamesMcS]

That's an incredibly handy sheet, now sitting in my favourites so ta for that! I tried the setfocus method but it doesn't work, the text box still goes blank... Let me explain a little less un-clearly:

Initially the subform in continuous view shows a list of manufacturers, let's call it "MainSub". Double clicking on the manufacturer name (a text box) changes the source object of the subform to the "detail" form. Both MainSub and Detail link to the parent form through an intermediary text box on the parent form, with its controlsource set to MainSub.manufacturer, so that when I click on any of the manufacturers in the MainSub, the value of the text box changes to match.

In Access 03, when double clicking a MainSub.manufacturer to change the subform to "detail", the parent form intermediary text box would remain populated, so that the "detail" form would have something to link to. Now I've "up"graded to 07, the text box goes blank (I presume because "Mainsub" has closed, nothing for it to look at) and "detail" subform is also blank.

Clear as mud?

 

[vbaInet]

Am I correct to think that Detail form is a subform and MainSub is a subform within Detail?

Show me the code of how you're setting the source object?

You could also try refreshing the subform after setting the source object.

 

[JamesMcS]

Ah sorry - they're two separate entities - I should be better at describing things - basically there's a parent form, and a subform/subreport object therein. Initially the subform object's source object is "MainSub" but on the double click event the source object changes to "Detail". In both cases the link is manufacturer, which is stored in the intermediary text box on the parent form - the text box's value is set by me selecting one of the records in Mainsub, and obviously changes every time I select another record in mainsub.

So the problem is the text box - it doesn't keep its value after the double-click-to-change-source-object, as it did in A2003. Grrrr!

Code wise it's literally just "form!subform object.sourceobject="Detail"". This bit works fine still, it's just the bleedin text box going blank! Re-Grrrrr!

 

[JamesMcS]

Is it possible to put a variable in the linkchild/masterfields property? That might be a way around it actually...

 

[vbaInet]

It doesn't seem as if you can change the source object from the subform itself. By the way, remember you're changing the source object of the subform control, not the subform object. Try making a correct reference to the subform control via the parent form. Also change the name "Detail" to something else.

Still try this:

Forms!Name_Of_Parent.Controls("Name_Of_Subform_Control").SourceObject = "Detail"
Forms!Name_Of_Parent.Controls("Name_Of_Subform_Control").Form.Refresh

 

[JamesMcS]

Sorry again, the MainSub/Detail refs were just so they could be differentiated here, their real names are a bit more descriptive...

Strange it all worked perfectly in 03 and seems to be fine in 07 except for the text box being blanked out on the chenge of source object... I'll give it a go now though, thanks for your help vba! excellent as always!

 

[JamesMcS]

And I can't even add to your rep... need to rep someone else first I think!

 

[vbaInet]

It may be that 07 demands "proper" referencing when referring to certain properties if you're down a level or two. Let us know if that works for you.

Strange about the rep Could be you need to have certain number of points to be able to do so. Thanks anyway.