Refreshing Subforms

[CaptainBalrog]

Hi there.
I have a form with two sub forms on it. The form loads with a reference number that I pass to a function that in turn requeries the first sub form. The function call is in the on load event of the main form.
This part works fine.
The second sub form uses a similar requery function to the first one. The only difference is that the query for the second sub form is based on a reference number from the selected record in the first sub form.
If I put the function call for the second sub form in the on click of the first one all is well and the second sub form displays all records with a matching reference number.

The problem is that when the main form loads the first sub form will display the correct data but the second remains blank or displays the wrong data.
I know this is happening because the first sub form has not loaded fully and so the second sub form cannot get the reference number. I just can’t find a way to make the second sub form display the correct data when the main form loads.

I have tried calling the requery function for the second sub form from most of the events on the main form and many of the events on the first sub form but to no avail.
I also tried putting a meaningless sub form at the bottom of the main form and putting the requery function for the second sub form in the on load event but that was no good either. This surprised me as until that point I was assuming that access was loading the sub forms sequentially from top to bottom.

Can anyone tell me how I might determine the point at which the first sub form has been loaded so that I can get access to the reference numbers needed for the second sub form query. I would prefer not to use a timer for this as there is no way to determine the maximum time it will take access to load the rest of the data but at present it looks like the only option left.

Any comments would be much appreciated and sorry for the lengthy explanation.

 

[RuralGuy]

You are not taking advantage of the Link Master/Child Fields built in to SubFormControls. Set up properly they do all of the work for you. Put an invisible TextBox on your main form with the control source set to:
=SubformControl1.Form.ControlWithS2sReference
...using YourNames of course!
Then set up the Link Master/Child Fields to point to this invisible TextBox. As SubForm1 loads it will correct the TextBox value. As the TextBox value adjusts it will sync SubForm2.
That is just one way to resolve the issue.

 

[CaptainBalrog]

Hi there.
I did what you suggested but its not working as yet.
Currently when I try to open the main form the sub form control is white.
Any idea why that might be?

When I click the button to enter something in the Link Master/Child fields I get an error saying “Cannot build a link between unbound forms”.

If I type directly into the Link Master/Child fields and then open the form I get a popup that says “Enter parameter value” with a combo containing two values, <NULL> and <DEFAULT>.

Wish I could tell you more but these are uncharted waters for me.

 

[RuralGuy]

I would start by binding your forms to queries or tables. Why are you using unbound forms?

 

[CaptainBalrog]

Hi again.
Do you mean the main form should be bound?
The sub form in question is bound to a query as far as I understand it. I made the query in sql design view and then selected it as the data source for the sub form.

The reason I am using an unbound form is that it’s just the way things are done around here for all kinds of reasons. The only development related reason for it that I am aware of is that there is no way to turn off the mouse wheel scrolling through records in a bound form. This allows users the opportunity to enter data in the wrong records and the like. Don’t event try and tell me that wouldn’t happen, it does. Give them the opportunity and they will grab it with both hands. Well, they would if they had thumbs.

 

[RuralGuy]

Stephan Lebans has solved the mouse wheel problem here:
http://www.lebans.com/mousewheelonoff.htm
This code from Stephan has been tested by many people and works well.

Most of the power of Access is with bound forms. Without having the MainForm bound you cannot avail yourself of the Link Master/Child Fields as you discovered. If you put a bound SubForm on a bound MainForm then the Link Master/Child Fields are completed by Access when you add a record to the SubForm, for example. If either of the forms is not bound then you need to do all of the work yourself as you started off doing.

If you don't want to test with a bound form then I'll go back and reread your op and try a different approach. Your choice.

 

[CaptainBalrog]

Hi again.
I got it working. I called the function to refresh the second sub form in the oncurrent event of the first one and it worked fine.

I have been developing in the evil lotus notes for years and this is the first access app I have been given so I’m still learning what all the extra events that it has do. I'm liking access more today than I did yesterday anyway.

I will get that mousewheel dll for future reference.

Thanks for your help mate.