By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Generating Automatic PO Numbers via SQL within Loop Procedures

100+
P: 129
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:

Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim rst2 As Recordset
  4.     Dim rst3 As Recordset
  5.     Dim IntPONo As Integer
  6.     Dim strSQL As String
  7.     Dim PO As String
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
  13.  
  14.     If IsNull(txtSuppNo) Then
  15.         MsgBox "You CANNOT Assign an Automatic Purchase Order when there are NO SUPPLIER Or ORDER Details.", vbOKOnly, "Criteria Missing"
  16.     Else
  17.     rst.Edit
  18.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  19.     DoCmd.RunSQL strSQL
  20.     rst.update
  21.     rst.Close
  22.  
  23.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  24.  
  25.     rst3.MoveLast
  26.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  27.     rst3.Close
  28.  
  29.     Set rst3 = Nothing
  30.  
  31.     Set rst3 = CurrentDb.OpenRecordset("SELECT * FROM pordhdr")
  32.     rst3.MoveLast
  33.  
  34.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  35.  
  36.     rst2.Edit
  37.     strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  38.     DoCmd.RunSQL strSQL
  39.     rst2.update
  40.     rst2.Close
  41.     rst3.Close
  42.  
  43.     Set rst2 = Nothing
  44.     Set rst3 = Nothing
  45.  
  46.     DoCmd.SetWarnings True
  47.  
  48.     lstSuppNoSelect.Requery
  49.     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?
Sep 2 '08 #1
Share this Question
Share on Google+
20 Replies


100+
P: 129
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.
Sep 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 2 '08 #3

100+
P: 129
Yeah sorry, it is an AutoNumber
Sep 2 '08 #4

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 3 '08 #5

100+
P: 129
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim Reply As String
  3.     Dim db As Database
  4.     Dim rst As Recordset
  5.     Dim SeqItemNo As Integer
  6.  
  7.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  13.  
  14.     Do
  15.         rst.Edit
  16.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  17.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  18.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  19.         'rst!ItemNo = ItemNo + 1 - Attempt 1: Result: 1st and 2nd Row worked, 3rd and 4th were the same as row 2.
  20.         'SeqItemNo = rst!ItemNo + 1 - Attempt 2: Result: Could not assign value to field error.
  21.         '[Forms]![frmPreSOLine]![ItemNo].Value = SeqItemNo
  22.         rst.update
  23.         rst.MoveNext
  24.     Loop Until rst.EOF
  25.     rst.Close
  26.     Me.Requery
  27.     Set rst = Nothing
  28.  
  29.     cmdCheckStock.Enabled = False
  30.  
  31.     If IsNull(StkID) Then
  32.         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!")
  33.         If Reply = vbYes Then
  34.             DoCmd.RunSQL strSQL
  35.         Else
  36.         End If
  37.     Else
  38.     End If
  39.  
  40.     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;

Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("SELECT * FROM ordlin WHERE (([ItemNo]) > 0) ORDER BY [ItemNo]")
  2.  
  3. rst.edit
  4. 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?
Sep 3 '08 #6

NeoPa
Expert Mod 15k+
P: 31,342
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;
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("SELECT * FROM ordlin WHERE (([ItemNo]) > 0) ORDER BY [ItemNo]")
  2.  
  3. rst.edit
  4. 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).
Sep 4 '08 #7

100+
P: 129
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:

Expand|Select|Wrap|Line Numbers
  1.     Set db = CurrentDb()
  2.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  3.  
  4.     Do
  5.         rst.Edit
  6.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  7.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  8.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  9.         rst!ItemNo = [ItemNo] + 1
  10.         rst.update
  11.         rst.MoveNext
  12.     Loop Until rst.EOF
  13.     rst.Close
  14.     Me.Requery
  15.     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?
Sep 4 '08 #8

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 4 '08 #9

100+
P: 129
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:

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim Reply As String
  3.     Dim db As Database
  4.     Dim rst As Recordset
  5.     Dim SeqItemNo As Integer
  6.  
  7.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  13.  
  14.     DoCmd.Save
  15.     DoCmd.GoToRecord , , acFirst
  16.  
  17.     Do
  18.         rst.Edit
  19.         SeqItemNo = [ItemNo].Value
  20.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  21.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  22.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  23.         [ItemNo] = rst!ItemNo
  24.         rst!ItemNo = [SeqItemNo] + 1
  25.         rst.update
  26.         rst.MoveNext
  27.     Loop Until rst.EOF
  28.     rst.Close
  29.     Me.Requery
  30.     Set rst = Nothing
Would this sort of code writing be correct?
Sep 4 '08 #10

100+
P: 129
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?
Sep 4 '08 #11

NeoPa
Expert Mod 15k+
P: 31,342
I'm working on it right now.
Sep 4 '08 #12

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 4 '08 #13

NeoPa
Expert Mod 15k+
P: 31,342
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.
Expand|Select|Wrap|Line Numbers
  1. .
  2.     Dim strSQL As String
  3.     Dim Reply As String
  4.     Dim db As Database
  5.     Dim rst As Recordset
  6.     Dim intInc As Integer
  7.  
  8.     strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
  9.  
  10.     DoCmd.SetWarnings False
  11.  
  12.     Set db = CurrentDb()
  13.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  14.  
  15.     DoCmd.Save
  16.     DoCmd.GoToRecord , , acFirst
  17.  
  18.     intInc = 0
  19.     Do
  20.         rst.Edit
  21.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  22.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  23.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  24.         rst!ItemNo = [ItemNo] + intInc
  25.         rst.update
  26.         intInc = intInc + 1
  27.         rst.MoveNext
  28.     Loop Until rst.EOF
  29.     rst.Close
  30.     Me.Requery
  31.     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.
Sep 4 '08 #14

100+
P: 129
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.
Sep 4 '08 #15

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 5 '08 #16

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 5 '08 #17

100+
P: 129
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim SeqItemNo As Integer
  4.  
  5.     DoCmd.SetWarnings False
  6.  
  7.     Set db = CurrentDb()
  8.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  9.  
  10.     SeqItemNo = 1
  11.  
  12.     Do
  13.         rst.Edit
  14.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  15.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  16.         rst!Cost = DLookup("[CostPrice1]", "primas", "[StkID] =" & rst!StkID)
  17.         rst!ItemNo = [ItemNo] + SeqItemNo
  18.         rst.update
  19.         SeqItemNo = SeqItemNo + 1
  20.         rst.MoveNext
  21.     Loop Until rst.EOF
  22.     rst.Close
  23.     Me.Requery
  24.     Set rst = Nothing
Sep 5 '08 #18

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 5 '08 #19

100+
P: 129
I got it, thanks for your help, it was much appreciated. All i did was change the
Expand|Select|Wrap|Line Numbers
  1. rst!ItemNo = [ItemNo] + SeqItemNo
to
Expand|Select|Wrap|Line Numbers
  1. rst!ItemNo = rst!ItemNo + SeqItemNo
Thanks again!
Sep 5 '08 #20

NeoPa
Expert Mod 15k+
P: 31,342
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 :)
Sep 5 '08 #21

Post your reply

Sign in to post your reply or Sign up for a free account.