String variable as form name 1

[LowBrow]

I apologize for cross posting, but I did not get any responses in VB 5&6.

I want to be able to open a form as a subform sourceobject based on a string that is built from two other strings. The first is a name convention of a group of forms ("frm_Ques_"), and the second is a number between 1-73, which represents the number of a given form. When it is combined, the string should look something like "frm_Ques_34", and then frm_Ques_34 would be opened as the sourceobject for the given main form.
Here is what the code I built looks like (but it does not work).

Private Sub cmdPickUp_Click()
Dim frm As Form
Dim frm2 As String
Dim strEnumerator As String
Dim strSubObject As String

Set frm = Forms(frmPCP_Edit_Main)

strEnumerator = Me.StatusID

strSubObject = "frm_Ques_" + strEnumerator

Set frm2 = Forms(strSubObject)

  If Me.StatusID < 1 Then
  
  frm.frm_Sub_Object.SourceObject = "frm_Ques_01"
    
  Else
  
  frm.frm_Sub_Object.SourceObject = frm2
    
  End If
    
End Sub

The goal of this code is have a 'pick up where you left off' option in an assessment db. I thought it would be a rather straightforward option. Now, I'm not so sure. I do not want to have to develop a 'case scenario' for all 73 subforms. But I cannot get Access to access the string as a form name. StrongM has solved this to some degree, but I cannot get his code to work with mine.
StrongM has answered many posts with code for a similar issue, but I cannot get his code to work.

Any ideas appreciated!

 

[VBslammer]

I use dynamic switching of subforms in my projects all the time, what kind of problems are you having? Is it a naming problem or are you getting errors? Is "frm_Ques_10" the actual name of the subform?

You can refer to the objects like this:

[green]'reference for the subform[/green]
Dim subfrm As Form   

[green]'assign the subform[/green]
MainFormObject!SubformControl.SourceObject = "Customers subform"
[green]'OR[/green]
Forms("MainFormName").Controls("SubformControlName").SourceObject = "Customers subform"

[green]'capture the subform object[/green]
Set subfrm = MainFormObject!SubformControl.Form
[green]'OR[/green]
Set subfrm = Forms("MainFormName").Controls("SubformControlName").Form
Debug.Print subfrm.Name, subfrm.ActiveControl.Name
[green]'etc.[/green]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[LowBrow]

Hey, good to have you here! Thanks!
The dynamic switching isn't the problem, Slammer. Its the using the variable as a form name. I probably didn't explain completely enough in this post.

My code builds the subform name based on two pieces, one is the string "frm_Ques_" and the second is the enumerator (a number between 1-73). There are 73 forms in the collection so numbered. The problem seems to be in the loading of a form that is not open AND in calling that form using a variable. It seems Access can easily do one of these two things, but not both together without a separate function which is called.

When I try to do it straight-forward, I get 'Access cannot find the form 'frmWhatever'. When I use StrongM's code with mine I get 'Object Required' when the script hits the function. Mousing over the function variable name in his function shows the correct form, but I cannot figure out what the problem is. Here is my original thread with a link to StrongM's code: thread222-996138

And here is my code with inspiration from StrongM:

Private Sub cmdPickUp_Click()
Dim frm As Form
Dim frm2 As Form
Dim strEnumerator As String
Dim strSubObject As String

Set frm = Forms(frmPCP_Edit_Main)

strEnumerator = Me.StatusID

strSubObject = "frm_Ques_" + strEnumerator

  If Me.StatusID < 1 Then
  
  frm.frm_Sub_Object.SourceObject = "frm_Ques_1"
    
  Else
  
  frm.frm_Sub_Object.SourceObject = FormLoad(strSubObject)
    
  End If

  End Sub

And this is the function:

Function FormLoad(ByVal sFormName As String) As Form
    Set FormLoad = VB.Forms.Add(sFormName)
End Function

Don't mean to overwhelm you, but I feel I am very close to getting this to work, and if I don't, well, I'm an obsessive caffiene and nicotine fiend...it won't be pretty!

 

[VBslammer]

Are you using Access or VB? I see [tt]VB.Forms.Add(sFormName)[/tt] which is a VB method, not Access. If you want to load a form in Access, it's different: [tt]DoCmd.OpenForm sFormname[/tt]

Let me know...

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[LowBrow]

That may be the root of this problem. I am using Access and VBA. I assumed that many things were interchangable. I guess all things are not.
That gets me a LOT closer. It opens the correct form, but not as the sourceobject for the subform (it opens it as its own form), and gives me Run-Time error 2465 Application-defined or object-defined error.

Thanks again for the help. If you have any other ideas about this, I'll listen. I think this error is from the sourceobject and DoCmd conflicting (the code thinks I am trying open the form in two places at once).

I'll keep plugging away. Thanks again!

 

[VBslammer]

I'm not really sure what your design goal is here, but I put together an example that loops through all the forms in the database, and searches for any form that has a subform with ("frm_Ques_" & index) on it, then opens the main form to get a reference to its subform. You should be able to get some ideas here:

