Field Name Reference with Variable as Part of Name

[shaggy]

Can a function be written to create a field name reference using a variable(s) as part of the name?

Example: A=1
B=2

Adding A+B should give the numeric at the end of the field name.
I would like to refer to [Field3]. Something like [Field(A+B)].

 

[namliam]

Anything can be done, but your discribtion is too general I think...

Can you be more specific?

 

[RuralGuy]

Use the Controls collection:
Me.Controls("Field" & (A+B)).SetFocus

P.S. Are you talking about Fields in a table/query of Controls on a form? I answered the second part!

 

[shaggy]

I have an application that creates a backup file of data in an Oracle db as a comma delimited text file. I’m basically trying to reverse the process and take the comma delimited file and turn it into multiple tables in Access with the same field names and data contained in the Oracle DB.

I’ve written an Access application that imports the data from the backup file and allows me to view or print reports of the contents of the backup file.


I’m using the transfertext action in a macro to import the comma delimited text file. I end up with one table with Field1 through Field217. From this table I have queries that separate the data into multiple tables. The queries work fine for all but one of the tables. One of the tables has a field where the original data contains one or more commas, the backup file contains the commas, but also has commas separating the fields. When I import the text file it breaks up the text into fields between the commas. Some of the single fields end up being split into multiple fields if there are actual commas in them instead of just commas separating the fields. Not only does this cause multiple fields to have to be combined, it also shifts any following data into the next field(s).

I’ve written a query that works, but only up to 3 actual commas in a field. I can’t account for anymore or I get a “Too Complex to be Evaluated” error.

I was thinking of a way to use a couple of variables to determine the fields to combine and the starting field based on commas in the previous fields.

There’s probably a better way to import this file through some code that will just import the data between the 1st 2 commas into Field1, between the 2nd 2 commas into Field2, etc…


Here’s some sample data

Oracle DB
Field1 Field2 Field3 Field4
1. abcd 1234 795 dfge
2. ace,gf 2213 292 dbcb
3. gnr,qrc,b 3351 925 vxgp

Backup Text File
1. "abcd","1234","795","dfge"
2. "ace,gf","2213","292","dbcb"
3 "gnr,qrc,b","3351","925","vxgp"

Access (Imported Backup Text File)
Field1 Field2 Field3 Field4 Field5 Field6
1. "abcd" "1234" "795" "dfge" null null
2. "ace gf" "2213" "292" "dbcb" null
3. "gnr qrc b" "3351" "925" "vxgp"

 

[RuralGuy]

I don't have any suggestions to resolve your issue but the table/query DAO Recordset has the equivalent of the Controls collection in the Fields collection.
Me.TextBox = Rs.Fields("Field" & (A+B))

 

[Bat17]

If the structure is the same each time then you should be able to do it by hand once and save the imprt specification, then use the specification when you use the TransferText. this should then let you bring the data in correctly.

HTH

Peter