do while?

C

Chris

I'm having an issue with my do while. I'm a bit of a novice so any detail
explanation or help would be greatly aprreciated.

I have created the recordset that I need and the data is exactly what I'm
looking for. I have ran the code and my loop statement works fine and
inserts the records from my recordset. However, I would like to add
condtions to my loop statement

1) I would like to create a varible that adds order quantities (sdsoqs)
until those quanties match or exceed our return quantities (rdtrqt) for a
particular item. Once this condtion has been met I would like to drop down
to the next item in my recordset.

So if we have a return of an item 103-303 with 100 pieces being returned and
there are 4 past orders for that customer, item combo each with a quanity of
25, I would like all four records inserted in my temp table because those
quatities add up to the 100 pieces being returned. Then I would like my sum
varible to be zeroed out and drop down to the next item in the recordset.
The next item in this example would be 105-505 with 50 pieces being returned
and there is one past order with an order quantity of 50, so I would only
insert one record for that item into my temp table because that would be the
sum of the quantities being returned. At this point I would zero out my
variable and drop down to my next item and so on.

2) I can't figure out how to exit the loop after my quantities condtion has
been met and drop down to the next item in my recordset.


Below is my code. Like I said, I get the desired data but I'm struggling
with where to insert my quantity counting varible and how to jump down to
the next item in the recordset after my quantity condtion has been met.
Thanks in advance for your help.


Private Sub cmdEnter_Click()

On Error GoTo Err_cmdEnter_Click

Dim sSQL As String
Dim QtySum As Long
Dim rsRMACheck As DAO.Recordset
Dim rsRMAOrder As DAO.Recordset
Dim DB As Database
Set DB = CurrentDb


QtySum = 0


If IsNull(Me.txt_RMANum) Then
MsgBox ("You must enter a valid RMA Number."), vbCritical, "Legend Valve
Error Log"
Me.txt_RMANum.SetFocus
Exit Sub
Else
End If



If Not IsNull(Me.txt_RMANum) Then

sSQL = " SELECT proddta_F40051.RDRORN"
sSQL = sSQL & " FROM proddta_F40051"
sSQL = sSQL & " WHERE (((proddta_F40051.RDRORN)= '" & Me.txt_RMANum &
"'))"


Set rsRMACheck = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

If rsRMACheck.EOF Then
MsgBox "This is not a valid RMA Number. Please try again",
vbCritical, "Legend Valve Error Log"
Me.txt_RMANum = Null
Me.txt_RMANum.SetFocus
rsRMACheck.Close

Exit Sub
Else

rsRMACheck.Close


sSQL = " SELECT
proddta_F40051.RDRORN,proddta_F40051.RDTRQT,proddta_F4211.SDDOCO,
proddta_F4211.SDDCTO,
proddta_F4211.SDLNID,proddta_F4211.SDAN8,proddta_F4211.SDITM,proddta_F4211.SDLITM,proddta_F4211.SDDSC1,proddta_F4211.SDSOQS,proddta_F4211.SDUPRC,proddta_F4211.SDIVD"
sSQL = sSQL & " FROM (proddta_F40051 INNER JOIN proddta_F4211 ON
proddta_F40051.RDLITM = proddta_F4211.SDLITM AND proddta_F40051.RDITM =
Proddta_F4211.SDITM AND proddta_F40051.RDAN8 = proddta_F4211.SDAN8) "
sSQL = sSQL & " WHERE (((proddta_F40051.RDRORN)) = '" & Me.txt_RMANum &
"') "
sSQL = sSQL & " AND proddta_F4211.SDDCTO = '" & "SO" & "'"
sSQL = sSQL & " AND proddta_F4211.SDIVD < [RDURDT]"
sSQL = sSQL & " AND proddta_F4211.SDLNTY = '" & "S" & "'"
sSQL = sSQL & " AND proddta_F4211.SDNXTR = '" & "999" & "'"
sSQL = sSQL & " AND proddta_F4211.SDLTTR = '" & "620" & "'"
sSQL = sSQL & " ORDER BY proddta_F4211.SDLITM DESC, proddta_F4211.SDIVD
DESC"

