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]

: 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.



Ordersinroute.JPG


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.

Analysis.JPG


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];