List all fields on a form

[vburrows]

I am not very good with the access object model
How would I create a list of all the fields on a particular form or all forms in a particular .mdb?

 

[SkipVought]

Hi,

Check out faq700-6905.

I used this excellent FAQ as the basis for gathering info for OTHER objects in Access.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vburrows]

I need help with getting the fields on forms this only tells about tables and relationships. Thanks thought that will be usefull on some documentation that I have to do today as well.

 

[TheAceMan1]

How are ya vburrows . . .

The following routine will output all forms to the immediate window. You can run the routine from any module in the modules window:

[blue]Public Sub GetAllForms()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Name, Type " & _
         "From MSysObjects " & _
         "WHERE ([Type] = -32768) " & _
         "ORDER BY [Name];"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      MsgBox "No Forms in Database!"
   Else
      Do
         Debug.Print rst!Name
         rst.MoveNext
      Loop Until rst.EOF
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[PHV]

Have a look here:

http://mdbdoc.sourceforge.net/

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

 

[vburrows]

That is awsome all i need now is to figure out how to get each field listed that is on those forms

 

[vburrows]

PHV thank you very much that is a great piece of software/scripts My problem is I have to have the code because of limitations of RDC (remote desktop connection) I cannot paste anything there but text.

 

[Remou]

Some notes:

Sub BuildFormsTable()
Dim tdf As Object
Dim fld, fn, tn, strSQL

'Create table
strSQL = "Create Table tblForms (TableName Text (100),FieldName Text(100))"

'Clear existing table
'strSQL = "Delete From tblForms"

CurrentDb.Execute strSQL
For Each frm In CurrentProject.AllForms
    
    DoCmd.OpenForm frm.Name, acDesign
        Set f = Forms(frm.Name)
        fn = f.Name
        For Each ctl In f.Controls
            cn = ctl.Name
            strSQL = "INSERT INTO tblForms ( " _
                    & "FormName,ControlName) VALUES ('" _
                    & fn & "','" & cn & "')"
            CurrentDb.Execute strSQL
        Next
    DoCmd.Close acForm, fn
Next
End Sub

 

[vburrows]

Ok I am putting into a module the above code edited in this manner

Sub BuildFormsTable()
Dim tdf As Object
Dim fld, fn, tn, strSQL

'Create table
'strSQL = "Create Table tblForms (TableName Text (100),FieldName Text(100))"

'Clear existing table
'strSQL = "Delete From tblForms"

'CurrentDb.Execute strSQL
For Each frm In CurrentProject.AllForms

DoCmd.OpenForm frm.name, acDesign
Set F = Forms(frm.name)
fn = F.name
For Each ctl In F.Controls
cn = ctl.name
strSQL = "INSERT INTO tblForms ( " _
& frm.name & ",ControlName) VALUES ('" & fn & "','" & cn & "')"
Debug.Print strSQL
CurrentDb.Execute strSQL
Next
DoCmd.close acForm, fn
Next
End Sub

I comented out the create and delete strings after I created the table the first time.

but I am getting a syntax error on my insert statement maybe i have looked at it for to long but can someone else run this and tell me what I am doing wrong?

 

[Remou]

The code was quickly edited from a longer piece, try this:

Sub BuildFormsTable()
Dim tdf As Object
Dim fld, fn, tn, strSQL

'Create table
strSQL = "Create Table tblForms (FormName Text(100),ControlName Text(100))"

'Clear existing table
'strSQL = "Delete From tblForms"

CurrentDb.Execute strSQL
For Each frm In CurrentProject.AllForms
    
    DoCmd.OpenForm frm.Name, acDesign
        Set f = Forms(frm.Name)
        fn = f.Name
        For Each ctl In f.Controls
            cn = ctl.Name
strSQL = "INSERT INTO tblForms ( " _
                    & "FormName,ControlName) VALUES ('" _
                    & fn & "','" & cn & "')"
            CurrentDb.Execute strSQL
        Next
    DoCmd.Close acForm, fn
Next
End Sub

 

[vburrows]

Thank you very much Remou I found an error in the 2 varibles and it is working great!!!!