Getting a table record name

[Peter Bellamy]

I want to offer a user Combo box filled with the contents one of the records of a table.
The user has already made a selection of the Table required (by selecting an item they are interested in) and its name is available in the VBA code.

The problem is I do not know the exact record name.
For example
Chosen Table name "Company Product"
The Field I want will be named something like "company_serialno" , it will always be "something_serialno" and is always the third record in the Table.

How can I find its exact wording?

Cheers

 

[dcb]

A few ways:

1. Create a table that matches the selection and the sql to be used
2. Write a if clause for each different option
3. Use Currentdb.TableDefs to find the column name
4. If the column name is always logical ie tablename & "_serialNo" you could build it

Its up to you where you want to go - the answer to your question is look at the help file for tabledefs

 

[Peter Bellamy]

Thans for your reply.

Unfortunately the Table names were created inconsistently so it would very hard to build the record names from them.

I thought tabledef might be the way to go but have never used it, so I have no idea how to get column name from it.
The Table is a linked one, will that make a difference?

I have searched through help and not found it to be so! (I rarely do as I never seem to have the correct terminology!)

 

[boblarson]

...and is always the third record in the Table.

How can you be sure about that? The only sure way of getting the third record (row) will be to use a query and to make sure that there is some sort of ordering mechanism which will keep track of the ordinal position. The data in tables are not stored by Access in any particular order (it does not store it in the order it is entered, even though many times it may APPEAR to be doing so).

Also, not quite sure, given your description, of exactly what you are looking to do. Are you wanting to bring back all of the rows of data for a particular column in a particular table? I thought so, until I read about the "third record" part.

 

[dcb]

How can you be sure about that? The only sure way of getting the third record (row) will be to use a query and to make sure that there is some sort of ordering mechanism which will keep track of the ordinal position. The data in tables are not stored by Access in any particular order (it does not store it in the order it is entered, even though many times it may APPEAR to be doing so).

Also, not quite sure, given your description, of exactly what you are looking to do. Are you wanting to bring back all of the rows of data for a particular column in a particular table? I thought so, until I read about the "third record" part.

I read it differently: I think he is looking for a Column Name for the query? I automatically assumed that row was meant to be column - may be wrong....

 

[Peter Bellamy]

I said I had probems with the terminology!

I want the column name that includes the string "_serialno", I take your point it may not be the 3rd column.

With that name I can populate a list with all the row data for the user to select from.

 

[Peter Bellamy]

Sorry, not row data, all the data in that coloumn!

 

[boblarson]

Okay, this should help.

Put this into a STANDARD module (not form or report module) and then call it by passing the table name you want and the text you want it to search for in the table.

Function GetColName(strTableName As String, strInput As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    On Error GoTo err_handler
    
    Set db = CurrentDb
    
    Set tdf = db.TableDefs(strTableName)
    
    For Each fld In tdf.Fields
        If InStr(1, fld.Name, strInput, vbTextCompare) > 0 Then
                GetColName = fld.Name
                Exit For
        End If
    Next
    If GetColName = "" Then
        GetColName = "No match"
    End If
    
GetColName_Exit:
     Exit Function
     
err_handler:
    MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
    Resume GetColName_Exit
End Function

So, for example if you wanted to check Table1 for a column with the text "_serialNo" you would call it by doing this:

Dim myCol As String

myCol = GetColName("Table1", "_serialNo")

And myCol would get the full column name.

If the column doesn't exist, it will return the words "No Match."

 

[Peter Bellamy]

Thanks that is brilliant!

So I can understand the code can you please confirm that:

The Column Names, in long hand, are:
DAO.Tabledef.Field.Name

And without the .Name it would be looking at the content of the fields.

Thanks

 

[boblarson]

Thanks that is brilliant!

So I can understand the code can you please confirm that:

The Column Names, in long hand, are:
DAO.Tabledef.Field.Name

Essentially, yes, but in that context you would need the field number. So

DAO.Tabledefs("TableNameHere").Field(x).Name

And without the .Name it would be looking at the content of the fields.

No, you would need .Value as it doesn't have .Value as the default.

 

[Peter Bellamy]

Thanks again