Can I create A Report from This? 1

[theif68]

This gives me a layout of a table in the debug screen. How can this be alterd to show in a report?

Public Sub fieldProperties()
Dim tblStandeardLayout As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set rs = CurrentDb.OpenRecordset("tblStandardLayout")
For Each fld In rs.Fields
Debug.Print fld.Name & " " & fld.Size
Next fld
End Sub

 

[rjoubert]

You could just use the Documenter tool in Access to give you that information. Click on Tools - Analyze - Documenter.

 

[theif68]

Thanks but this is something that I would like to add to a macro to be ran at the begining of a job.

 

[rjoubert]

Create a new table with two fields in it...FieldName and FieldSize. In your code that you already posted, append a new record to that table within your loop. Then create a report based on the new table. At the end of your code, you can also add a line to open your report.

 

[rjoubert]

After you add your new table and report...I'll call the table "Documenter" and the report "rptDocumenter"...try this code...

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fld As DAO.Field
  
    Set rs = CurrentDb.OpenRecordset("tblStandardLayout")
    Set rs2 = CurrentDb.OpenRecordset("Documenter")
    
    For Each fld In rs.Fields
        'Debug.Print fld.Name & "        " & fld.Size
        rs2.AddNew
            rs2("FieldName") = fld.Name
            rs2("FieldSize") = fld.Size
        rs2.Update
    Next fld

    DoCmd.OpenReport "rptDocumenter", acViewPreview

    rs.Close
    Set rs = nothing
    rs2.Close
    Set rs2 = nothing

 

[theif68]

Wish I new how to create a table from this or I would.
Thanks again guys.

 

[rjoubert]

Do you mean you want to create a table from the code too?

 

[rjoubert]

Try this out...

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fld As DAO.Field
  
    DoCmd.RunSQL "CREATE TABLE Documenter (FieldName Text(50), FieldSize Text(10))"
    
    Set rs = CurrentDb.OpenRecordset("tblStandardLayout")
    Set rs2 = CurrentDb.OpenRecordset("Documenter")
    
    For Each fld In rs.Fields
        rs2.AddNew
            rs2("FieldName") = fld.Name
            rs2("FieldSize") = fld.Size
        rs2.Update
    Next fld

    DoCmd.OpenReport "rptDocumenter", acViewPreview

 

[theif68]

Thanks alot, works great.