Private Sub cmdPickUp_Click()
On Error GoTo ErrHandler
  Dim frm As AccessObject
  Dim subfrm As Form
  Dim ctl As Control
  Dim index As Integer
  Dim blnFound As Boolean
  Dim strSubControl As String
  Dim strSubCurrent As String
  Dim strSubform As String
  
  index = 1
  strSubform = "frm_Ques_"
  
  For Each frm In CurrentProject.AllForms
    DoCmd.Echo False
    If Not frm.IsLoaded Then
      DoCmd.OpenForm frm.Name, acDesign
    End If
    
    For Each ctl In Forms(frm.Name).Controls
      If ctl.ControlType = acSubform Then
        strSubCurrent = ctl.SourceObject
        strSubControl = ctl.Name
        If strSubCurrent = (strSubform & index) Then
          DoCmd.OpenForm frm.Name, acNormal
          Set subfrm = Forms(frm.Name).Controls(strSubControl).Form
          blnFound = True
          Exit For
        End If
      End If
    Next ctl
          
    If blnFound Then
    
      [green]'reset flag[/green]
      blnFound = False

      [green]'increment counter[/green]
      index = index + 1
    
      [green]'we have the subform, now do whatever with it...[/green]
      Debug.Print subfrm.Name, subfrm.Controls.count
      
      DoCmd.Echo True
      
      If MsgBox("Found Subform " & subfrm.Name & ", continue search?", _
                  vbQuestion + vbYesNo, "Confirm Action") = vbNo Then
        Exit For
      End If
    
    Else
      [green]'close the main form[/green]
      DoCmd.Close acForm, frm.Name, acSaveNo
    End If
  Next frm

  MsgBox "Found " & (index - 1) & " subforms meeting your criteria"

ExitHere:
  On Error Resume Next
  DoCmd.Echo True
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[LowBrow]

This part of the database is a 73 question assessment that takes about 1.5 hours to complete. Some of the questions are fairly detailed, and the nature of our work and the clients we support mean that staff may not be able to complete the assessment in one sitting. I wanted to have a simple method that required no response from the user to set the last question answered. The main form is client specific information. There are three ways to navigate through the questions, which reside on the subform:
Next/Previous buttons, some section/part master buttons on a separate navigator form, and the 'pick-up where you left off' button.
Which brings us to our problem: The way this works is that I have a table called tblEdit. tblEdit has a field called StatusID. When I move from question to question (changing which subform appears as the sourceobject), the StatusID value is updated in this box. This box is tied to the masterID for the assessment record. The value that is updated in this box is the value 1-73, and is equal to the question number of the current question (subform). This is the only automatic method of setting which was last question answered I could come up with.
Everything works fine up to the point of actually opening the subform as a sourceobject. The statusID field updates correctly, the form name builds properly, and the value passes up to the error mentioned previously.

Does this make sense? Did I reinvent the wheel as a oval? It seemed efficient and mostly user-proof when I was mapping it out. Its long past my bedtime. I will check back to on your code here in the morning. Thanks for response and help!

 

[VBslammer]

If the pick-up button is on the main form, the subform should be easy to change:

Private Sub cmdPickup_Click()
  Call UpdateSubform
End Sub

Private Sub Form_Load()
  If Not IsNull(Me.OpenArgs) Then
    Me!StatusID = Me.OpenArgs    
  End If
  Call UpdateSubform
End Sub

Public Sub UpdateSubform()
  If Me!StatusID > 0 Then
    Me!frm_Sub_Object.SourceObject = "frm_Ques_" & Me!StatusID
  Else
    Me!frm_Sub_Object.SourceObject = "frm_Ques_1"
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

All of the navigation buttons need to be on the main form or other form:

[green]'Main Form[/green]
Private Sub cmdPrevious_Click()
  If Me!StatusID > 1 Then
    Me!StatusID = Me!StatusID - 1
    Call UpdateSubform
  Else
    Beep
  End If
End Sub

Private Sub cmdNext_Click()
  If Me!StatusID < 73 Then
    Me!StatusID = Me!StatusID + 1
    Call UpdateSubform
  Else
    Beep
  End If
End Sub

[green]'Other Form[/green]
Private Sub cmdPickup_Click()
  Dim frm As Form

  If Not CurrentProject.AllForms("MainForm").IsLoaded Then
    DoCmd.OpenForm "MainForm"
  Endn If

  Set frm = Forms("MainForm")

  frm!StatusID = 34
  frm.UpdateSubform

End If

Notice the "UpdateSubform()" procedure has Public scope, so you can call it from outside the main form, as shown above, and it can handle its own subform changes.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[LowBrow]

Dude, this is so sweet! I did not even think about using the Prev and Next commands based on the statusID. Never mind want I was going to do, that is brilliant!
Thanks a ton!
I will implement this when I get to work this morning and let you know how it goes. Thanks a ton!