rs.movenext is not refreshing all the fields identified in the query.

[SilverDevil72]

Problem statement: Have 4 fields prior to the From clause. rs.movenext is only populating 2 of the 4. Everything works well except for this movenext problem. Very perplexed. Project is stuck.

PLEASE HELP!!

This question has 4 sections, Query, Code, Table data, Results of the movenext.
Query:
QRS = "SELECT ProdFile.[Abstract Bk # & Pg] as PBkPg, BOBTest.[Abstract Bk # & Pg] as BBkPg, ProdFile.[Volume & page #] as PVolPg, BOBTest.[Volume & page #] as BVolPg FROM ProdFile, BOBTest WHERE (((ProdFile.[Abstract Bk # & Pg]) = BOBTest.[Abstract Bk # & Pg]) And ((ProdFile.[Volume & page #]) = BOBTest.[Volume & page #]));"


Set RS = db.OpenRecordset(QRS, dbOpenDynaset, dbPessimistic)

UpdRS = "UPDATE ProdFile, BOBTest SET BOBTest.[Volume & page #] = BUpdVolPg, WHERE (((ProdFile.[Abstract Bk # & Pg])=BOBTest.[Abstract Bk # & Pg]) And ((ProdFile.[Volume & page #])=BOBTest.[Volume & page #]));"

[code]


Debug.Print "Number of records= "; numReturned
If numReturned > 0 Then
Do While Not RS.EOF
' Assuming the prod bk&pg match Bobfile bk&pg AND prod twp matchs Bob file TWp.
Debug.Print "Prod File.[Abstract Bk # & Pg] = "; RS!PBkPg
Debug.Print "BOBTest.[Abstract Bk # & Pg] = "; RS!BBkPg
Debug.Print "Prod File.[Volume & page #] = "; RS!PVolPg
Debug.Print "Bob File.[Volume & page #] = "; RS!BVolPg
Debug.Print " "
If RS!PVolPg = PrevVolPg Then
If RS!PBkPg = PrevBkPg Then
Processing....
Else
Cnt = 1
PrevBkPg = RS!PBkPg
BUpdVolPg = ""
End If
Else
PrevVolPg = RS!PVolPg
End If


If Len(BUpdVolPg) > 1 Then
'RS.Edit
Debug.Print " Prod.file Vol&pg = "; RS!PVolPg & " " & "Bob New value= "; BUpdVolPg
Debug.Print

'RS.Execute UpdRS
Debug.Print " Move Next "
RS.MoveNext ' Read next record
Else
End If
Loop
Else
Response = MsgBox("query returned zero records", vbOKOnly)


End If
RS.Close

Exit Sub
/Code

Table data:

P.. is ProdFile, B.. Bobtest

Can see first 2 records end in "014" second two "015". Test results below clearly show the Move next refreshed the last two columns but not the first two. ??

Please help!!

PBkPg	BBkPg	PVolPg	BVolPg
0515 51507 014	0515 51507 014	101 p423	101 p423
0515 51507 014	0515 51507 014	150 p25	150 p25
0515 51507 015	0515 51507 015	194 p598	194 p598
0515 51507 015	0515 51507 015	314 p241	314 p241
0515 51508 019	0515 51508 019	520 p277	520 p277

Testing:
Number of records= 20

Prod File.[Abstract Bk # & Pg] = 0515 51507 014
BOBTest.[Abstract Bk # & Pg] = 0515 51507 014
Prod File.[Volume & page #] = 101 p423
Bob File.[Volume & page #] = 101 p423

Prod File.[Abstract Bk # & Pg] = 0515 51507 014
BOBTest.[Abstract Bk # & Pg] = 0515 51507 014
Prod File.[Volume & page #] = 101 p423
Bob File.[Volume & page #] = 101 p423

Prod.file Vol&pg = 101 p423 Bob New value= 101 p423.


Move Next
Prod File.[Abstract Bk # & Pg] = 0515 51507 014 Did NOT refresh with new record.
BOBTest.[Abstract Bk # & Pg] = 0515 51507 014 Did NOT refresh with new record.
Prod File.[Volume & page #] = 150 p25 DID refresh with new record
Bob File.[Volume & page #] = 150 p25 DID refresh with new record.

[Welshgasman]

Not even going to try and read that unless within code tags.
If you are typing them manually you missed the enclosing brackets [ and ]

Probably best to upload the DB with enough to see the issue and instructions on how to recreate the issue.

[Micron]

Not even going to try and read that unless within code tags

Ditto. As a cranky old f@rt I've pretty much taken to skipping over such posts. There ought to be a sticky thread on how to post, but I fear it would be something seldom looked at.

Anyway FWIW, the basic principle with recordsets is, you load one. Then you can retrieve field values and/or edit them. If you expect to see the changes, you will have to first commit them (rs.Update - assuming you already put it in Edit mode), then reload the rs - or look at the table that you've just edited. However, you do not update an rs by navigating through it (movenext or whatever).

[orange]

As others have said --suggest you attach a copy of your database and describe exactly what you want to happen.
We don't know you nor your environment, so what is straightforward to you is not as clear to readers.
A little background of the application would help put your post into context.

[June7]

Click # button on post editor toolbar to generate proper CODE tags. Paste code between.

UPDATE action with variable embedded between quotes won't work. But then you comment out the UPDATE action as well as RS.Edit so what exactly are you trying to accomplish with this code?

[moke123]

Here's a related thread but it doesn't help much.

https://www.accessforums.net/showthread.php?t=89649

[SilverDevil72]

Thanks to all!! I was able to take care of the issue. I created a new application copied in most of the code and the problem disappeared. I don't actually know what this issue but i got past it. Thanks to all!!