MS Access VBA - Set Variable Field Name equal to passed string parameter ??

MS Access  VBA - Set Changing Variable Field Name equal to a passed string parameter  ??

I create a string name in my 2ndLoop then try to pass that string name into a field variable for recordset wrkSet from table tblWork

but always get the err Item not found in collection... Simple error i am making ?

All Code works fine until line  until line 33

Here are table field names for tblWork - I am trying to pass AttempType2_1 as the string value to use that recordset field name in an update SQL string... but never get to that point...

RID - AttempType2_1 | AttempType3_1 | AttempType4_1

Private Sub cmdRun_Click()
    Dim i As Integer, iCount As Integer, rst As Recordset, recSet As Recordset
    Dim sSql As String, iSql As String, cSql As String, i2 As Integer
    Dim iCount2 As Integer, mSql As String, newSet As Recordset, aSql As String
    Dim Tr2 As String, Tr3 As String, Tr4 As String, RD As String
    Dim Tr2F As DAO.Field, Tr3F As DAO.Field, Tr4F As DAO.Field, RF As DAO.Field
    Dim wrkSet As Recordset

    Tr2 = "AttempType2_": Tr3 = "AttempType3_": Tr4 = "AttempType4_": RD = "Results_"

    sSql = "qryUnique"

    Set rst = CurrentDb.OpenRecordset(sSql)
    If rst.EOF = False Then
        rst.MoveLast: rst.MoveFirst: iCount = rst.RecordCount - 1: i = 1
        MsgBox "No Recs"
    End If
    Do Until i = iCount
        cSql = "Select Count([RID]) as myCount from tblHOH where RID = '" & rst!RID & "'"
        Set recSet = CurrentDb.OpenRecordset(cSql): i2 = 1: recSet.MoveLast: recSet.MoveFirst: iCount2 = recSet!myCount + 1
        mSql = "Select AttempType2 as A2, AttempType3 as A3, AttempType4 as A4, Results as RT from tblHOH where tblHOH.RID = '" & rst!RID & "'"
        Set newSet = CurrentDb.OpenRecordset(mSql)
        aSql = "Select * from tblWork"
        Set wrkSet = CurrentDb.OpenRecordset(aSql)
        wrkSet.MoveLast: wrkSet.MoveFirst: Dim iWk As Integer: iWk = wrkSet.RecordCount
            Do Until i2 = iCount2
                Tr2 = Tr2 & CStr(i2)

                Debug.Print Tr2              ' = AttempType2_1 when it debug prints.

                wrkSet.Fields(Tr2F) = Tr2  

                CurrentDb.Execute iSql, dbFailOnError
            i2 = i2 + 1
    i = i + 1
End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're trying to set the value of a field based on the value in the variable "Tr2F", and I don't see anywhere you're creating that variable.

Also, Tr2F is declared as a DAO.Field. If you want to get that field from the wrkSet recordset, you'd probably do this:

EnflowAuthor Commented:
Found this stackoverflow answer while looking for solution...  Tried it but did not work... Where is my simple err.... ?
EnflowAuthor Commented:
Hi Scott,

Yes... trying to pass an existing field name in a table & recordset to a changing Field variable Tr2F that will change throughout my loop

The field name value i am trying to pass into a Field variable is in a string parameter value '-- Tr2 -- and it is equal to an existing field name in the table tblWork...  not trying to get anyting...

can i upload my Access file --- would that help
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, it would help.
EnflowAuthor Commented:
Okay... Data in tables is non revealing so no worries... Open frmRunCode click button or go to click event code... thanks... CJ
John TsioumprisSoftware & Systems EngineerCommented:
Well i downloaded the sample and i am puzzled...
1st where is the field Tr2F
2nd if there is a field Tr2F then you should write it
wrkSet.Fields("Tr2F") = "" & Tr2 & ""

Open in new window

3rd in order to manipulate a recordset you must either add a record (rst.AddNew) or edit (rst.Edit) ...and after the operation push the change (rst.Update)

Last...a bit unrelated but you have design issues....a table beyond 10-15 columns is dysfunctional....
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As I mentioned in my earlier post: There is no value set for the Tr2F variable, which is declared as a DAO.Field. Basically, this statement:

wrkSet.Fields(Tr2f) = "SomeValue"

