almost there...field type and value of first row

[markhirt]

OK. I'm almost there. have the following code to get the field type, but it returns a number instead of the text... anyone know how I can get returned 'Text' instead of '10'??

Then what about the first row. Can I set txt.TEXT = the result of a query statement?

Please help.

 

[JoanneM]

Did you go down the previous page of the code I sent you it took care of that glitch

and as for the first row.... I must have misunderstood I thought you wanted the values & they were included.

Remember the second procedure went through the field names and called a function passing that integer and returning the text for it's data type.
Tell you what, see if this helps below for now ...

BTW can we keep on one posting it's easier to track.

I just need to go back to your original to make sure I give you a satisfactory solution.
regards
Jo


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

 

[JoanneM]

Here goes

The code below:

1. Goes thru all your tables except system tables
2. Creates a table to store the info you need
3. Gets the
Tablename
field names
field types
field data
and populates the newly created table.
This can be used to source your report.

It does various checks for things along the way - you can step thru it at your leisure.

Copy from here
and paste into a module.

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

'This checks for, and if found will delete the table

Dim tbl As TableDef
Dim stablename As String

'THis deletes any old tempTDF table if it exists
For Each tbl In mydb.TableDefs

stablename = tbl.Name
If stablename = "tbl_TableInfo" Then
mydb.TableDefs.Delete (stablename)
End If
Next

'Recreate it

Dim tbl_TableInfoTDF As TableDef
Set tbl_TableInfoTDF = mydb.CreateTableDef("tbl_TableInfo&quot

With tbl_TableInfoTDF
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' your database

.Fields.Append .CreateField("Tablename", dbText)
.Fields.Append .CreateField("FieldName", dbText)
.Fields.Append .CreateField("Data_Type", dbText)
.Fields.Append .CreateField("Data", dbMemo)

End With
'cycle round the recordset and populate a the newly created temptools table
mydb.TableDefs.Append tbl_TableInfoTDF
mydb.TableDefs.Refresh




' Now that your receiving table is ready to be populated begin your process

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 tdf
MsgBox "done"
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





Dim TableInfoRS As Recordset
Dim Datavalue As String

Set TableInfoRS = mydb.OpenRecordset("tbl_TableInfo", dbOpenDynaset)
TableInfoRS.AddNew
TableInfoRS.Update
TableInfoRS.MoveFirst
DoEvents
Set rst = mydb.OpenRecordset(tblname, dbOpenDynaset)
'check for no records int table
If rst.BOF = -1 And rst.EOF = -1 Then
Datavalue = "Empty"
Else
rst.MoveFirst
End If

For Each fld In rst.Fields

TableInfoRS.Edit
TableInfoRS!Tablename = tblname
TableInfoRS!FieldName = fld.Name
TableInfoRS!Data_Type = FieldType(fld.Type)
'if there is no current record you can't get a value
'so Datavalue of "Empty" is written to the table
If Datavalue <> &quot;Empty&quot; Then
TableInfoRS!Data = fld.Value
Else
TableInfoRS!Data = Datavalue
End If
TableInfoRS.Update
TableInfoRS.AddNew
TableInfoRS.Update
TableInfoRS.MoveNext

'Debug.Print tdf.Name & &quot;,&quot; & fld.Name & &quot;,&quot; & FieldType(fld.Type) & &quot;,&quot; & fld.Size & &quot; , &quot;; fld.Value
Next

rst.Close
TableInfoRS.Close
Set mydb = Nothing
End Sub
Function FieldType(intType As Integer) As String

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

End Function

 

[markhirt]

What version are you using?

When I declare something as DATABASE, it compiles to tell me that's an unrecognizable object type.

 

[markhirt]

have you tested this? I'm encountering an issue or two... By the way, I realized I needed to reference DAO. sorry, I'm a ADO champ and forgot to check for that.

 

[JoanneM]

Yep and keeps on working
I'm in 2k what are you in?

my apologies for not giving you the references - should have done that

What other issues are you having?

jo

 

[markhirt]

nothing now. i'm a little unclear with the direction I want to take populating a dynamic report with the info in the tbl_TableInfo..... any thoughts

 

[JoanneM]

Are you asking for layout /formatting suggestions or have I missed something?

regards

Jo

 

[markhirt]

I guess I'll base the report on a parameter query based on tbl_TableInfo.... Ok on formatting. However, I'll need to select the rows that are from the table a user selects from a combo box.... I figure a parameter query is in order somehow automatically sending the table name to the query for the report.... what do you think? Suggestions on passing the parameter programatically?

Thanks,
Mark

P.S. I'm almost there....

 

[JoanneM]

What exactly do you need to see on the report The fields for a specific table

To do this you could

1.create an unbound combobox on a form
2 source the combo box from the tbl_tblinofo you have just
created.

3 create a query to source your report and in the
criteria of theTablename column -
place
forms!formname!comboboxName

formname being the name of the form the combo box is on
Combobox name being the name of the combo box listng the
tables


3 Create your report using the query as data source

4 On the after UPdate property of the combo box
docmd.openreport, &quot;Report name&quot; acpreview
Report name being the name you have given to your report


hope this is an idea

jo

 

[markhirt]

Jo,

I have the project complete now. Thanks for all your help!

Mark