Selecting a field/column

[usingVB]

Hi all,

Just wondering if there is a way in VB to select a field/column in a table based on part of its name.

For example, I am currently getting a column by:

Name.field = "USA_ID"

However, I want to be able to use different datasets with the code, and therefore get the column that ends in "_ID" - So I could have UK_ID, Australia_ID etc...We can assume there will only ever be one column that will end with "_ID"

..something like *_ID?

Can I do this?

Hope this makes sense..

Cheers

 

[HarleyQuinn]

One way would be to use the TableDefs collection.

Something like:

Public Function GetIDColumn(sTableName As String) As String

Dim db As Database
Dim td As TableDef
Dim fld As Field

    Set db = CurrentDb()
    
    Set td = db.TableDefs(sTableName)
    
    For Each fld In td.Fields
        If InStr(1, fld.Name, "_ID") > 0 Then
            GetIDColumn = fld.Name
            Exit For
        End If
    Next fld

    Set td = Nothing
    Set db = Nothing
    
End Function

You can call this using something like:

Name.field = GetIDColumn("YourTablesName")

Because it's an example I've not included any error handling or dealing with tables that contain no ID column.

As I say, this is one way to achieve this, there are others.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

Hi,

"...So I could have UK_ID, Australia_ID ..."

Not a very good table design, my friend! Your Country ID Data, ought to be in a Country_ID field. Its Database 101.

http://en.wikipedia.org/wiki/Database_normalization

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Nodding in agreement with Skip on that one.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.