Column Count

[CBRAY]

Hi I have a list box on a form where I can select any table in my database, the onclick event puts my selection into the rowsource of another listbox.
Private Sub lsttables_Click()
tblsamp.RowSource = lsttables.Value

tblsamp then acts as a sample view for the selected table. At the moment I have the column count of tblsamp set to ten. Is there anyway I can count the number of columns in the table selected from lsttables and pass this to column count field of tblsamp?


thanks

 

[ChrisLomax]

You could take a look at the DCount Function, you pass in what table and field you want to count and the WHERE query, so the WHERE query would be the table id and it would count the amount of rows in the table that correspond to that table id.

Another way would be to do a DAO recordset query and count the amount of rows by incrementing a variable each pass through a row.

It can be done many ways.

 

[CBRAY]

Sorry I dont understand, I want to count columns not rows

 

[ChrisLomax]

From what i can gather you have managed to put your columns of your tables into a table so that you can analyse them?

Thats what i understand you have done?

 

[CBRAY]

I have one list box 'lsttables' which uses MsysObjects to show all tables in the database. I select a table from 'lsttables' and this sets the rowsource of another list box 'tblsamp' to the table selected in 'lsttables'. tblsamp now shows me the first X number of records for this table.

I have manually set the column count of 'tblsamp' to 10.

I want the column count of list box 'tblsamp' to change to the correct number of columns for any table selected in 'lsttables'.

Cheers

 

[david.brent]

This little sub will give you the general idea. There is probably a more elegant way to do this but this is all I could think of at the moment.

Sub ColCount()
Dim myRecs As DAO.Recordset

Set myRecs = CurrentDb.OpenRecordset("MSysAccessObjects")

MsgBox myRecs.Fields.Count

Set myRecs = Nothing
End Sub

Take care

Carl

 

[CBRAY]

Carl

I amended your advise to

Dim myRecs As DAO.Recordset

Set myRecs = CurrentDb.OpenRecordset(lsttables)

tblsamp.ColumnCount = myRecs.Fields.Count

tblsamp.RowSource = lsttables.Value

Beautiful!!

Thanks

Chris

 

[CBRAY]

Carl

I amended your advise to

Dim myRecs As DAO.Recordset

Set myRecs = CurrentDb.OpenRecordset(lsttables)

tblsamp.ColumnCount = myRecs.Fields.Count

tblsamp.RowSource = lsttables.Value

Beautiful!!

Thanks

Chris