Get Recordsource of unopened form 4

[sterlecki]

Is it possible to capture the record source property of a closed form?

I would like to capture the recordsource property in a text box for any form in the DB by using the afterupdate property of a combobox that lists all the DB's forms.

Private Sub cbo_FormName_AfterUpdate()
Dim FormName As String
Dim FormRS  As String

FormName = Me.cbo_FormName 'selects a form from a combobox

FormRS = FormName.RecordSource
Me.txt_FormRecordSource = FormRS

End Sub

 

[jrbarnett]

Have tried, but you need to open the form (even if in design mode or view set to hidden to access the Recordsource property.

John

 

[kjv1611]

Sure...

Private Sub cbo_FormName_AfterUpdate()
Dim FormName As String
Dim FormRS  As String

FormName = Me.cbo_FormName 'selects a form from a combobox

FormRS = FormName.RecordSource
Me.txt_FormRecordSource = [HIGHLIGHT]Forms(FormRS).RecordSource[/HIGHLIGHT]
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[jrbarnett]

kjv - you need the form to be open for that to work.

Sterlecki wanted to use it with closed forms.

John

 

[kjv1611]

Fooey! I thought the form was closed in my test, but it wasn't. Oh well. Surely there's a way.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

sterlecki,

It looks like you will have to open the form - one way or another - if you want to view its recorsource in any way.

So, look into the DoCmd.OpenForm and DoCmd.Close acForm commands if you really need this. There are options in the open portion to set how/if the form can be seen, etc. And then I suppose make sure it doesn't save the form upon closing, unless you need to for some strange reason.


--

"If to err is human, then I must be some kind of human!" -Me

 

[MajP]

To make it work like you are asking you will have to do like everyone said: open the form hidden, get the recordsource, and then close the form.

You can actually get the recordsource from a closed form by going to the class. But there is no way to use a variable name to do that. So if Form1 is closed you can use the object

Form_Form1.recordsource

You could write a case statement to do this, but you would be better off writing the codel

This will return the recordsource of an open or closed form

Public Function getRecordSource(frmName As String) As String
  Dim afrm As AccessObject
  Dim frm As Access.Form
  For Each afrm In CurrentProject.AllForms
    If afrm.Name = frmName Then
      If Not afrm.IsLoaded Then
        DoCmd.OpenForm frmName, acDesign, , , , acHidden
        getRecordSource = Forms(frmName).RecordSource
        DoCmd.Close acForm, frmName
      Else
       getRecordSource = Forms(frmName).RecordSource
      End If
    End If
  Next afrm
End Function

 

[MajP]

if the form is closed it opens it hidden then closes it. If it is open it leaves it open.

 

[kjv1611]

Learned something new, I did.


--

"If to err is human, then I must be some kind of human!" -Me

 

[sterlecki]

Thanks MajP This works great.

I set this up in the module of my "Defaults" Form where it collects the value from the forms combobox cbo_FormName and then returns the recordsource to the textbox me.txt_FormRecordSource.

One small glitch is most of these forms reside as subforms in my "Main Form" If the "Main Form" happens to be open then the code causes this form to close. That is a very minor problem for me. I'm not sure if users will be doing this routine with the Main Form open or closed as a general rule. Works great though.

Private Sub cbo_FormName_AfterUpdate()

Dim FrmName As String
Dim FormRS  As String

FrmName = Me.cbo_FormName 'selects a form from a combobox
FormRS = getRecordSource(FrmName)

Me.txt_FormRecordSource = FormRS

End Sub


Public Function getRecordSource(FrmName As String) As String
  Dim afrm As AccessObject
  Dim frm As Access.Form
  For Each afrm In CurrentProject.AllForms
    If afrm.name = FrmName Then
      If Not afrm.IsLoaded Then
        DoCmd.OpenForm FrmName, acDesign, , , , acHidden
        getRecordSource = Forms(FrmName).RecordSource
        DoCmd.Close acForm, FrmName
      Else
       getRecordSource = Forms(FrmName).RecordSource
      End If
    End If
  Next afrm
End Function

 

[sterlecki]

FYI

When I changed the openform to
DoCmd.OpenForm FrmName, acNormal, , , , acHidden

Everything stayed open and works great

 

[MajP]

I opened in design view because if your form has code in the onload or onopen event the code fires eventhough the form is hidden. Could cause problems.

I did not think about subforms. The bullet proof fix would be to still open in design view any forms but do the following before opening.
1) Look at all the open forms
2) If your open form is what you are looking for then get its recordsource
3) If not
Check all the subforms on the open form. And see if it is what you are looking for.
4) If not then it is not an open form or an open subform. so open it in design view and do not worry about any code executing

 Public Function getRecordSource(frmName As String) As String
  Dim afrm As AccessObject
  Dim frm As Access.Form
  Dim ctl As Access.Control
  'check open forms and subforms first
  For Each frm In Forms
    If frm.Name = frmName Then
      getRecordSource = frm.RecordSource
      Exit Function
    Else
       For Each ctl In frm.Controls
           If ctl.ControlType = acSubform Then
              If ctl.Form.Name = frmName Then
                getRecordSource = ctl.Form.RecordSource
                exit funtion
               End If
           End If
       Next ctl
     End If
  Next frm
  
  
  For Each afrm In CurrentProject.AllForms
    If afrm.Name = frmName Then
        DoCmd.OpenForm frmName, acNormal, , , , acHidden
        getRecordSource = Forms(frmName).RecordSource
        DoCmd.Close acForm, frmName
     End If
  Next afrm
End Function

Public Sub test()
  MsgBox getRecordSource("subFrmA")
End Sub

 

[PHV]

A little speed improvement:

Public Function getRecordSource(FrmName As String) As String
If CurrentProject.AllForms(FrmName).IsLoaded Then
  DoCmd.OpenForm FrmName, acNormal, , , , acHidden
  getRecordSource = Forms(FrmName).RecordSource
  DoCmd.Close acForm, FrmName
Else
  getRecordSource = Forms(FrmName).RecordSource
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Well thanks again to both of you for these great suggestions. I'm amazed at how fast you guys bang out this code.

I'm half way there in dealing with this problem thanks to you guys.

Have a star again.

 

[MajP]

I think it should read
If not CurrentProject.AllForms(FrmName).IsLoaded Then

The reason I looped the all forms collection is out of habit. That is how you can tell if a form exists. Be advised
If not CurrentProject.AllForms(FrmName).IsLoaded
will throw an error if you pass in a bad name

This will not throw an error but just falls out of the loop:
For Each afrm In CurrentProject.AllForms
If afrm.Name = frmName Then
DoCmd.OpenForm frmName, acNormal, , , , acHidden
getRecordSource = Forms(frmName).RecordSource
DoCmd.Close acForm, frmName
End If
Next afrm

Should not be an issue since you have a combo box.
Anyway here is the standard code to see if a form exists

Public Function frmExists(frmName As String) As Boolean
Dim aFrm As Access.AccessObject
For Each aFrm In CurrentProject.AllForms
If aFrm.Name = frmName Then
frmExists = True
End If
Next aFrm
End Function

 

[PHV]

A shorter way:

Function FormExists(strFormName As String) As Boolean
On Error Resume Next
FormExists = (CurrentProject.AllForms(strFormName).Name = strFormName)
End Function