Count of columns

[StuartDwight]

Can anyone tell me how to count the number of columns in a recordset. The datasource for this recordset is a crosstab query and the columns will vary in number each time it is run.
Many Thanks.

 

[allan57]

I use a combination of 'GetRows' and 'UBound', check then out in VBA help for example.

 

[realvilla]

Once you open a recordset, you can use the "fields" object to find the number of columns using the .Fields.Count property. Look at the following excerpt from some code.

For Each tbl In cat.Tables 'Cycle through every table in the database excluding system tables
If Left(tbl.Name, 4) <> "MSys" And tbl.TYPE = "TABLE" Then 'QUERY = "VIEW"
For i = 0 To tbl.Columns.Count - 1 'Cycle through fields in table chosen
.AddNew
.fields("TableName") = tbl.Name 'get table name
.fields("FieldName") = tbl.Columns(i).Name 'get field name
.fields("FieldType") = ConvFieldType(tbl.Columns(i).TYPE) 'get field type
.fields("Description") = tbl.Columns(i).Properties("Description")
.Update
.MoveNext
Next i
End If
Next

 

[Moniker]

Err...

For tables:
ColumnCount = CurrentDb.TableDefs("YourTableName").Fields.Count

For queries:
ColumnCount = CurrentDb.QueryDefs("YourQueryName").Fields.Count