Get Field Name From Column Number

[chalupabatman]

This may be explained elsewhere, but I am not finding it...


What I am after, is for a table in my access database, for a user to input a number and in a messagebox display the column name. For example, let's say I have this dataset-up

tblTest
name short text
address short text
city short text
state short text
zip number


And if a user inputs 3 I would want city returned since city is the 3rd field in the table.

How is this accomplished in VBA Access 2013"?

[June7]

Maybe you could explain why you want such an unusual requirement. Maybe this isn't really what you need to do.

Could have a combobox with RowSourceType set to FieldList and RowSource set to a table or query or SQL statement. Then user can select field name from the list.

[chalupabatman]

Maybe you could explain why you want such an unusual requirement. Maybe this isn't really what you need to do.

Could have a combobox with RowSourceType set to FieldList and RowSource set to a table or query or SQL statement. Then user can select field name from the list.

I agree it is an unusual requirement, but unfortunately it is what I need to achieve.

To input a number, and it return the corresponding field name (or if the field does not exist, return invalid)

[June7]

Why a number? Why let users input an invalid number to begin with? The combobox restricted list controls user input. Why will that not serve your purpose? Could be a ListBox instead.

Okay, if you must - consider:

Debug.Print CurrentDb.TableDefs("Rates").Fields(Me.tbxInputNum ).Name

That was easier than I expected. Field index begins with 0. So field 1 would be index 0. User would have to enter 0 to get field 1 name. Good luck educating users. Oh, wait, code can subtract 1 from the user input.

Debug.Print CurrentDb.TableDefs("Rates").Fields(Me.tbxInputNum - 1).Name

If field index not found, triggers run-time error 3265. Build error handler code to deal with it. Also, validate user input is a number. Can set textbox ValidationRule property.

[orange]

Here's a routine that may help, but I have to ask ---Why??

'---------------------------------------------------------------------------------------
' Procedure : ColumnNamesFromNumber
' Author    : mellon
' Date      : 23-Aug-2017
' Purpose   : Print table column name and table column index/position
'---------------------------------------------------------------------------------------
'
Sub ColumnNamesFromNumber()
          Dim fld As DAO.Field
          Dim rst As DAO.Recordset
          Dim i As Integer

10       On Error GoTo ColumnNamesFromNumber_Error

20        Set rst = CurrentDb.OpenRecordset("Your tablename goes here")


30        For Each fld In rst.fields
40            Debug.Print "field (" & i & ")  " & fld.name
50            i = i + 1
60        Next
        
ColumnNamesFromNumber_Exit:
70       Exit Sub

ColumnNamesFromNumber_Error:
80       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ColumnNamesFromNumber of Module AWF_Related"
90       Resume ColumnNamesFromNumber_Exit
End Sub

[chalupabatman]

@orange - an excel file is being imported and we need to know where certain "key" columns are in the spreadsheet. So the thought for set-up is to have the user input the column letter into an access form, then have access interpret that column letter into a numeric value to get the field name from the table. Now that we have captured the field name based off the Excel Spreadsheet Letter we can use that field name to build out a query.

[orange]

I see this has been marked solved.
If I had a data file to import into Access, I would try to set up a "standard" format or a set of formats (if there were a number of potential files to import) so that there was no guessing on which field was in which position.