• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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
    Else
        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
            recSet.MoveNext
            Loop
    i = i + 1
    rst.MoveNext
    Loop
End Sub

Open in new window

0
Enflow
Asked:
Enflow
  • 9
  • 5
  • 2
2 Solutions
 
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:

wrkSet.Fields(Tr2f.Name)
0
 
EnflowAuthor Commented:
Found this stackoverflow answer while looking for solution...  Tried it but did not work... Where is my simple err.... ?


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

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, it would help.
0
 
EnflowAuthor Commented:
Okay... Data in tables is non revealing so no worries... Open frmRunCode click button or go to click event code... thanks... CJ
HowToPassFieldName.accdb
0
 
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....
0
 
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.
0
 
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...   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.
0
 
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 ?
0
 
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.
0
 
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could always open up a Gigs project for this.
0
 
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...
0
 
EnflowAuthor Commented:
is that a LIVE project
0
 
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.
0
 
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
Next

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now