Generating Automatic PO Numbers via SQL within Loop Procedures

[Constantine AI]

Hi I have this code which i thought was perfect maybe a bit of a mess but it works. The only problem is that the rst!PONo does not look at the table properly and generate a continuing number, which i fine but i would prefer it to fill in the blanks plus carryon from the last record in the table. Also the last problem is that assigning this PONo to relevant records within a loop procedures works sometimes and then doesn't. Here is the code:

    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim IntPONo As Integer
    Dim strSQL As String
    Dim PO As String
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
 
    If IsNull(txtSuppNo) Then
        MsgBox "You CANNOT Assign an Automatic Purchase Order when there are NO SUPPLIER Or ORDER Details.", vbOKOnly, "Criteria Missing"
    Else
    rst.Edit
    strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
    DoCmd.RunSQL strSQL
    rst.update
    rst.Close
 
    Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst3.MoveLast
        PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    rst3.Close
 
    Set rst3 = Nothing
 
    Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
    rst3.MoveLast
 
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
 
    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
    DoCmd.RunSQL strSQL
    rst2.update
    rst2.Close
    rst3.Close
 
    Set rst2 = Nothing
    Set rst3 = Nothing
 
    DoCmd.SetWarnings True
 
    lstSuppNoSelect.Requery
    End If

When assigning the PONO to the ordlin table, it assigns this value for half the order and then decides to stop for some unknown reason. Can you see a problem with my coding?

[Constantine AI]

For the counter problem i have three records within the pordhdr numbering 9, 10 and 11 so the next PONo should be 12 however it is 116. The counter does add in 1's and i have created this many records however rapid deletions have been made, is there a way to solve this code so it fills in the blanks and cancels the pordhdr number if the code fails. This way an unused podrhdr number is not wasted.

[NeoPa]

I'm guessing from this that PONo is a field set up as an AutoNumber.

I can see no other way for this to work, yet I can't believe anyone would ask this question without mentioning the most important point of the whole situation.

Can you confirm / deny this before I continue so I know what I'm dealing with.

[Constantine AI]

Yeah sorry, it is an AutoNumber

[NeoPa]

