Hi Emily,
Have you tested the code by inserting breakpoints? You need to debug the
codes to see that it works for you. Please new comments in the codes
carefully and compare with mine from the last post.
‘--------------------------------this is what you posted----------------------
-------------------
Well, this is what I have done...
Do Until myrecset1.EOF
'open a record set to get part number and kitted QTY for old Job
number
mySQL1 = "SELECT WIPRawDetails.PartNumber, W_KittedQTY FROM
WIPRawDetails WHERE WIPRawDetails.JobNumber='" & Me.txtJobNumberOld & "'"
myrecset.Open mySQL1 ‘I presume this was working
’---------------------------this part of the code may not work----------------
-----------
'check to make sure data (part #'s) are present for the old job #
’ If myrecset.BOF And myrecset.EOF Then
’ MsgBox "There are no part numbers tied to this old job #:" & Me.
’txtJobNumberOld
’ 'strmyrecset = strmyrecset & "," & myrecset!PartNumber
’ myrecset.Close
’ Exit Sub
‘I have commented it out
‘----------------it will very like go to Else or may not----------------------
--------------
‘when recordset “myrecset†is open and if there are records,
‘BOF is “True†and EOF is “Falseâ€
‘if there are no records, you need to change it to………
‘â€If myrecset.BOF = False And myrecset.EOF = True Thenâ€
‘use don’t need to use BOF, just use EOF.
‘if you wish to check “is there any records?†and exit, just use..
If myrecset.EOF = True Then
MsgBox "There are no part numbers tied to this old job #:" & Me.
txtJobNumberOld
‘you need to close your recordset here? What about “myrecset1�
‘is it use somewhere?
myrecset.Close
Exit Sub
End If
‘This code above here is not for checking matching records, it’s your query
“mySQL1â€
’-----------------------------------------------------------------------------
-----------------------
‘--------------commented out-----------------------------
‘ Else
‘----------------------------------------------------------------
'loop through the part #'s of the old job #
Do Until myrecset.EOF
’----part of the code is missing here-----------------------------------------
-
‘this part of the code is to recycle one of the recordset to match the
‘the “Do Until myrecset.EOF is “Trueâ€. Because it is still “Falseâ€, you
‘need to recycle the recordset “myrecset1†to match “myrecset†until
‘â€myrecset.EOF = True†where it will exit the Loop. (use this only if the
table
‘sorting order is not set correctly.)
If myrecset1.EOF = True And myrecset.EOF = False Then
myrecset1.MoveFirst
End If
‘I have edited it to recordset “myrecset1†as you want cycle back to first
record
‘As for recordset “myrecsetâ€, refer to the “Do Until“ “Else†part
‘
‘----this code is important! If your tables are not in proper sorting order---
-
‘like I said, if the recordsets are reversed, change them
‘if “myrecset1†is the one you wish to recycle to the top the record.
‘You do not need to recycle the recordset in the “Do Untilâ€
‘if it is recordset “myrecset†change it accordingly.
‘Please refer to the previous post comments
‘-----------------------------------------------------------------------------
-----------
'if part numbers of the two jobs are the same, perform the
’update, if not move on to next part number in old job
If myrecset1!PartNumber = myrecset!PartNumber Then
mySQL2 = "Update WIPRawDetails set W_KittedQty =" & myrecset!
W_KittedQty
mySQL2 = mySQL2 & " Where jobnumber= '" & Me.txtJobNumberNew
& "' AND partnumber ='" & myrecset1!PartNumber & "'"
CurrentDb.Execute mySQL2, dbFailOnError
’----It appears that you don’t understand this part-----------
‘If both recordsets “myrecset†and “myrecset1†matches, move both
‘record pointer to next record.
myrecset1.MoveNext
myrecset.MoveNext
Else
‘-----------------------------------------------------------------------------
-------
’if recordset “myrecset†does not match “myrecset1â€, move next
‘record for “myrecset1†only but not “myrecsetâ€. Why don’t move
‘â€myrecset1â€? you might ask. Refer to “Do Until†above.
‘Here lies one problem, what if “myrecset†doe not match and
‘will loop continuously?
‘This where you must make sure that “myrecset†and “myrecset1â€
‘records exist in both queries. Only one recordset, in this case
‘recordser “myrecset1†has one or more unmatch record. Only
‘this recordset “myrecset1†must be the one you want to
‘recycle to MoveFirst to the top of the record. Recordset
‘â€myrecset†will end the loop once the .EOF is True.
‘So the chances of a continuous looping is prevented.
‘
‘if you MoveNext “myrecsetâ€, this unmatch record will not be
‘updated. Unless that’s what you intend to do if both recordsets
‘have unmatching records, but this will not work well.
myrecset1.MoveNext
strmyrecset = strmyrecset & "," & myrecset1!PartNumber
End If
’-----------------------------------------------------------------------------
------
‘---The Loop should be here-------------------
Loop ‘Loop until myrecset.EOF = True
‘------------------------------------------------------
‘----Display message box here---------------------------------
‘show message box if there are unmatching records
If Not IsNull(strmyrecset) & “ = “ Then ‘not null or is blank
MsgBox "What Ever message here " & strmyrecset1, vbInformation, "What ever
Title"
Else
MsgBox "Update has been completed"
’-------------------------------------------------------------------------
’this lines of code should be just after the “Do Until†line
‘please read last post
’ If myrecset1.EOF = True Then
’ myrecset.MoveFirst
’ End If
‘please refer to top at “Do Untilâ€
‘-------------------------------------------------------------------------
‘----you sure the loop is here?---------------------
‘Loop
’-----------------------------------------------------------------------------
-
‘-----------------------------------------------------------------------------
-
’Do not to close the recordset here, do it after the last End If
‘ myrecset.Close
End If
‘-----------------------------------------------------------------------------
---
‘----this where the Message Box code ends-----------------------
‘------------I have comment this out-------------------
’ myrecset1.MoveNext
’ Loop
’--------------------------------------------------
‘-------Close Recordset--------------------
myrecset.Close
myrecset1.Close
Set myrecset1 = Nothing
Set myrecset = Nothing
‘-----------------------------------------------
‘----comment out--------
’End If
’End If
’-----------------------------
Well, I understand what is meant by .EOF and .BOF...
I know it is kind off troublesome to get your help to go through this code,
but I really hope to learn something from mistakes...Thanks
'-------------------end--------------------------------------
Hope it will get you moving.
