Looping Through Recordsets and Tabledef

[Knicks]

I have a table that has records with field names as the data that then needs to be matched up with Yes/No fields that uses the same name. So I was planning on looping through the recordset and looping through the tabledef. So in situation below I would loop through the records and where Race_Hold.value = tabledef.field.name set to TRUE/YES. The code I have loops through everything but is not setting the YES/NO field, no errors just isn't doing anything. The debugging shows the RS!fieldname it seems correctly, I don't need brackets as the field names don't have spaces.

Any suggestions would be appreciated (Ms Access 2010)
Thanx


Example: Here we would the looping to update cln_race_white = TRue, cln_race_pacific = true

Client = 100
Race_Hold = cln_race_white
raceAdd_hold = cln_race_pacific

cln_race_black = false
cln_race_white = false
cln_race_pacific = false

Dim db As dao.Database
Dim rs As dao.Recordset

Dim strTemp As String
Dim strTemp2 As String
Dim strTemp3 As String
Dim STRsql As String
Dim strTempALL As String
Dim hyp As Hyperlink
Dim QueryName As String
Dim tdf As dao.TableDef
Dim fld As dao.Field

'Clear Temp Clients
DoCmd.OpenQuery ("qryTemp_exp_Client_Delete")
'Prime Temp Clients
DoCmd.OpenQuery ("qryTEMP_exp_client_append")



Set db = CurrentDb
Set rs = db.OpenRecordset("Temp_exp_client")
Set tdf = db.TableDefs("temp_exp_client")



'Loop through Temp Clients to update races
Do Until rs.EOF

For Each fld In tdf.Fields
strTemp = fld.Name
strTemp2 = "RS!" & strTemp
Debug.Print strTemp2

If rs![race_hold] = strTemp Then
Debug.Print "RH = " & rs![race_hold]
Debug.Print "fldName = " & strTemp
rs.Edit
strTemp2 = YES
'fld.Value = True
rs.Update
Else
End If

If rs![raceAdd_hold] = strTemp Then
rs.Edit
strTemp2 = YES
'fld.Value = True
rs.Update
Else
End If


Next fld








rs.MoveNext
Loop


rs.Close









DoCmd.SetWarnings True

 

[Andrzejek]

Did you try to step thru your code?

It is hard to follow your logic here since you do not use TGML tags to show code :-(
But just to look at his little piece of code:

...
Dim strTemp2 As String
...
strTemp2 = "RS!" & strTemp
 Debug.Print strTemp2
... [green]So far so good...[/green][red]
strTemp2 = YES[/red]

The last line of your code should give you an error: Variable not defined.
Yes/No, True/False values in your Access table are in reality -1 and 0 values (I think...)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Knicks]

I tried something like

dim StrTemp3 = dao.field

and then in the code
rs.edit
strTemp3 = strTemp2
strTemp3 = -1
rs.update

Now that produces an ERROR, object block variable not set. So the question then becomes how does one go about using a variable that equates to a field name to change its value?

How do we turn strTemp2 into
RS![cln_race_white]
and then
RS![cln_race_white] = -1

 

[Knicks]

Got it to work.

Instead of trying to attach the complete table and field identifier in a variable, use the .dot operator and plug the string variable in the parenthesis

rs.Fields(strTemp) = -1