In that case I'm afraid you're suffering from a very common misunderstanding about how AutoNumbers work. In essence, they cannot be used to provide the functionality you require :(

All is not doom and gloom though, there are ways of finding the logically following value from the existing data in the table. I'm not sure that existing holes can be filled up, but certainly the next available value AFTER all existing ones is possible.

It appears you may have other problems though. What happens if you display this in a form for entering new items in, then the addition is cancelled? Or if new ones are added simultaneously (They might both try to add a record with the same new value)?

Let me know your thoughts on this and whether or not you are interested in taking this further.

[Constantine AI]

In that case I'm afraid you're suffering from a very common misunderstanding about how AutoNumbers work. In essence, they cannot be used to provide the functionality you require :(

All is not doom and gloom though, there are ways of finding the logically following value from the existing data in the table. I'm not sure that existing holes can be filled up, but certainly the next available value AFTER all existing ones is possible.

It appears you may have other problems though. What happens if you display this in a form for entering new items in, then the addition is cancelled? Or if new ones are added simultaneously (They might both try to add a record with the same new value)?

Let me know your thoughts on this and whether or not you are interested in taking this further.

Yes i do see your point of that issue. I think i may just have to leave the PONo as it is, however do you know why the PONo only gets assigned to some data and not all of the records it is required to?

With what you were saying before i could use the logical numbering on a different table. I have another table called 'preordlin' which contains stock items. I have a procedure that imports a csv file into the table. However i would like it to assign 'ItemNos' to the stock, so if there were 4 stocks items. The numbers would be 1 - 4. I do not want an AutoNumber for this feature as Additional Stock details will be assigned to the Stock (Sub-Stock and Component-Stock) I just need the numbering system to work for the top level individual stock items and not count the Sub-Stock and Component-Stock as items as well. I have tried various coding methods for this procedure but no luck so far.

    Dim strSQL As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
    Dim SeqItemNo As Integer
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        'rst!ItemNo = ItemNo + 1 - Attempt 1: Result: 1st and 2nd Row worked, 3rd and 4th were the same as row 2.
        'SeqItemNo = rst!ItemNo + 1 - Attempt 2: Result: Could not assign value to field error.
        '[Forms]![frmPreSOLine]![ItemNo].Value = SeqItemNo
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    cmdCheckStock.Enabled = False
 
    If IsNull(StkID) Then
        Reply = MsgBox("There are Stock Items present that do not belong to our existing Stock files. Would you like to ADD them?", vbYesNo, "None Existent Stock Details!")
        If Reply = vbYes Then
            DoCmd.RunSQL strSQL
        Else
        End If
    Else
    End If
 
    DoCmd.SetWarnings True 

However when it does eventually work i need it to lookup the last number within the table 'ordlin' and assign the number after to the 'preordlin' table. Table 'ordlin' is where the data gets imported into after csv file.

I have thought about doing an;

Set rst = db.OpenRecordset("SELECT * FROM ordlin WHERE (([ItemNo]) > 0) ORDER BY [ItemNo]")
 
rst.edit
rst.movelast

But then i loose the plot when assigning this value to the start of the preordlin value, for the next stock items. So the first batch was numbered 1 - 4 so the second batch would be 5 - unlimited.

What do you think i could do?

[NeoPa]

Yes i do see your point of that issue. I think i may just have to leave the PONo as it is, however do you know why the PONo only gets assigned to some data and not all of the records it is required to?
...

I don't think I have any info from you that might explain that.

...
With what you were saying before i could use the logical numbering on a different table. I have another table called 'preordlin' which contains stock items. I have a procedure that imports a csv file into the table. However i would like it to assign 'ItemNos' to the stock, so if there were 4 stocks items. The numbers would be 1 - 4. I do not want an AutoNumber for this feature as Additional Stock details will be assigned to the Stock (Sub-Stock and Component-Stock) I just need the numbering system to work for the top level individual stock items and not count the Sub-Stock and Component-Stock as items as well. I have tried various coding methods for this procedure but no luck so far.
...

Typically, we avoid long complex questions on here. It is rarely practical as the amount of information needed makes these types of question extremely difficult to handle.

In this case for instance, you refer to various tables without any information related to those tables. It would be a job of great intricacy simply to try and work out what question you're actually asking.

Furthermore, this doesn't seem to be a response to my last post, but you've just gone off at a tangent. There's no real problem with that, but I don't choose to follow you there.

...
I have thought about doing an;

Set rst = db.OpenRecordset("SELECT * FROM ordlin WHERE (([ItemNo]) > 0) ORDER BY [ItemNo]")
 
rst.edit
rst.movelast

...

I won't get into anything too complex with this, but some immediate observations are :

1. WHERE (([ItemNo]) > 0) is almost certainly redundant. Not absolutely definite, but having [ItemNo] = 0 would be unusual.
2. After a .Edit() on a recordset, moving the pointer (as in .MoveLast()) is not recommended until a .Update() is called. This is because you are essentially in the middle of an update for an individual record. Moving the pointer half way through doesn't make logical sense (unless you WANT to cancel the update which is the effect it will have).

[Constantine AI]

Ok thanks for the advice and tips. I understand about the coding now and where i was going wrong. I have this modified code version:

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = [ItemNo] + 1
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

The rst!ItemNo = [ItemNo] + 1 works for the first two rows of data and assigns the correct sequential numbers to the ItemNo attribute. But after that the ItemNo just repeat themselves and create duplicates.

Is there a way i can tell the code to look at the previous 'ItemNo' and then + 1 to that instead of what i have which always looks at the first row assignment?

[NeoPa]

You threw me by referring to the first two rows :S Looking at the code, only one row should work.

I'm assuming your reference to [ItemNo] in line #9 is actually a control on your form. If so, this is not being updated anywhere, so this value + 1 will always be the same value processing through your Do loop.

I suggest you set a variable (Integer or Long) and increment it within your loop. Adding this variable to [ItemNo] would give an incrementing set of values as you seem to require.

[Constantine AI]

You threw me by referring to the first two rows :S Looking at the code, only one row should work.

I'm assuming your reference to [ItemNo] in line #9 is actually a control on your form. If so, this is not being updated anywhere, so this value + 1 will always be the same value processing through your Do loop.

I suggest you set a variable (Integer or Long) and incremet it within your loop. Adding this variable to [ItemNo] would give an incrementing set of values as you seem to require.

I have tried this idea before but before i try it again, with me using the loop procedure, do i incorporate the variable within the loop or outside as i have had error where it says, you cannot edit this field or you can not assign a value to this field.

For example would i do the following:

    Dim strSQL As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
    Dim SeqItemNo As Integer
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    DoCmd.Save
    DoCmd.GoToRecord , , acFirst
 
    Do
        rst.Edit
        SeqItemNo = [ItemNo].Value
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        [ItemNo] = rst!ItemNo
        rst!ItemNo = [SeqItemNo] + 1
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

Would this sort of code writing be correct?

[Constantine AI]

Ok i have tried various ways of doing the variable and still manage not to get it working. I either get a return value of the same number, errors or nothing. Could you please show me how to use variables properly?

[NeoPa]

I'm working on it right now.

[NeoPa]

Your most recently posted code doesn't really get to grips with the issue. I will try to get something out to you shortly, but I don't want simply to post the code.

I'm also putting together some explanation with it.

[NeoPa]

What you need is for the variable to be set BEFORE going into the loop. Within the loop it is incremented, and used.

This gives a different value every time it is used.

In our case, we want the variable to start at 0 (or 1 if the value in Me.ItemNo is never to be used as is). I will use 0 in this example. I am using an integer variable intInc for this. I have underlined the principal changes.

.
    Dim strSQL As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
    Dim intInc As Integer
 
    strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    DoCmd.Save
    DoCmd.GoToRecord , , acFirst
 
    intInc = 0
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = [ItemNo] + intInc
        rst.update
        intInc = intInc + 1
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

I have principally used your latest code with some changes for this particular point. This doesn't indicate that I've checked through the rest of your code simply because I leave it as it was.

[Constantine AI]

Hi thanks for the code. I have used your example and applied it to my existing code. It works but for some random reason it displays the numbers 1,3,4,5 and misses out the number 2. All i did was to change your intinc from 0 to 1. As my table shouldn't contain the number 0 as an ItemNo.

Do you know why this is? Besides that the code works fine.

[NeoPa]

Let me just look some more. I just checked the code and noticed a line of your earlier code that should have been removed.

I've removed it now and am now scanning through the code to see if I can answer your question.

[NeoPa]

I've looked and can't see anything.

Can you post the actual code you used. There may be something in there that you don't even think of as a change.

[Constantine AI]

Dim db As Database
    Dim rst As Recordset
    Dim SeqItemNo As Integer
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    SeqItemNo = 1
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
        rst!ItemNo = [ItemNo] + SeqItemNo
        rst.update
        SeqItemNo = SeqItemNo + 1
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

[NeoPa]

I can honestly say that I can see no reason why that code, as posted, would skip from 1 to 3 after the first record, but then increment normally thereafter.

Try tracing through the execution of the code and seeing where either Me.ItemNo changes from 0 to 1 or where SeqItemNo skips a number.

Check out Debugging in VBA for help with that.

[Constantine AI]

I got it, thanks for your help, it was much appreciated. All i did was change the

rst!ItemNo = [ItemNo] + SeqItemNo

to

rst!ItemNo = rst!ItemNo + SeqItemNo

Thanks again!

[NeoPa]

I'm glad I was able to help, but changing what you did would have been the last thing I would have guessed to do. I suppose I don't know exactly how your data is organised :S

Anyway, the important thing is that you found a solution :)