DBGrid Column Field Types

[kennedymr2]

I have a DBgrid on my form

The database is an Access .mdb

Dim strsql
strsql = "SELECT * FROM results "
Data.RecordSource = strsql
Data.Refresh

The grid loads up with the database records, all ok.

????

Is there a way of of determining the columns loaded, ?? the field type.. eg Text Currency Date etc.

I want to be able to validate any alterations made

Appreciate any help

Kennedym2

 

[Andrzejek]

From your example:[tt]
Dim strsql
strsql = "SELECT * FROM results "
Data.RecordSource = strsql
Data.Refresh[/tt]

In another words you have something like:[tt]
Data.RecordSource = "SELECT * FROM results "
Data.Refresh[/tt]

Your RecordSource is set to a String, no different to[tt]
Data.RecordSource = "Old McDonald had a farm "
Data.Refresh[/tt]

Try something like (code not tested):[tt]
Dim strsql[blue]
Dim rst As RecordSet[/blue]
strsql = "SELECT * FROM results "[blue]
rst.open strsql[/blue]
Data.RecordSource = [blue]rst[/blue]
Data.Refresh[/tt]

Plus, this is a VB Forum, but you have a VB[red]A[/red] question - try to ask your question here: forum705


Have fun.

---- Andy

 

[kennedymr2]

PostAndrzejek... thanks for the reply

This is a vb6 issue not vba..

I am opening the dbgrid all ok...no problems.

BUT what i am trying to do is determine (automatically), when a column is entered ... is it text,currency,date etc..

At the moment.. i look at the header to get the field name, then look at the recordset field name .type... and then i have the type eg 10 = text.. this is a little messy...

I was hoping for a simple way.. if there is any.


Regards Kennedymr2

 

[Andrzejek]

I see.
In my opinion checking the Type of the field in your recordset is the best (and only...?) way to know what you have. And there are not many types: text, number, date, number, and maybe a couple more.
Unless you have standards of naming your fields in your DB that indicate the Type of the field - I would not rely on Names of the fields.
If you show the code of 'messy' type of fields, maybe it could be made 'cleaner'....

Have fun.

---- Andy

 

[tedsmith]

You can set the format of each column in the datagrid properties.
If you than try to enter a text in a number formatted column or an invalid date you get a zero or an error message

Also if you use the routine that operates when you change data in a cell you can examine what you entered and apply a set a rules to the cell you just left.

 

[kennedymr2]

Andrzejek...

What i have done is..

Dim objField, i
i = 0
For Each objField In rs.Fields
List1.AddItem objField.Name & "," & objField.Type
i = i + 1
Next

list1 is hidden !!!!

IN DBGrid_Change()

I check the col header name against the list1 .type and see if it is eg 4 for decimal 5 for currency etc

It works fine....

I was just trying to see if there was a more simple way

Appreciate your interest in helping with this problem

Regards Kennedymr2

 

[Andrzejek]

If you already have your recordset ready, you may want to do:

Dim i As Integer

For i = 0 To rst.Fields.Count - 1
    List1.AddItem rst.Fields(i).Name & "," & rst.Fields(i).Type
Next i

Which will do the same thing.

But the question is - why do you need to know the type of your fields? I am sure you have a reason.

You may try:
add a field to your table(s) indicating the Type of the field (4 for decimal, 5 for currency, etc) and have another table in your DB like:
[tt]
tbl_Data_Types
Data_ID Data_Type
1 number
2 text
3 boolean
4 decimal
5 currency
... ...[/tt]

This way you can have the information in your rst ready to use.

Have fun.

---- Andy

 

[kennedymr2]

Thanks for your reply....

I need to know the field type so i can stop users typing alpha data into a numeric fields etc.......

I an using this routine for a large number of forms, and do not wish to have to format the columns individually for each individual form.

What i have done seems to work 100%..


Regards Kennedymr2