Concatenating a variable

[sjdeetz]

I have a form where the user will select from a combo box the table information they wish edit or add. I am using ADO and I want to pull the table down from SQL Server into a table in Access 2003. I am trying to loop through the list of field names and assign each field to a specific string. Each table has less than 15 fields so I created 15 strings as Dim strFld1 As String, Dim strFld2 As String, etc. But during my loop I want to identify which strFld? as a concatenated string combining the "strFld" and the integer counter. Below is a code sample:

rs1.Open strSQL1, cnnlocal, adOpenDynamic, adLockOptimistic
intCount = rs1.Fields.Count
intStart = 1

Do Until intStart = intCount
CStr("strFld" & intStart) = rs1.Fields(intStart - 1).Name
intStart = intStart + 1
Loop

VBA is giving error messages whether I use CStr("strFld" & intStart), strFLD & intStart, or strFld + intStart. I know there should be a simple way to get this to work but I am currently baffled

Thanks in advance for any suggestions.

 

[DCrake]

Remove the quotes from around the strFld variable

 

[sjdeetz]

Compile error when CStr(strFld & intStart) = rs1.Fields(intStart - 1).Name is used.

 

[DCrake]

Try

strFld & cstr(intStart) = rs1.Fields(intStart - 1).Name


However you cannot start a line with a variable name

but

Rs1(strFld & cstr(intStart)) = rs1.Fields(intStart - 1).Name

 

[sjdeetz]

It seems that no matter how I try to create the strFld? variables Access doesn't like it. So in a different tack I use the IF statement to list all variables instead. It works but the elegance is lost

Do Until intStart = intCount
If intStart = 1 Then
strFld1 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 2 Then
strFld2 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 3 Then
strFld3 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 4 Then
strFld4 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 5 Then
strFld5 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 6 Then
strFld6 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 7 Then
strFld7 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 8 Then
strFld8 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 9 Then
strFld9 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 10 Then
strFld10 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 11 Then
strFld11 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 12 Then
strFld12 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 13 Then
strFld13 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 14 Then
strFld14 = rs1.Fields(intStart - 1).Name
ElseIf intStart = 15 Then
strFld15 = rs1.Fields(intStart - 1).Name
End If

intStart = intStart + 1
Loop

 

[boblarson]

first off -

Why are you creating those variables? What is the purpose?

I think there is a better way but I need to know what you plan on doing with these in order to come up with the better way.

 

[sjdeetz]

Bob,

I am putting together a sales quote application and one of my development goals was to find a way that end users can update, add, or delete not only product line items, but product lines themselves.

What got me to this current development spot is my design where each product line is a table. Each product line has some similar fields but they also have their own unique fields as well. The product line information resides on SQL Server and I want the user to be able to pull a product line down into Access and work with the information before updating SQL Server. I am staying away from an ODBC connection to try and limit a salespersons ability to change prices and secondly so I do not have to set every user's PC up with an ODBC connection. I have used ADO in the past to pull information down and DAO to transfer the data into Access tables. However, that was with tables that were more "static". One of the design questions that I am attempting to answer with these posts is "How would I be able to copy a table's information from SQL Server, work on it in an Access table, and send the new, deleted, or updated information back to SQL Server if I really didn't know the table name or structure?"

I hope this helps. Thanks

 

[boblarson]

1. The table design is bad - very bad. You should not have a table for each product line. They should be ROWS in a table.

2. It matters not that there are unique fields as there is a normalized way to handle that. See my sample here.

3. I would seriously consider normalizing this before you get even further into FrankenDatabase.

 

[sjdeetz]

Bob,

I will rethink the design as a many-to-many to avoid these development issues. Thanks for the sample design.