Use different column based on user input to update data

[mikebrewer]

Hey all,

kind of an odd question but here is what I'm trying to do...

I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.

How can i make some form of a loop that when I'm attempting to update data, i pull the correct column? see the below code snippet i was starting with:

strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
I NEED HELP RIGHT HERE TO LOOK FOR THE COLUMN NUMBER AND THEN ASSIGN THE COLUMN NUMBER TO CUSTOMERID. IF IT IS 1, I WOULD WANT rst!F1, if it is 2, then rst!F2 and so on and so forth. I feel there must be an easy way to do this other than a long check.
Else
CustomerID= DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")
End If


Hopefully any of that makes sense.

thanks!!

 

[mikebrewer]

Nevermind everyone, i figured it out.

thanks though!

 

[DennisOJensen]

Oh well you should post your answer so that it becomes an informative then

 

[mikebrewer]

well, with my recordset, i used this:

Customer_ID = rst.Fields(ColumnName)

Column name was determined by taking the user definition of which column the field would be found in. so if the user input was column 3 then the column name would be f3.

Hope that helps!

 

[DennisOJensen]

You can also do that with the actual field name I believe as the field name should be a constant value such as:

Customer_Id = rst.Fields("RecordFieldName") and this is a whole lot less cryptic than the field index value of 3

Oh and just curious -- as I see this a lot -- why "ID"? I ask because ID would imply an acronym where the I is a word and the D is a word such as "Intelligent Deceiver" however it is most often use in place of the word "Identifier" as an abbreviation but an abbreviation of Identifier would be "Id" not "ID". Again just a curiosity item on my part.

 

[mikebrewer]

the only reason for the index is because it can change. some users have data that is customer id in column 2 and some in column 3, etc. so I kind of have to determine that on the fly so the variable helps with that. if i try to statically assign the column name, the user wouldn't have control (unless I'm thinking wrong).

and ID is really identifier. I just capitalize both out of personal preference, nothing else really.

 

[DennisOJensen]

Okay in recordsets like in tables the fieldname is often a constant value and in your case the Index is not so let's say we are working with a recordset that returns FirstName and LastName.

If the user chooses to list it as FirstName LastName it could be referenced with the indexes as follows

UserFirstName = rst.Fields(0)
UserLastName = rst.Fields(1)

However if your user chose to list it LastName FirstName it would be referenced with the indexes as follows:

UserFirstName = rst.Fields(1)
UserLastName = rst.Fields(0)

Or instead of using the indexes you can use the field name as follows:

UserFirstName = rst.Fields("FirstName")
UserLastName = rst.Fields("LastName")

This way it does not matter what order the user places them in as long as the field name stays the same.