Recordset Data doesn't match SQL

gblack

Registered User.
Local time
Today, 22:59
Joined
Sep 18, 2002
Messages
632
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:

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
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?
 
records are not stored nor retrieved in any specific order unless you tell your query using an ORDER BY clause. Try adding a sort by one of the columns and then see if things line up as you expect.
 
Hmm... I don't think this has to do with linning up... I go line by line. The value which equates to 1845 in the query is the one that equates to 1883 in the code... some of the other values change as well and some remain the same...

But because the crosstab query rolls everything up this is a relatively small set of records... I can see all the records from both perspectives, thusly this doesn't appear to be an issue of things not linning up right...

I am not really concerned with where they are loaded in the table... later on I use order ID numbers to line things up when I query that table.
 
hmmm, I guess I don't understand what you're trying to accomplish then. Sorry :(
 
Yeah, It's kind of a complex concept to write down. I probably didn't explain myself the way i needed to.

Basically when I use the code above to update a table from a query... what should happen is the code loops through each record, field by field and places that value into a table.

The issue with the code is that it was placing a value into my table... but the value was entirely different that the value from the query it was supposed to be pulling from.

In any event I just used an append query and obtained the results I wanted... however I am still baffled as to why the recordset wasn't directly pulling the values from the query I was setting it to!!! I'd post the file for people to look at, but the info isn't something I can share, Mores the pity...

The append query works... but for some reason the recordset didn't.

I think it may have been holding on to a previous value from the query prior to me changing the query structure or something... but I figured repair and compact or rewriting the query in another file would have done the trick... but no.

So... like SOOOO many other things that come up when I code... I found a workaround... It chips away at my very soul, everytime I can't do something the way I set out to...! Ugh!

Usually Bob has all the answers! Where are you Bob? :D
 
In your post #1 you have rs, rsQ and rsY, what is rs?
When you post code, why not post all of the code?

I see rsY.addnew, but no rsY.update (commit the update).
 
Eh rs was supposed to be rsQ... I was trying to make the code relate to my post, so I went in and changed the part that was mesisng up, in my debugging process, to match the example I was talking about... I don't show all the code because it's not relevant and some of the names I actually had might have been confusing...

Like I said before, I'd post the entire accde file, but then they'd cuff me & lock me away...

I suppose I should have posted all the code... but it's long and involved and it was only partially finished... I was running into this issue as I was trying to do a bunch of stuff and didn't want to have the rest of the code be evaluated as part of the issue... when Iknew it wasn't... I know how critical people are when it comes to code and I wanted to save myself the headache of folks say Oh you misspelled your variable name below... yada yada yada...

Anyway...

The most simple way I can put this is: when I open a recordset, based on a SQL string, the values that show up in that recordset do not match the values that show up... if I run that same SQL string from the query SQL prompt

*And I was wondering what could be making this happen.*

At this point it seems clear, from the lack of responses and the fact it's so tough to explain, that I am the only one in the world that has run into this issue...

And... as no one seems to know what the issue is... or what it could be... I guess I am not gonna find out... it certainly wouldn't be the first time that I didn't know why Access was doing the funky chicken...

Thanks for trying though,
Gary

PS. In retrosepct, I wonder if I would have done any better if I had not posted any of my code and simple said:

When I open a recordset, based on a SQL string, the values that show up in that recordset do not match the values that show up... if I run that same SQL string from the query SQL prompt... has this ever happened to anyone else?
 
Provide working code and corresponding data in a db, sufficient to replicate the problem. If not your original data then make some up.

The answer to your question may have been "yes, I've seen someone mess up a db" or, perhaps not. Both question and answer are irrelevant because they do not solve the problem. The problem is not solvable by others, unless there is enough info to debug it. And there isn't in your posts.

A workaroud can mean that you made a mistake in the original, and failed to reimplement the mistake in your workaround. Since that works, consider moving on and forgetting the failing code.
 
The code was there as an example of what I was trying to do... it wasn't there for you to recreate the issue.

How could you...really? you don't have the query that I was running nor the data behind it...

Many times I have submitted posts to this forum without any code at all and have had the problem solved by people who understood the issue and who could tell me why I was running into it... or at least take an educated guess.

At this point, it is clear that the fact i posted code at all just clouded the issue... I think the problem is clear for my post script of my last post...

That said, if you have any ideas of what I can check... I would appreciate the help...

Thanks,
G
 
Not all problems are solvable by remote vision or guesswork. Striking gold before, without showing code, does not mean that that works in all cases.

And yes, posting partial code, with errors in it, does definitely cloud the issue.

Just because you have data /field names you do not wish to show, does not prevent you from creating an equivalent example, with bogus names/data, to replicate the problem. Sometimes the process of constructing such an example is by itself enough to spot the error/misconception.
 
So, what I want to do at this point is loop through qryX and place each value from qryX into tblY.
Why are you trying to save derived values? This is bad practice.

Remove the error handling in your code and you will see if (and or when) it errors.

Also, not all crosstab query fields are visible when opening the recordset especially if the crosstab headings are not fixed.

Plus field(0) of rsX may not be the same data type as field(0) of rsY.
 
Why are you trying to save derived values? This is bad practice.

Because I am.

Remove the error handling in your code and you will see if (and or when) it errors.

This is a good suggestion, but there is no error, per se... the value simply isn't coming up the same on both sides... no error is thrown.

Also, not all crosstab query fields are visible when opening the recordset especially if the crosstab headings are not fixed.

Plus field(0) of rsX may not be the same data type as field(0) of rsY.

Hmm... well... I checked the loops... because the query is a cross tab it has a limited amount of records (around 68), so it is easy for me to put a break in and get to the actual values as they run off. The data types are fine.

I use an order_ID field and another ID field to find the value as it runs through the code. Plus the largest value for the dataset is in the 1800's (and everything else is much smaller), so I can easily correlate said high value to the query and the recordset. Plus all the values line up... so it's not an issue of the columns not linning up or the data types being different. I am running the EXACT same query line...

I am just wondering... only because I changed the query, after I wrote the code, if there could be some residual sticking point and the recordset isn't actually using the changed strSQL, but is somehow still trying to run the old strSQL? Is that even possible?

Hmm... maybe I will recreate the issue and post it all... it really is odd. but that's a lot of work... not sure if I have the time, especially since I got it all done by using an append query and skipping the recordset all together... at least for that one issue.
 
OK I decided to go ahead and revamp all the data and objects to show you guys the issue I had with this data.

When you run the code in the module named: Format_Spend_Charts it fills a table named "SAND_CHARTS_FINAL"

You will see that the query which is the basis for this (i.e., "00475_Toys_By_OA") shows different values than what gets filled in the table...

Most notably the Legos value in the 00475_Toys_By_OA query shows: 1842
Whereas the Legos value in the SAND_CHARTS_FINAL table is showing: 1880

My question is why, since all my code does is copy from the query to the table???

All the data and queries are there with the code and it all works... so have at.
 

Attachments

Users who are viewing this thread

Back
Top Bottom