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.