Problem with my code...for each loop

[Guest]

Hi all,
I am working on developing an application for my company and I have got
struck up with a step where, it looks like, I need to do a loop through my
records. I have given a sample recordset similar to the one I have below
where I have 5 fields and the fifth field in that, which is called "Total" is
going to be the calculated field and that's where I need a looping. What that
field signifies is Total==(quantity*Percent) if 'Number' field=0; else for
each Order Type: (quantity-number)*percent)…in this case it would be 25. I
have given the coding that I tried below the sample data. But apparently it's
not the way to appoach it. Can any of you suggest why it's not working and
maybe a better way of achieving this? I would really appreciate your help on
this. Thanks!

Order Quantity Percent Number Total
A 100 50% 25
A 100 50% 25
A 100 50 50
B 200 50% 100
B 200 50% 100

Private Sub cmdCalc_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim Order As Field
Dim quantity As Field
Dim number As Field
Dim total As Field
Dim percent As Field
With rs
..CursorType = adOpenDynamic
..Open "sheet1", CurrentProject.Connection
End With
Do While Not rs.EOF
For Each rs!Order In rs
If rs!number = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If
Next rs!Order
Loop
End Sub

 

[Tim Ferguson]

Do While Not rs.EOF
For Each rs!Order In rs
If rs!number = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If
Next rs!Order
Loop

I don't often use ADO recordsets, because I don't see the point of them;
but this is not the way to iterate one. Start with the open RS and loop it
like this:

' sorry, I'm old-fashioned enough to prefer While Not to Until
Do While Not rs.EOF

' ADO is a bizarre toolkit: you don't need this line
' rs.Edit

If rs![number] = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If

' but you really do need this line to send the changes back
' to the table
rs.Update

' look at the next row
rs.MoveNext

' all done
Loop



Mind you it's probably easier to use SQL and do the whole thing on the
server:

UPDATE Sheet1
SET Total = PerCent * (Quantity - IIF([Number]>0,p[Number],0))

Having looked a bit closer at that, I am assuming that you have made a
mistake in the description, since (Quantity - 0) is exactly the same as
(Quantity - [Number]) and the whole thing is trivial. Since it's so
trivial, the whole problem becomes a nonsense since the Total field does
not belong in the table; it should be calculated in the query.

Another point: [Number] is a really bad thing to call a database object,
since it's a reserved word in SQL and will cause bugs later on.

So, just chuck out the buggy code altogether and remove the unneccessary
field. Works for me every time!


Tim F

 

[Guest]

