Subform changes for no (apparent) reason (1 Viewer)

[A. Turtle]

I have several small forms from which users can select options. I want to group these small forms onto a single form so that all selections can be made from one screen.
I have created the main form and started adding the smaller forms using the Subform Wizard. All goes well until I add the third subform. If I close the main form, after saving it of course, when I reopen it the last subform added disappears and is replaced by a second instance of the first subform.

I have searched for any references to there being a limit on the number of subforms that can exist within a form, but have found nothing. No amount of resizing, repositioning with or without overlaps, or anything else I have tried makes any difference. Severalteen attempts to create this form have all ended in the same result.

Any help will be very gratefully received.

 

[MarkK]

I've had this problem before. I don't know the cause, but my solution was to explcitly load the subforms into the controls in the Form_Open() event handler of the main form. Code looks like...

Private Sub Form_Open(Cancel as integer)
  me.sfmControl1.sourceobject = "fSubformName1"
  me.sfmControl2.sourceobject = "fSubformName2"
  ...
End Sub

Also, delete the existing sourceobject from the subform control in design view.
And, as an aside, this loads subforms faster anyway. If you have a subform based on a large table you can significantly reduce the amout of time it takes the main form to open by loading the subform after the main form. By default, subforms open before the main form.

 

[A. Turtle]

Hi Lagbolt,

Many thanks for the reply, but I am having problems using the example code you gave.
The controls were deleted as you suggested, to leave blank pages. The pages have been named, without spaces.

My version of the code for the first page reads:

Me.SPS_Payroll.SourceObject = "frmSubSPS_Payroll"

where SPS_Payroll is the name assigned to that particular tabbed page, and frmSubSPS_Payroll is the name of the form I want to appear in that tabbed page.

When I attempt to open the form I get the error message, "Compile Error: Method or data member not found", and the part of the command that is highlighted is ".sourceobject ="

Which bit did I get wrong please?

 

[MarkK]

No, don't delete the control. Delete the SourceObject property value from the control. The control should go white and maybe says 'unbound' rather than containing the name of the source object.
Let me know...

 

[A. Turtle]

Once again Lagbolt, thank you for your time and help.

I have deleted SourceObject as stated, and control does indeed go white and says "unbound". However the error remains the same;

"Compile Error: Method or data member not found", and the part of the command that is highlighted is ".sourceobject ="

Is there any possibility that this is an issue with the version of Access that I am using? I use Access 2003 at work, but only have 2000 at home. The majority of the work on this project has been done at home. (Later Edit) No. SourceObject exists in both.

Have tried this on a single form and also on tabs and the result is the same. Subform changes when placed directly, and code errors when using above method. Despite lots of searching, and reading, I can't find any reference to Access having this "feature".

 

[MarkK]

Are you sure you're referencing the sourceobject property of a subform control object? This, "Have tried this on a single form and also on tabs and the result is the same," suggests you are trying to set the sourceobject property of a tab. This will give you the error you describe.

 

[A. Turtle]

Hi yet again Lagbolt,

Are you sure you're referencing the sourceobject property of a subform control object? This, "Have tried this on a single form and also on tabs and the result is the same," suggests you are trying to set the sourceobject property of a tab. This will give you the error you describe.

I presumed, wrongly it seems, that in this context "SourceObject" was the object that was to appear on the main form or tab, i.e. the complete subform.

To break down how I understood the command:

"Me.SPS_Payroll.SourceObject = "frmSubSPS_Payroll"

works

"Me" is the parent form
".SPSPayroll" is the name of the tabbed page on the parent form
".SourceObject = " defines what follows as what is to be placed on the tab. In this case that is -
"frmSubSPS_Payroll" a small form with three command buttons which run update queries to set the value of a field in the underlying table.

I hope this explanation makes sense.

A new reference book on using VB in databases is on the way. Hopefully it will answer some of my other questions as well. The books I have atm don't go into anything this deeply.

 

[MarkK]

When you create a subform on a mainform using a wizard you get a control, a rectangular object that hosts the subform. This is a subform control. It is not a textbox, not a tab page, not a combo box, etc.... If you click on it in design view and look at its property sheet it'll say "Subform/Subreport: SomeControlName" in the title bar.
It will have been this object from which you previously deleted the source object property, saw the control go white and saw the word 'unbound'.

It is to that object that you must re-assign the name of the form you would like to load into that control.

Using...
Me.SPS_Payroll.SourceObject = "frmSubSPS_Payroll"
"Me" is the object in which code is currently running
".SPS_Payroll" is the subform control
".SourceObject = " is a property of a subform control that should be assigned the name of the form you would like the control to contain.

 

[A. Turtle]

Hi Lagbolt,

Done and working. There were other ways of presenting this bit to the users, but they were untidy, and imho offered far more opportunity for error. Hopefully this will minimise those opportunities.

The users (unknowingly) and I will be grateful for a long time to come.