Table Properties 1

[Mats]

Hello,

Does anyone have the code for looping through the properties of a table (fieldnames, type, description)? I would need to show the type and decription of a field in a table based on the fieldname the user types. Is there any way of getting the recordcount of a table without using the count function of sql?

Thanks,
Mats

 

[kathryn]

Here you go! txtFieldName is the text taht the user typed.

dim tdf as tabledef
dim fld as field

set tdf = currentdb.tabledefs("YourTableName&quot

Now you can access tdf.Fields(txtFieldName).name, tdf.Fields(txtFieldName).fieldsize, tdf.Fields(txtFieldName).type

You can find all the accessible properties by looking at the help topic Field Object and choosing Properties.

To get the recordcount of a table:

dim rst as recordset
dim intCount as integer

set rst = currentdb.openrecordset("YourTableName&quot
rst.movelast
intCount = rst.recordcount



Hope this helps.





Kathryn

 

[Mats]

Thanks a lot!

 

[Mats]

Hello again,

Something is not right, I keep getting 'runtime error 3420, object invalid or no longer set'. Here's the code I use, Input is the table, and PORID is a field in the table.

dim tdf as tabledefs
dim fld as field (??? what is this for)
dim str as string

set tdf = currentdb.tabledefs("Input&quot
str= tdf.fields("PORID&quot.Name

Thanks,
Mats

 

[kathryn]

Good morning!

dim fld as field can be deleted. When I first worked on this problem, I thought I needed it.

dim tdf as tabledefs needs to be changed to
dim tdf as tabledef (singular)

Let me know if that works.



Kathryn

 

[Mats]

No, still no success. Actually I already had dim tdf as tabledef.

Mats

 

[kathryn]

Do you know how to run the code by stepping through it line by line? Or do you know what line is causing the error? Could you post the entire procedure?

Thanks.

Kathryn

 

[Mats]

Hi, this is the short version of my code that I use to test this function:

Dim tdf As TableDef
Dim str As String
Set tdf = CurrentDb.TableDefs("Input&quot
str = tdf.fields("PORID&quot.Name

When stepping through the code the error comes on the last line.

Thanks,
Mats

 

[kathryn]

Ok, when stepping through the code, when you get to the last line, open the debug window (Ctrl-G)and see if you can access ANY of the properties of the field.

Type the following:

?tdf.fields("PORID&quot.type

or

?tdf.fields("PORID&quot.size

Also, look in the top half of the debug window and make sure that tdf is of type TableDef.

Let me know.

Kathryn

 

[Mats]

I got it!
For some reason it works if I use this

dim tdf as tabledef
dim dbs as database

set dbs=currrentdb
set tdf=dbs.tabledefs("Input&quot
....

BTW, do you know how to access either the description or caption properties of a table?

Thanks for all your help,
Mats