looping through properties of all controls on a form...

[MistyEE]

Can anyone PLEASE tell me why I the code below produces a type mismatch error? It breaks on highlighted...

Public Sub ReadFormProps(frmOpenForm As Form, HistoryID As Integer)

     Dim ctrl As Control       'to loop through controls
     Dim prpty As Property  'to loop through properties of controls
     
     With frmOpenForm
          strName = .name
          intControlCount = .Controls.Count
          strCaption = .Caption
          For Each ctrl In frmOpenForm.Controls
               strCtrlName = ctrl.name
               intControlType = ctrl.ControlType
[COLOR="Red"]               For Each prpty In ctrl.Properties[/COLOR]
                     strPropertyName = prpty.name
                     varPropertyvalue = .Properties(strPropertyName).Value
               Next      'property of control
          Next      'control on form
     End With  'open form

 

[pbaldy]

How are you calling it? In a brief test, I didn't get that error.

 

[MarkK]

I also can't replicate your error.
If you...

Debug.Print Typename(prpty)

it's actually an 'AccessProperty', which is a hidden member of the Access object model. I also tried...

Dim prpty as DAO.Property

and it still typename()'s as an AccessProperty, which seems weird.
Maybe it'll work if you...

Dim prp as object

 

[MistyEE]

Okay...it is starting to uncloud here, if it is an access object as lagbolt said...then, as pbaldy had mentioned, it is probably in the calling.

I will post the calling procedure below... What I am trying to do is read the properties of controls on a form in an external database. I can query for all of the AccessObjects, and do. But I was under the impression that I could only get at the form's control properties by opening the form. So once I have all of the forms name's I loop through them, opening each and then loop through the controls. The information gathered up to this point has been okay. Within the control loop, my attempt at getting at the properties has failed for remote databases. (Although it works fine in the current database...) Any suggestions at all-would be extremely appreciated!!

Before I post the code, I just want to thank both of you for taking the time to take a look at this. I have been beating my head into a concrete wall...to no avail.

 Public Sub ReadDbForms(strMDB As String, intReadHistoryID As Integer)

'ADO
     Dim rstForms As ADODB.Recordset
     Dim strSQl As String
     Dim dbName As String
'Objects, etc.
     Dim strForm As String    'string name of form from recordset
     Dim frmOpenForm As Access.Form
     Dim objAccess As Access.Application
     Dim intSysObjID As Integer 'pkobjectid from SYS_readObjects table
     Dim DB As Database
'set environment/etc.
     Set rstForms = New ADODB.Recordset
     strSQl = "SELECT Sys_RO.pkObjectID, Sys_RO.Name, Sys_RO.Type, Sys_RO.fkReadHistoryID " _
                         & "FROM SYS_ReadObjects Sys_RO " _
                         & "WHERE (((Sys_RO.Type)=-32768) " _
                                   & "AND ((Sys_RO.fkReadHistoryID)=" & intReadHistoryID & ")); "
     Set objAccess = New Access.Application
     With objAccess
          .OpenCurrentDatabase strMDB
     End With
               With rstForms
                    .Open strSQl, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
                    If Not .EOF Then
                         .MoveFirst
                         Do While Not .EOF   'rstforms
                              intSysObjID = .Fields("pkobjectid")
                              strForm = .Fields("Name")
                              objAccess.DoCmd.OpenForm strForm, acDesign, , , , acHidden
[COLOR="Red"]                              Set frmOpenForm = objAccess.Forms(strForm)
                              Call ReadFormProps(frmOpenForm, intReadHistoryID)[/COLOR]
                              objAccess.DoCmd.Close -32768, strForm, acSaveNo
                         .MoveNext
                         Loop 'recordset listing forms in Database App = rstforms
                    End If
                    .Close    'rstforms
               End With  'rstforms
          Set rstForms = Nothing
End Sub

 

[MistyEE]

Forgot to say...I still don't understand why it comes back type mismatch....
I am in a form, I have a control....

 

[MarkK]

You might also reference the remote database rather than create it as a New Access.Application. See if that gives you less restricted access to its objects.
Here's code I use to set a reference to my library database of common functions...

Private Sub CreateLibRef()
'  Creates a reference to the Library database FILENAME_LIBRARY
   Dim rfs As Access.References
   Dim rf As Access.Reference
   Dim filespec As String
   
   Set rfs = Application.References
   filespec = CurrentProject.Path & FILENAME_LIBRARY
   'fso is 'Public Property Get fso() As Scripting.FileSystemObject'
   If Me.fso.FileExists(filespec) Then  
      On Error GoTo handler
         Set rf = rfs.AddFromFile(filespec)
      On Error GoTo 0
   End If
   ...
End Sub

 

[matthew_of_cambridge]

' First, cast the AccessProperty to a Variant or Object:
Dim testVar As Variant
testVar = testObj.Properties(propertyName)

' Then, the TypeName function will give you the information you require:
TypeName(testVar)

 

[RealMojeaux]

for access environments:

instead of:

dim prop as property

use:

dim prop as dao.property


Took 2 days to figure out why it would only throw an error, when running.

woohoo

 

[gemma-the-husky]

note that surprisingly, some properties might not have values. you may need error handling to manage them, when you iterate every property in a collection.

maybe that's the issue.

I think I have had that issue before, although I thought it generated a "not in collection" or similar error, rather than "type mismatch", I think.

 

[pbaldy]

Note, 5 year old thread.