reporting table def criteria

[markhirt]

I need to generate a report that lists all fields and the first value of each field in a report. Any suggestions?

 

[JoanneM]

hello

I had to do some thing a littl similar see vba below

This prints it to the debug window
you may have a table ready or want to create one each time this is run - how ever you want to do it.


This uses 3 routines

just in case you need data types as well.

Sub Print_tableNames
This just lists all tables in the DB

Each time a non system table is identified it passes
name to this routine

Sub Print_Field_Names(tblname As String)

this provides the data.
Additionally it provides the data type by calling

Function FieldType(intType As Integer) As String
THis one is optional of course.
------------------------------------------------

Sub Print_tableNames()
Dim mydb As Database
Set mydb = CurrentDb
Dim rst As DAO.Recordset, intI As Integer
Dim fld As Field
Dim tdf As TableDef
For Each tdf In mydb.TableDefs
If InStr(1, tdf.Name, "msys&quot = 0 Then
Print_Field_Names (tdf.Name) ' excludes system tables
Else
End If
Next
End Sub




Sub Print_Field_Names(tblname As String)
Dim mydb As Database
Set mydb = CurrentDb
Dim rst As DAO.Recordset, intI As Integer
Dim fld As Field


Set rst = mydb.OpenRecordset(tblname, dbOpenDynaset)
rst.MoveFirst
For Each fld In rst.Fields

Debug.Print tblname & "," & fld.Name & "," & FieldType(fld.Type) & "," & fld.Size & " , "; fld.Value
Next
End Sub




Function FieldType(intType As Integer) As String

Select Case intType
Case dbBoolean
FieldType = "dbBoolean"
Case dbByte
FieldType = "dbByte"
Case dbInteger
FieldType = "dbInteger"
Case dbLong
FieldType = "dbLong"
Case dbCurrency
FieldType = "dbCurrency"
Case dbSingle
FieldType = "dbSingle"
Case dbDouble
FieldType = "dbDouble"
Case dbDate
FieldType = "dbDate"
Case dbText
FieldType = "dbText"
Case dbLongBinary
FieldType = "dbLongBinary"
Case dbMemo
FieldType = "dbMemo"
Case dbGUID
FieldType = "dbGUID"
End Select

End Function


hope this is ok

regards jo

 

[markhirt]

thanks!