Set rsRMAOrder = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)



Do While rsRMAOrder.EOF = False

QtySum = QtySum + [rsRMAOrder]![sdsoqs]

Do Until QtySum >= [rsRMAOrder]![rdtrqt]


sSQL = "INSERT INTO tblRMA_Prior_Orders (
[RMA_Num],[Order_Num],[Cust_Num],[Shipped_Date],[Shipped_Qty],[Unit_Price],[Lng_Item])
VALUES("
sSQL = sSQL & " '" & [rsRMAOrder]![RDRORN] & "', " &
[rsRMAOrder]![SDDOCO] & ", " & [rsRMAOrder]![SDAN8] & ", " &
[rsRMAOrder]![SDIVD] & "," & [rsRMAOrder]![sdsoqs] & ", " &
[rsRMAOrder]![SDUPRC] & ",'" & [rsRMAOrder]![SDLITM] & "' )"
CurrentDb.Execute (sSQL)


Loop

rsRMAOrder.MoveNext


Loop
QtySum = 0





End If
End If



Exit_Err_cmdEnter_Click:
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_Err_cmdEnter_Click

End Sub
 
G

Graham Mandeno

Hi Chris

At the moment it looks like your inner loop will continue indefinitely,
because its termination depends on the value of QtySum, and that value is
not changing inside the loop.

Currently you have (simplified):

Do Until rs.EOF

QtySum = QtySum + [sdsoqs]

* Do Until QtySum >= [rdtrqt]
* ' insert new record
* Loop

rs.MoveNext

Loop

# QtySum = 0

Notice that the inner loop (marked *) has no code which affects the value of
QtySum, so if that value is less than [rdtrqt] to start with then that
condition will never change.

Also, your statement resetting QtySum to 0 (marked #) occurs outside the
outer loop, so it will not be reset for each new record.

What you should have is this:

Do Until rs.EOF

QtySum = 0

Do Until QtySum >= [rdtrqt]
' insert new record
QtySum = QtySum + [sdsoqs]
Loop

rs.MoveNext

Loop

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Chris said:
I'm having an issue with my do while. I'm a bit of a novice so any detail
explanation or help would be greatly aprreciated.

I have created the recordset that I need and the data is exactly what I'm
looking for. I have ran the code and my loop statement works fine and
inserts the records from my recordset. However, I would like to add
condtions to my loop statement

1) I would like to create a varible that adds order quantities (sdsoqs)
until those quanties match or exceed our return quantities (rdtrqt) for a
particular item. Once this condtion has been met I would like to drop down
to the next item in my recordset.

So if we have a return of an item 103-303 with 100 pieces being returned
and there are 4 past orders for that customer, item combo each with a
quanity of 25, I would like all four records inserted in my temp table
because those quatities add up to the 100 pieces being returned. Then I
would like my sum varible to be zeroed out and drop down to the next item
in the recordset. The next item in this example would be 105-505 with 50
pieces being returned and there is one past order with an order quantity
of 50, so I would only insert one record for that item into my temp table
because that would be the sum of the quantities being returned. At this
point I would zero out my variable and drop down to my next item and so
on.

2) I can't figure out how to exit the loop after my quantities condtion
has been met and drop down to the next item in my recordset.


Below is my code. Like I said, I get the desired data but I'm struggling
with where to insert my quantity counting varible and how to jump down to
the next item in the recordset after my quantity condtion has been met.
Thanks in advance for your help.


Private Sub cmdEnter_Click()

On Error GoTo Err_cmdEnter_Click

Dim sSQL As String
Dim QtySum As Long
Dim rsRMACheck As DAO.Recordset
Dim rsRMAOrder As DAO.Recordset
Dim DB As Database
Set DB = CurrentDb


QtySum = 0


