Loop until a value is null

[Snuggle Walrus]

Hello! I've got a really wacky weird problem i'm trying to solve. I'm trying to create a small loop thats going to generate the next number in line for a "linked purchase order" the first 3-4 digits will be the master purchase order number and the last digit is there to be an incriminate in case not all of the purchase order arrives in one go. So say i order 100 of something and go to book it in and 50 arrive i can run the rest of my code (i'll explain this in a mo and why this may be slightly confusing) and book the rest in later. My only problem is when i come to book in the same amount it kicks off a fuss and links to the same ID. Now the code in question is if i'm booking in what i've called a "Kit" and the parent item being booked in needs to actually adjust the stock of a child item, but because of how my pricing works purely off of Purchase orders I need to insert a new PO for the child items linked to a dedicated supplier so that it's not affected the 'master' po.... So i've tried something like this....

  If OGPQty > ParentProductOrderQty Then 'Checks to see if there is potentially an existing PO for the current Parent kit item.
    Dim IDLoop As Integer 'Sets the number of times its checked to see if an ID already exists
    Dim CurrentMaxLinkedID As Variant 'saved as a variant as this CAN store Null Values?
    Dim SCode As Integer 'Spaghetti code that just stores 1, will change to a 0 when my Variable from above is reading 'Null'
    SCode = 1
    IDLoop = 1
    PONotes = CurrentPOID & IDLoop 'this is whats storing my linked PO the name is just unfortunate as at the start i was storing it in the notes section, this was not ideal.
    CurrentMaxLinkedID = DLookup("[POID]", "[tbl_PurchaseOrders]", "[LinkedPO]=" & PONotes) 'looks up the purchase order ID number by checking the linked PO section for the PONotes variable.
    Do Until SCode = 0 'keep checking until Scode =0
    PONotes = PONotes + 1 'adds another count to the POnotes
    CurrentMaxLinkedID = DLookup("[POID]", "[tbl_PurchaseOrders]", "[LinkedPO]=" & PONotes) 'uses the new value to search
    If (CurrentMaxLinkedID Is Null) Then 'if the search returns a null value then
    MsgBox ("yay") 'this is only here for a visual que so i know when my code got to this point.
    SCode = 0 'Scode is set to 0 which SHOULD end the loop and still store the 'next in line' PONotes variable to be used in my insert queries after. (that code all works fine)
    End If
    Loop
    End If

I tried to comment my code to help it make sense. There may be an easier way to do all this but I cant seem to work it out. The rest of my code that inserts and manages the children items seems to work great but only i'm worried on it potentially adding onto a same PO if the qty ends up being the same at one point and causing an issue there.

 

[namliam]

1) whats wrong with your enter key? What a wall of text!
2) whats wrong with your language, does it really hurt to type proper english?
3) whats wrong with your space key? Indenting code is KEY to keeping it readable!

That beeing said... to find the max ID you dont dont have to use a loop with a dlookup.... Instead try using DMax

 

[Snuggle Walrus]

1) whats wrong with your enter key? What a wall of text!
2) whats wrong with your language, does it really hurt to type proper english?
3) whats wrong with your space key? Indenting code is KEY to keeping it readable!

That beeing said... to find the max ID you dont dont have to use a loop with a dlookup.... Instead try using DMax

Sorry I should have taken the time to structure it better. Appreciate the help that makes a lot more sense I don't know why I didnt use a Dmax to begin with. Have a nice day.

 

[Isaac]

Can't follow non-indented code. (how can you? i couldn't even write code for myself like that!)