Syntax for referring to a variable Field name

[RedSkies]

I currently have a set of data describing skillsets for call center agents which is arranged as follows:

Name Skillset
Joe Blow Skill_A
Joe Blow Skill_B
Joe Blow Skill_C
Jane Doe Skill_C
Jane Doe Skill_D

The user needs it converted to this format in order to bulk load it into another application:

Name Skillset1 Skillset2 Skillset3 Skillset4...
Joe Blow Skill_A Skill_B Skill_C
Jane Doe Skill_C Skill_D

I'm attempting to loop through the recordset and use the .AddNew method to create the record and populate it with the agent name, ID, location etc. Then I'm using the .Update method to add each skill set record associated with the current agent to subsequent Skillset columns in the converted table. However, since the "Skillset" fields (10 of them) will all have similar names (Skillsetn) I need to know how to refer to the field name generically for the Update. I can't seem to figure that part out .

The following snip of code isn't working for me:

Do While i <= 10
    i = i + 1

        Set rsConvSkillSets = db.openrecordset("SELECT * FROM tblConvertedAgentSkills WHERE SymposiumAgentID = '" & strAgtID & "';", dbopendynaset)
        strTmpSkill = "Skillset" & Str(i)
        Set fldSkill = strTmpSkill ' THIS IS WHAT I'M DOING WRONG I THINK!

        With rsConvSkillSets
            .Edit
            !fldSkill.Value = strImpSkillSet ' THIS PROBABLY ISN'T RIGHT EITHER!
            .Update
        End With
Loop

I'm clueless and haven't been able to find anything in Help on how to approach it.

Hope this makes sense. Thanks in advance.

 

[scottn]

you're on the right track. a recordset has a Fields collection, and you can use a string which matches the field name to indicate the field you're interested in. something like this:

'Build the field name as you would any string
strFieldName = "SkillSet" & SomeInteger

'Then use it to reference the field
With rst
    .Edit
        .Fields(strFieldName) = "VBA Programmer"
    .Update
End With

 

[Mile-O]

Set rsConvSkillSets = db.openrecordset("SELECT * FROM tblConvertedAgentSkills.....

This line should not be within the loop. You are effectively creating the same query and destroying it ten times.

Just make a stored query and use this as the recordsource - your database won't bloat as much.

 

[RedSkies]

Thanks!

Yup, you're right about setting the RS within the Loop - stoooopid! Dunno what I was thinking.
Thanks for your help. I'll give it a try.

 

[RedSkies]

Not in collection error

Scott, thanks for your suggestion but when I try it, I get an "Item not found in this Collection" error.

Function fAddSkills(strAgtID As String)
Dim rsConvSkillSets As DAO.Recordset
Dim strImpSkillSet As String
Dim strImpPriority As String
Dim strConvSkillSet As String
Dim strConvPriority As String
Dim strSkillField As String
Dim strPriorityField As String
Dim i As Integer
i = 0

strSQL = "SELECT * FROM tblConvertedAgentSkills WHERE SymposiumAgentID = '" & strAgtID & "';"
Debug.Print strSQL
Set rsConvSkillSets = db.openrecordset(strSQL, dbopendynaset) 'destination table
rsImport.MoveFirst

Do While rsImport.EOF = False 'source table. recordset is set in another routine which calls this one.
If i <= 10 Then
    i = i + 1
    strImpSkillSet = rsImport![Skillset Name].Value
    strImpPriority = rsImport![Priority].Value
    strSkillField = "Skillset" & Str(i)
    strPriorityField = "Priority" & Str(i)
        With rsConvSkillSets
             .Edit
                .Fields(strSkillField) = strImpSkillSet
                .Fields(strPriorityField) = strImpPriority
                  
            .Update
        End With
    rsConvSkillSets.MoveNext
    Else
        Exit Do
End If
Loop
Set rsConvSkillSets = Nothing
End Function

Feel free to point out anything else that looks goofy. I haven't worked with this stuff in a while.

Thanks again!

 

[scottn]

that error message is usually an indication that the named field doesn't exist in your recordset. are you sure the string you're building matches the field name exactly? also, check the routine in which you're opening the source recordset to make sure that the recordset you're opening actually has those fields that you want to work with. if you're still having trouble maybe you could post that part of your code as well...

by the way, it looks like your .MoveNext statement in the loop is for the wrong recordset. you're trying to step through rsImport, right?

 

[RedSkies]

Thanks again

Yes, you're right about the recordset. It should be rsImport.MoveNext.

I've stepped through the code watching the variable values and am positive that the variable evaluates to the correct table field name. But for some reason, the code:

.Fields(strSkillField) = strImpSkillSet

can't seem to handle a variable. If I put in the string literal in place of the field name variable strSkillField, i.e

.Fields("SkillSet1") = strImpSkillSet

the code works fine.

Any other thoughts on this? Thanks

 

[RedSkies]

I figured it out

Never mind. The problem was that I was using Str to convert an integer to a string which apparently sticks in a space, i.e. The integer "1" evaluates to " 1" using Str. The solution is to use Format.

Thanks again for your help everyone.

 

[scottn]

you know, the following actually works fine -- the integer is implicitly cast to a string before the concatenation.

Dim SomeInt as Integer
Dim SomeString as String
Dim AnotherString as String

AnotherString = SomeString & SomeInt   'works fine -- implicit cast

if you want to be explicit (sometimes you actually have to be), you can explicitly cast one data type to another using the CType functions. For example:

AnotherString = SomeString & CStr(SomeInt)  'also works -- explicit cast

if you look up "type conversion functions" in the help, you'll see a whole list of these functions. glad you got the bug worked out.