Means "Write the value of 'SomeValue' to the Field in wrkSet named <the value of Tr2F>". If there is no value in Tr2f, the code will fail.

If you're trying to set that value to AttempType2_1, for example (which is the value contained in your Tr2 file), then:

wrkSet.Fields(Tr2) = "SomeValue"

But before we can give you any real advice, you'd have to let us know what you're trying to achieve.

Also, I agree with John - tblWork is a poorly designed table, and is more of a spreadsheet than a database table.
EnflowAuthor Commented:
Hi John

Thanks for looking at the file...

Yes... To be sure... There is no field in tblWork that is called Tr2F...  i am trying to capture/discover a tblWork field name during the loop that is part of tblWork collection and use it in a SQL update query that includes the field variable Tr2F as if it was a field the name AttempType2_1

I am trying to use the variable DAO.Field Tr2F (declared in my above Dim statements) to be a Field variable that receives A CHANGING FIELD VALUE NAME that is also included in the tblwork collection...

as in the DAO.Field variable Tr2F = AttempType2_1 (a tblWork field name) with a line of code something like this...

wrkSet.Fields(Tr2F) = "" & Tr2 & ""

again... see...

I tried your code with "Tr2f" but it does not work... same err message of Item not in Collection.

so then i can use Tr2F in my SQL update statement... since i have found that is the needed field name for part of my sql vba string syntax.

I DO NOT KNOW WHAT FIELD NAME I AM GOING TO USE UNTIL THE LOOP IS HALFWAY THRU ITS CODE... then i take that field name value that is created in code (which WILL be in tblWork collection of course) and call that Field Tf2F...

the table structure design is to client spec since it will be exported to EXCEL (once my code transposes it the data from vertical to horizontal) with the EXACT Field names as columns in the excel spreadsheet... the data will not stay in AccessDB... that db will be deleted.
EnflowAuthor Commented:
Hi Scott,

I am sorry i am just not explaining it clearly in text and I am open to paying someone on the phone to clear it up... I do not want to type myself to death and also lose time for me and you guys unnecessarily...

So i will either delete the question or pay someone on the phone...

Who is game ?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I am trying to use the variable DAO.Field Tr2F (declared in my above Dim statements) to be a Field variable that receives A CHANGING FIELD VALUE NAME that is also included in the tblwork collection...
You don't need to use a DAO Field variable for that. You can just use a String variable.

Your code is hard to follow due to the archaic inline syntax (i.e. the code segments separated by colons), but you might try this:

wrkSet.Fields("AttemptType2_" & cstr(i)) = Tr2

If you need to change the other numeric value:

wrkSet.Fields("AttemptType" & cstr(icount) & "_" & cstr(i)) = Tr2

Of course, you'd have to fix that to ensure it's going in the right places.
EnflowAuthor Commented:
Hi Scott,

thanks i will try this... but i just had another idea... Would a complex crosstab query enable me to take the vertical table tblHOH  and transpose it Horizontally by unique RID to tblWork instead of using code... some RID require 50 sets of attemps etc...

Only a thought... Will now work with your new code suggestions... thanks... CJ
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could always open up a Gigs project for this.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EnflowAuthor Commented:
Hi Scott,

Yes i tried this but here i what i am really trying.. is it not ?

wrkSet.Fields("AttemptType2_" & cstr(i)) = Tr2

is saying...

wrkSet.Fields("AttemptType2_" & cstr(i2)) = "AttempType2_1    ' (which is the value of the variable Tr2)

or more explicitly..

codeXYZ("AttempType2_1" = "AttempType2_1"

and same err as before...  but i do not see the logic in this...
EnflowAuthor Commented:
is that a LIVE project
EnflowAuthor Commented:
Hi Scott,

I am trying to alias the name of a field (AttempTypeX_Y something that i discover in loop code)  to a Field variable (Tr2F)  and then use that Tr2F in a update query in my code...

short and clear ?

Make TrF2 authority name = to AttempTypeX_Y (like AttempType2_1) and have it have the same authority as that table name in my update query.
John TsioumprisSoftware & Systems EngineerCommented:
Sorry but i am a bit lost here...
If you want to capture a field that is unknown until the code is executed then you should go like this :
For i =0 to rst.Fields.Count-1
If rst.fields(i).Name = "TheNameOf the Field you after" then
 **** Do the work you want ****
End if

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.