Hi Tim,
Thanks a lot for your response! The SQL approach you suggested is a great
idea certainly in this case. But I think I haven't put myself clear to you. I
will try to explain the table in a better way. Let's say I need to get 100
parts of type X and I have decided to get 20% from supplier A; 50% from
supplier B and 30% from supplier C. So the total field, in this case, would
be (100*20%) for Row 1; (100*50%) for Row 2; (100*30%) for Row 3. But in some
case rather than giving the supplier type in percentage, we may give the data
in Numerical quantity. For example, let's say, I need another 200 parts in
TYPE Y and I am going to get 20% from supplier A; 80% from supplier B and
also I have a special request here which says, out of those 200 parts I need
to get 100 parts from Supplier C(this is the "Number" field...and this is not
the actual name...actual name is SplRequest...I named it this way here for
convenience). That means, I will only get 20% of 100 parts from supplier A
and NOT 20% of 200 parts. So my total field, in this case, would be
((200-100)*20% for Row 1; ((200-100)*80%) for Row 2; and 100 parts for Row 3.
I have reproduced the table here again for convenience:

Type Quantity Supplier Percent Number Total
X 100 A 20% 20
X 100 B 50% 50
X 100 C 30% 30
Y 200 A 20% (200-100)*20%=20
Y 200 B 80% (200-100)*80%=80
Y 200 C 100 100

So my point is the update command would work fine if I don't have the "Type"
field. But, with the Type field, it's like each "Type" acts as a group. In
other words, if I just say Total=PerCent * (Quantity -
IIF([Number]>0,p[Number],0)), it would apply that to all the records which is
not the desired case as you can see in the table(If it had been that way,
field corresponding to Type Y and supplier A would have been 40 and Not 20).
It should do that individually for each part type. That means I need a
for...Next loop here. But I don't want to consider each record as unique; in
other words the "Type" X should be considered as a group and it's same for
Type Y. So to achieve this, I used the following code:

Do While Not rs.EOF
For Each rs!Type In rs
UPDATE Sheet1
SET Total = PerCent * (Quantity - IIF([Number]>0,p[Number],0))
Next rs!Type

But it's not working for some reason. Do you see why or perhaps a better way
to achieve this? Thanks, once again, for your help!

Do While Not rs.EOF
For Each rs!Order In rs
If rs!number = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If
Next rs!Order
Loop

I don't often use ADO recordsets, because I don't see the point of them;
but this is not the way to iterate one. Start with the open RS and loop it
like this:

' sorry, I'm old-fashioned enough to prefer While Not to Until
Do While Not rs.EOF

' ADO is a bizarre toolkit: you don't need this line
' rs.Edit

If rs![number] = 0 Then
rs!total = rs!quantity * rs!percent
Else
rs!total = (rs!quantity - rs!number) * rs!percent
End If

' but you really do need this line to send the changes back
' to the table
rs.Update

' look at the next row
rs.MoveNext

' all done
Loop



Mind you it's probably easier to use SQL and do the whole thing on the
server:

UPDATE Sheet1
SET Total = PerCent * (Quantity - IIF([Number]>0,p[Number],0))

Having looked a bit closer at that, I am assuming that you have made a
mistake in the description, since (Quantity - 0) is exactly the same as
(Quantity - [Number]) and the whole thing is trivial. Since it's so
trivial, the whole problem becomes a nonsense since the Total field does
not belong in the table; it should be calculated in the query.

Another point: [Number] is a really bad thing to call a database object,
since it's a reserved word in SQL and will cause bugs later on.

So, just chuck out the buggy code altogether and remove the unneccessary
field. Works for me every time!


Tim F

 

[Tim Ferguson]

Let's say I need to get 100
parts of type X and I have decided to get 20% from supplier A; 50%
from supplier B and 30% from supplier C. So the total field, in this
case, would be (100*20%) for Row 1; (100*50%) for Row 2; (100*30%) for
Row 3. But in some case rather than giving the supplier type in
percentage, we may give the data in Numerical quantity. For example,
let's say, I need another 200 parts in TYPE Y and I am going to get
20% from supplier A; 80% from supplier B and also I have a special
request here which says, out of those 200 parts I need to get 100
parts from Supplier C

Surely all this lot is a job for the front end, not the data... if there
is some auditing reason for you to keep a trail of why each order ended
up the size it is, then that would make some sense. But I would still
transfer all this validating and massaging to the user...

I imagine a form with a big list box; there is another list box with all
the suppliers in it; when you dbl-click the suppliers another dialog will
pop up asking for the percentage (and validate the running total against
100%) or offer the chance to force a set number instead; the supplier
with the required number of orders will be entered into the big list box.
The form itself would validate the percentages and recalculate each order
if the total changes. Etc.

But I would still use the tables to hold the NumberToBeOrdered and
perhaps a boolean NumberForced. Everything else can be calculated so it
should not be stored.

Using extensice code to work round a badly normalised schema is a bad
path to go down!

B Wishes


Tim F

 

[Guest]

Thanks, Tim. That's a good idea to adjust my table right at the form by
having the user enter one more data(i.e if they enter "Number" field, I will
have them enter the total quantity allocated for that "Type" and by that I
can convert my number into a percentage thereby I don't need a field for
"Number" any more. That indeed a good idea. Thanks again. I appreciate it.