Simple question on adding message box to list all the item that is not same

[EMILYTAN via AccessMonster.com]

Hi,

I have a few questions:-

1. Why can't we directly add
if partnumber same Then
'update
else
just store the partnumber?

2. Can we change the outer loop to myrecset rather than myrecset1 and inner
loop to myrecset1 rather than myrecset?

 

[EMILYTAN via AccessMonster.com]

Hey,
I have a bright new idea, but not sure whether it will work, so need your
help ...
The new idea is the 1 written with "suggestion"

Brief idea of the suggestion :-

I am using myrecset to be outer loop and myrecset1 to be the inner loop.
Again, note that myrecset is the old job while myrecset1 is the new job...

My idea is, if both of the part number is the same do whatever update, then
just go out to the outer loop to start with the next partnumber of old job.
If both of the partnumber are different, it will store the partnumber old the
old job in a TEMP location then continue looping in the inner loop to check
any other part number. A counter should be put there (TEMP), if not same,
count 1 and at the end, if the counter equals to the number of inner loop,
then move the value of the TEMP to the STRMYRECSET where it will be displayed
in message box.
So, after finishing the inner loop, the TEMP value should be erased to start
a new cycle of outer loop.....

I am not sure it will work or not so I need your guidance....
I am sorry, I am not good at coding, that is why need your help again...

Thanks Thanks...million of thanks
Option Compare Database


Private Sub cmdInsertItem_Click()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myrecset As New ADODB.Recordset
Dim myrecset1 As New ADODB.Recordset
myrecset.ActiveConnection = cnn1
myrecset1.ActiveConnection = cnn1
Dim mySQL As String
Dim mySQL1 As String
Dim mySQL2 As String
Dim strmyrecset As String
Dim strtemp As String



'Check to make sure the Job Number fields are not blank, if so prompt for
input

If IsNull(Me.txtJobNumberNew) Then
MsgBox "The New job # field cannot be left blank, please enter the new job
number"
Me.txtJobNumberNew.SetFocus
Exit Sub
End If

If IsNull(Me.txtJobNumberOld) Then
MsgBox "The Old job # field cannot be left blank, please enter the old job
number"
Me.txtJobNumberOld.SetFocus
Exit Sub
End If

'open a recordset to hold New Job # data
mySQL1 = "SELECT WIPRawDetails.PartNumber, W_KittedQTY FROM WIPRawDetails
WHERE WIPRawDetails.JobNumber='" & Me.txtJobNumberOld & "'"

myrecset.Open mySQL1


' loop through the part numbers of the New job #

Do Until myrecset.EOF

'open a record set to get part number and kitted QTY for old Job
number
mySQL = "SELECT WIPRawDetails.PartNumber FROM WIPRawDetails WHERE
WIPRawDetails.JobNumber='" & Me.txtJobNumberNew & "'"
myrecset1.Open mySQL

'check to make sure data (part #'s) are present for the old job #
If myrecset1.BOF And myrecset1.EOF Then
MsgBox "There are no part numbers tied to this job #:" & Me.
txtJobNumberNew
myrecset1.Close
Exit Sub
Else
'loop through the part #'s of the old job #

Do Until myrecset1.EOF
'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 myrecset!PartNumber = myrecset1!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
'----Suggestion :- Go to the outer loop and move next
'----Suggestion :- Else (if not same part number) store in a
temp place eg strtemp = myrecset!PartNumber...If the
'----Suggestion :- count is equal to the number of loop then
move the part number to a new place :-
'----Suggestion :- example strmyrecset = strtemp
End If
myrecset1.MoveNext


Loop
myrecset1.Close

'---Suggestion :- clear the strtemp in order to move to the next
1

End If
myrecset.MoveNext
Loop
myrecset.Close
If Not IsNull(strmyrecset) Then
MsgBox "What Ever message here" & strmyrecset, vbInformation, "What ever"""

Else

MsgBox "Update has been completed"

End If
Set myrecset1 = Nothing
Set myrecset = Nothing



End Sub

 

[AccessVandal via AccessMonster.com]

Hi Emily,

I have a few questions:-

1. Why can't we directly add
if partnumber same Then
'update
else
just store the partnumber?

What do you mean by add directly or directly add?
Do you want to skip the “Do Loop� If that’s what you mean. The answer is Yes,
but it’s only the first record gets update. Refer to the “MoveNext†and
“MoveFirst†method of moving pointer in recordsets.
Without the Do Loop and MoveNext, the first record will only be the one doing
the matching.
However, you’ll still need to check the recordsets EOF else your code will
stop executing with an error.

2. Can we change the outer loop to myrecset rather than myrecset1 and inner
loop to myrecset1 rather than myrecset?

Yes, it depends on what records you were trying to match.

 

[AccessVandal via AccessMonster.com]

Hi Emily,

That complicates things and makes the code difficult to read.

Anyway, Have you tried running the code? It is always good to test it first.

Air code here for you to try.

If you want to use GoTo.

OutLoopRec: ‘place goto here
Do Until myrecset.EOF ‘outer loop
‘what ever……
‘……………
Do Until myrecset1.EOF ‘inner loop
'----Suggestion :- Go to the outer loop and
move next
GoTo OutLoopRec 'tell VB where to go, might go into a continuous loop


If you want to count.

Dim LngRecCount as Long

LngRecCount = myrecset.RecordsetCount ‘the total of records in this set

Use the loop the minus or plus to do count like

LngRecCount = -1 ‘to minus the total in above line
‘---------------------------------------------------------------------------

Or you can do just by counting the number of loops

Dim LngRecCount As Long

LngRecCount = LngRecCount + 1
‘use the loop to increase the counter

 

[EMILYTAN via AccessMonster.com]

Ok...gonna try on it...thanks for helping

Hi Emily,

That complicates things and makes the code difficult to read.

Anyway, Have you tried running the code? It is always good to test it first.

Air code here for you to try.

If you want to use GoTo.

OutLoopRec: ‘place goto here
Do Until myrecset.EOF ‘outer loop
‘what ever……
‘……………
Do Until myrecset1.EOF ‘inner loop
'----Suggestion :- Go to the outer loop and
move next
GoTo OutLoopRec 'tell VB where to go, might go into a continuous loop

If you want to count.

Dim LngRecCount as Long

LngRecCount = myrecset.RecordsetCount ‘the total of records in this set

Use the loop the minus or plus to do count like

LngRecCount = -1 ‘to minus the total in above line
‘---------------------------------------------------------------------------

Or you can do just by counting the number of loops

Dim LngRecCount As Long

LngRecCount = LngRecCount + 1
‘use the loop to increase the counter

EMILYTAN wrote:
Hey,

[quoted text clipped - 22 lines]

Thanks Thanks...million of thanks