OK I feel like I am going nuts here... I have a set of Queries... basically three selects and three crosstabs which pivot on each of the selects...
Then I have another query which pulls these three sets of data together, lets just call it qryX.
When I run: "Select * from qryX;" I get some values...
So, what I want to do at this point is loop through qryX and place each value from qryX into tblY. (I know I could use a delete and append query and run a docmd... but I am trying to figure out why the route I chose didn't work... seems like it should!)
So... it seems straight forward enough.
I have simplied my code and added rsQ and rsY to denote query and table. Understand that the code runs fine and appends values to an empty table, they just happen not to be the ones which the query is producing... outside of the code:
Ok so first I load values into the first field in the table... then I basically load everything from the query into the table.
But when I do this... the values that I see when I run the query don't get. I have tried compact and repair, I have tried moving the objects to a new file and running it... I even rewrote the queries in another file and tried to run it and it still came up with the exact same issue.
In the watch window I can see the value of: [rsQ.Fields(i - 1)] as 1883 at the same time I can run the query ("SELECT * FROM qryX;") and it shows me 1845!!!! Is there something about using Crosstab Queries with recordsets, that I don't know about... or am I simply finally losing it?
Then I have another query which pulls these three sets of data together, lets just call it qryX.
When I run: "Select * from qryX;" I get some values...
So, what I want to do at this point is loop through qryX and place each value from qryX into tblY. (I know I could use a delete and append query and run a docmd... but I am trying to figure out why the route I chose didn't work... seems like it should!)
So... it seems straight forward enough.
I have simplied my code and added rsQ and rsY to denote query and table. Understand that the code runs fine and appends values to an empty table, they just happen not to be the ones which the query is producing... outside of the code:
Code:
strSQL = "SELECT * FROM qryX;"
rsQ.Open (strSQL)
rsQ.MoveFirst
'------------------------------------------------
' Open table for appending clone Agreement
'------------------------------------------------
strSQL = "SELECT * FROM tblY;"
rsY.Open (strSQL)
Do While Not (rs.EOF Or rs.BOF)
rsY.AddNew
Select Case rs.Fields(2)
Case "S"
rsY.Fields(0) = 2
Case "P"
rsY.Fields(0) = 1
Case "N"
rsY.Fields(0) = 5
End Select
'------------------------------------------------
' There are 21 fields in table Y
' (i.e. 0 to 20).
' Append new record fields 1 - 20
'------------------------------------------------
For i = 1 To 20
rsY.Fields(i) = rsQ.Fields(i - 1)
Next i
rsQ.MoveNext
Loop
But when I do this... the values that I see when I run the query don't get. I have tried compact and repair, I have tried moving the objects to a new file and running it... I even rewrote the queries in another file and tried to run it and it still came up with the exact same issue.
In the watch window I can see the value of: [rsQ.Fields(i - 1)] as 1883 at the same time I can run the query ("SELECT * FROM qryX;") and it shows me 1845!!!! Is there something about using Crosstab Queries with recordsets, that I don't know about... or am I simply finally losing it?