Loop not working correctly

[Huffner1]

Hello,

I have three tables in an access database. I am only showing the fields that have are in the code

I am trying to add the pieces from the onrl table and add onhand from the rl table.

The total of onrl![pieces] + rl![onhand] = analysis[on hand amount]

The one shown below is called ONRL.



Cust
Sku
Pieces


31
283875
72


31
86944
96


31
93245
100


31
93245
100



2nd table is called Analysis



Store Number
Master Sku Number
on hand amount


31
283875
86


31
86944
36


31
93245
387








3rd table is called RL



Location Id
Metro Sku Number
onhand


31
283875
86


31
86944
36


31
93245
387









What I am trying to do will be shown below.


If rl![location id] = 514 Then
If rl![sku] = 10100 Then
k = 1
End If
End If
an.AddNew
an![store number] = rl![location id]
an![City] = rl![Location DESC]
an![State] = rl![State]
an![master sku number] = rl![Metro SKU Number]
an![Master SKU Description] = rl![Master SKU Description]

The above works correctly

Here is were the problem starts


Do While Not onrl.EOF '1635 rows of data in the onrl table
If an![store number] = onrl![cust] And an![master sku number] = onrl![sku number] Then
'first run of the code
'an![store number] = 31 and onrl![cust] = 31 'This is correct
'an![master sku number]=4276 and onrl![sku number] =283875
'second run of the code
'first line the same 31 This is correct
'an![master sku number]=10178 and onrl![sku number] =283875
'need to figure out how to get it to go to the next one. Stuck on 283875

an![on hand amount] = rl![onhand] + onrl![pieces] '
Else: an![on hand amount] = rl![onhand]
'an.Update
Exit Do
End If
onrl.MoveNext
Loop

I can see the correct numbers in the code(cursor over the code) 72+86=158
however I am unable to get it to go to the next one. What is wrong with my loop? Any suggestions would be greatly appreciated.

Ken

If you need a better explanation I can try to provide one.

[SamT]

:dunno: Try this

Do''''
IF x Then '''
Else: an![on hand amount] = rl![onhand]
'an.Update
GoTo LoopNext
End If
onrl.MoveNext
LoopNext:
Loop

[jonh]

Is there a reason you are doing this in code?

Always use queries where possible. They execute much quicker and are far easier to keep up to date.

[Huffner1]

Jonh,

I think you are correct. I will need to figure out an update or append query to put these things together.

I would need to figure out the query then call it from within the code. Sounds easy I Know,However if it was easy everyone would be doing it.

[jonh]

Well if you need any help with the query let us know.

You'll find working with tables and queries a lot easier if you avoid using spaces and other non alphanumeric characters.
Use underscores and/or camel case to keep things readable.
e.g.
MasterSKU
Master_SKU_Number

Running action queries from code couldn't really get much simpler


currentdb.execute "QueryName"

[Huffner1]

jonH,

I created a simple query.

UPDATE Analysis INNER JOIN ordersinroute
ON (Analysis.[Master Sku Number] = ordersinroute.[Sku Number])
AND (Analysis.[Store Number] = ordersinroute.[cust])
SET Analysis.Totals = pieces +[on hand amount];

It most certainly does the trick but maybe a little to well. What I mean by this is that I have a store #31. Within the Ordersinroute table store #31 has 4 rows of data. These 4 rows show the correct totals.



10788


When I run my query it shows that store number #31 has 25 rows that need to be updated in the Analysis table. This is incorrect and I have been working most of the day to just update the ones who have pieces in the ordersinroute table.

I was going to upload another pic however it allows me to upload only one per post.

Let me know what else I can provide.

Thanks,

Ken

[Huffner1]

jonH,

Here is the second pic that might give you more insight.

10789


Ken

[Huffner1]

Hello,

I would like to thank you jonH for pointing me in a better direction. By creating the query I have made it work. You are absolutely correct writing a query is much much simpler then trying to write it in code. Please mark this as solved.

UPDATE Analysis INNER JOIN ordersinroute
ON (Analysis.[Store Number] = ordersinroute.[cust])
AND (Analysis.[Master Sku Number] = ordersinroute.[Sku Number])
SET Analysis.Totals = pieces +[on hand amount];