Looking for the best way to select a subform from a combobox

[Theguyinthehat]

So far, in scrounging forums and looking at databases, I've noticed two ways of doing it, neither of which works very well.

A) Using SourceObject to define a pre-existing form as a source for an unbound subform
--Works when it works, except it keeps corrupting and locking out

B) Hosting all subforms on the parent form, setting Visible = false on non-relevant forms
--We use some older machines at work, and this takes a crap-ton of virtual memory

Is there some way to do this without killing older machines? I cycle between about 12 subforms. Thanks in advance.

 

[Atomic Shrimp]

You can just change the sourceobject of the subform control like this:

Me.YourSubFormName.SourceObject = "YourNewSubformName"

So your combo box could be based on a table with a column of actual candidate form names (as the bound column) and a column of user-friendly form names (as the display column)

 

[Theguyinthehat]

I was doing that to an unbound subform, and it kept corrupting, even though it would often work a few times. Are there others that don't involve an unbound subform or loading all the subforms onto the main form at once?

 

[Atomic Shrimp]

Not that I know of - in this sort of case, I might consider making them independent forms and opening them from a switchboard type launcher.

What do you mean by 'corrupting', by the way? - what actually happened?

 

[Theguyinthehat]

Access would close down or I would go to form view and the form would close. When I tried to open it, nothing would happen--the computer would try to open it and ultimately would fail to do so. I would then have to rebuild the form from a saved version, and eventually the same thing would happen.

 

[Theguyinthehat]

Alright I gave the SourceObject approach another try, this time setting a default SourceObject on the form's 'on current'. I think that the issue was I didn't have a default form linked to the subform.

 

[Atomic Shrimp]

I'm not sure about the On Current event - doesn't that one fire each time the record pointer moves?

If there's a combo box on the parent form to choose the subform, I would have thought the AfterUpdate event of that combo would be the place to set the subform.

 

[Theguyinthehat]

On current I think just refreshes when the form is loaded. I put a default form for the oncurrent and the code to select the proper subform is on the afterupdate of the relevant combo box. In any case, the on current default form is loaded except at the very beginning.

 

[boblarson]

On Current fires every time you change records.

Also, if you want, I have a sample on my website where I have a tabbed control with about 2 or 3 subforms and load them only when the tab is selected.

 

[Kryst51]

On Current fires every time you change records.

Also, if you want, I have a sample on my website where I have a tabbed control with about 2 or 3 subforms and load them only when the tab is selected.

Does this make it so that there are not unecessary things running and consuming resources? That is very cool, and I think I'll try to implement it into my NCR database here at work.

 

[boblarson]

Does this make it so that there are not unecessary things running and consuming resources? That is very cool, and I think I'll try to implement it into my NCR database here at work.

Yes, depending on how many subforms and controls you have, it can slow things down immensely if all has to be loaded initially. So, it can be helpful to only load those things that are actually needed and load others as they are needed too.

 

[Theguyinthehat]

I successfully used sourceobject to load only the relevant form--the trick is to have a default form loaded in the unbound subform when you open the main form, otherwise it crashes.

Private Sub cboTools1_AfterUpdate()
If Me.Tools1.Value = 1 Then
Me.sfmTool.SourceObject = "sfrmA24DJE"
ElseIf Me.Tools1.Value = 2 Then
Me.sfmTool.SourceObject = "sfrmAnelvaJE"
ElseIf Me.Tools1.Value = 3 Then
' ... You get the idea
End If
End Sub

I could start a new thread for this but....My issue is now that I can't access fields in the subform to save to the record opened on the mainform. I can do it from within the subform (using afterupdate() on the fields in the subforms) but if a new tool is added that means a LOT of updating work for whoever's attempting to do it. I want to save via a command button on the main form. I tried this:

Private Sub Command30_Click()
Dim db As Object
Dim current as object

Set db = CurrentDb
If Me.Tools1.Value = 8 Then
Set current = db.OpenRecordSet("SELECT [Target Current] FROM JobEntry WHERE LotNumber = '" & Me.LotNumber & "'")
current.Edit
current("Target Current").Value = [Forms].[sfrmIMJE].txtcurrent.Value
current.Update
End If
End Sub

where sfrmIMJE is the form acting as the SourceObject for sfmTool (which is the unbound subform on the main form)...however it doesn't recognize sfrmIMJE. I tried to access txtcurrent from sfmTool but it obviously wasn't there.... help?

***
where txtcurrent is a textbox in sfrmIMJE

 

[boblarson]

Something doesn't sound right to me. You state:

My issue is now that I can't access fields in the subform to save to the record opened on the mainform.

Which sounds like you are thinking here in the wrong about way. A MAIN form should be showing data for a record and a subform shows RELATED data that is child data of the main form. You can update data for a subform from data on a main form, but it shouldn't really flow the other way. It almost sounds as if you are using a subform as more of a listbox which would work better for updating a main form instead of using a subform.

 

[Theguyinthehat]

Well you nailed what I'm trying to do. All my forms are set for data entry (and without navigation buttons). The main form creates the records, and I want to use the subform to add more data to the same record. I would use the same form but there are different fields that need to have values depending on which Tool the person selects. For instance, Anelva is a tool that needs a Target Deposition thickness, while the Implant tool needs to know a Target Current and a Target Temperature...If I could host all the fields on the main form and disable non-relevant fields I would, but there are 100 or so fields between all the tools used. So I'm using a subform for each tool that prompts values for the relevant fields. I'm using an OpenRecordset for the afterupdate() on the subform's fields to access the main form's record--maybe I shouldn't use a subform?
***
for the record, I'm getting runtime error 438 on the line
current("Target Current").Value = [Forms].[sfrmIMJE].txtcurrent.Value

 

[Theguyinthehat]

I figured out how to do it, for those interested. (Just for the Target Current field value in JobEntry entered in a textbox txtcurrent on the subform acting as a SourceObject for the unbound subform sfmTool, editing a record specified by a combobox LotNumber on the Main form...wow that was a mouthful)

Private Sub Command30_Click()
Dim db As Object
Dim current As Object
Set db = CurrentDb
If Me.Tools1.Value = 8 Then
Set current = db.OpenRecordSet("SELECT [Target Current] FROM JobEntry WHERE LotNumber = '" & Me.LotNumber & "'")
current.Edit
current("Target Current").Value = Me.sfmTool.Form.txtcurrent.Value
current.Update
End If
End Sub

Thanks for your help in all this!