[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2017-10-12
Medium Priority
Last Modified: 2017-10-16
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

Question by:Enflow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
LVL 85
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:


Author Comment

Found this stackoverflow answer while looking for solution...  Tried it but did not work... Where is my simple err.... ?


Author Comment

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 85
Yes, it would help.

Author Comment

Okay... Data in tables is non revealing so no worries... Open frmRunCode click button or go to click event code... thanks... CJ
LVL 18

Expert Comment

by:John Tsioumpris
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....
LVL 85
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.

Author Comment

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...   https://stackoverflow.com/questions/21885101/can-you-use-a-variable-for-the-field-name-when-using-addnew-to-a-record-set

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.

Author Comment

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 ?
LVL 85
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.

Author Comment

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
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
You could always open up a Gigs project for this.

Author Comment

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...

Author Comment

is that a LIVE project

Author Comment

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.
LVL 18

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 1000 total points
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


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Join & Write a Comment

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question