If IsNull(Me.txt_RMANum) Then
MsgBox ("You must enter a valid RMA Number."), vbCritical, "Legend
Valve Error Log"
Me.txt_RMANum.SetFocus
Exit Sub
Else
End If



If Not IsNull(Me.txt_RMANum) Then

sSQL = " SELECT proddta_F40051.RDRORN"
sSQL = sSQL & " FROM proddta_F40051"
sSQL = sSQL & " WHERE (((proddta_F40051.RDRORN)= '" & Me.txt_RMANum &
"'))"


Set rsRMACheck = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)

If rsRMACheck.EOF Then
MsgBox "This is not a valid RMA Number. Please try again",
vbCritical, "Legend Valve Error Log"
Me.txt_RMANum = Null
Me.txt_RMANum.SetFocus
rsRMACheck.Close

Exit Sub
Else

rsRMACheck.Close


sSQL = " SELECT
proddta_F40051.RDRORN,proddta_F40051.RDTRQT,proddta_F4211.SDDOCO,
proddta_F4211.SDDCTO,
proddta_F4211.SDLNID,proddta_F4211.SDAN8,proddta_F4211.SDITM,proddta_F4211.SDLITM,proddta_F4211.SDDSC1,proddta_F4211.SDSOQS,proddta_F4211.SDUPRC,proddta_F4211.SDIVD"
sSQL = sSQL & " FROM (proddta_F40051 INNER JOIN proddta_F4211 ON
proddta_F40051.RDLITM = proddta_F4211.SDLITM AND proddta_F40051.RDITM =
Proddta_F4211.SDITM AND proddta_F40051.RDAN8 = proddta_F4211.SDAN8) "
sSQL = sSQL & " WHERE (((proddta_F40051.RDRORN)) = '" & Me.txt_RMANum &
"') "
sSQL = sSQL & " AND proddta_F4211.SDDCTO = '" & "SO" & "'"
sSQL = sSQL & " AND proddta_F4211.SDIVD < [RDURDT]"
sSQL = sSQL & " AND proddta_F4211.SDLNTY = '" & "S" & "'"
sSQL = sSQL & " AND proddta_F4211.SDNXTR = '" & "999" & "'"
sSQL = sSQL & " AND proddta_F4211.SDLTTR = '" & "620" & "'"
sSQL = sSQL & " ORDER BY proddta_F4211.SDLITM DESC, proddta_F4211.SDIVD
DESC"

Set rsRMAOrder = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)



Do While rsRMAOrder.EOF = False

QtySum = QtySum + [rsRMAOrder]![sdsoqs]

Do Until QtySum >= [rsRMAOrder]![rdtrqt]


sSQL = "INSERT INTO tblRMA_Prior_Orders (
[RMA_Num],[Order_Num],[Cust_Num],[Shipped_Date],[Shipped_Qty],[Unit_Price],[Lng_Item])
VALUES("
sSQL = sSQL & " '" & [rsRMAOrder]![RDRORN] & "', " &
[rsRMAOrder]![SDDOCO] & ", " & [rsRMAOrder]![SDAN8] & ", " &
[rsRMAOrder]![SDIVD] & "," & [rsRMAOrder]![sdsoqs] & ", " &
[rsRMAOrder]![SDUPRC] & ",'" & [rsRMAOrder]![SDLITM] & "' )"
CurrentDb.Execute (sSQL)


Loop

rsRMAOrder.MoveNext


Loop
QtySum = 0





End If
End If



Exit_Err_cmdEnter_Click:
Exit Sub

Err_cmdEnter_Click:
MsgBox Err.Description
Resume Exit_Err_cmdEnter_Click

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Do While 2
Looping or Do While statement? 10
Do While... Looping through records on a form or RecordsetClone 0
Using an Access form to Mass Update a table 7
Using a Form to Mass Update a Table 1
Formula Help 2
Cannot reference object in custom collection 1
SQL query to Access database 5

Top