Enflow
asked on
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
************************** ********** ********** ********** ********** ********** *
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
ASKER
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
https://stackoverflow.com/questions/21885101/can-you-use-a-variable-for-the-field-name-when-using-addnew-to-a-record-set
ASKER
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
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
Yes, it would help.
ASKER
Okay... Data in tables is non revealing so no worries... Open frmRunCode click button or go to click event code... thanks... CJ
HowToPassFieldName.accdb
HowToPassFieldName.accdb
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
Last...a bit unrelated but you have design issues....a table beyond 10-15 columns is dysfunctional....
1st where is the field Tr2F
2nd if there is a field Tr2F then you should write it
wrkSet.Fields("Tr2F") = "" & Tr2 & ""
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....
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.
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.
ASKER
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.
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.
ASKER
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 ?
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 ?
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("AttemptType
If you need to change the other numeric value:
wrkSet.Fields("AttemptType
Of course, you'd have to fix that to ensure it's going in the right places.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Scott,
Yes i tried this but here i what i am really trying.. is it not ?
wrkSet.Fields("AttemptType 2_" & cstr(i)) = Tr2
is saying...
wrkSet.Fields("AttemptType 2_" & 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...
Yes i tried this but here i what i am really trying.. is it not ?
wrkSet.Fields("AttemptType
is saying...
wrkSet.Fields("AttemptType
or more explicitly..
codeXYZ("AttempType2_1" = "AttempType2_1"
and same err as before... but i do not see the logic in this...
ASKER
is that a LIVE project
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)