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

How do I run an append query with sequential numbers?

P: 25
I've creating a form (frmPurchases) in which I add new records to a table (tblItems) starting with a number I can set (TagNo). If I buy 5 copies of "Harry Potter and the Prisoner of Azkaban" from Amazon and then apply stickers numbering from 41 to 45, I would enter the following:

TagNo: 41
Qty: 5
Vendor: Amazon
ModelName: HP-Azkaban

At the click of the button I want this output:

Amazon HP-Azkaban 41
Amazon HP-Azkaban 42
Amazon HP-Azkaban 43
Amazon HP-Azkaban 44
Amazon HP-Azkaban 45

Instead I get this:

Amazon HP-Azkaban 41
Amazon HP-Azkaban 41
Amazon HP-Azkaban 41
Amazon HP-Azkaban 41
Amazon HP-Azkaban 41

So far I have a sub with a call to a function that is supposed to change the number each time the append query runs.

Sub:
Expand|Select|Wrap|Line Numbers
  1. Private Sub AddItems_Click()
  2.  
  3. Dim aSQL As String
  4. Dim TN As Integer
  5. Dim Q As Integer
  6.  
  7. TN = Me.TagNo
  8. Q = Me.Qty
  9.  
  10. aSQL = "INSERT INTO tblItems ( Vendor, ModelName, TagNo )"
  11. aSQL = aSQL & " VALUES ('" & Me.Vendor & "', " & Me.ModelName & ", " & SetTagNo(X) & ") "
  12.  
  13. For n = 1 To Q
  14. CurrentDb.Execute aSQL, dbFailOnError
  15. TN = TN + 1
  16. Next n
  17.  
  18. Exit Sub
  19.  
Function:
Expand|Select|Wrap|Line Numbers
  1. Function SetTagNo(X As Variant) As Integer
  2.  
  3. Static intTN As Integer
  4.  
  5. If intTN = 0 Then
  6. intTN = Forms!frmPurchases!TagNo - 1
  7. End If
  8.  
  9. intTN = intTN + 1
  10. SetTagNo = intTN
  11.  
  12. End Function
  13.  
Can someone figure out what I need to do to make it work?

Doug
Jul 13 '17 #1

✓ answered by PhilOfWalton

Try Aircode

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddItems_Click()
  2.  
  3.      Dim MyDb as Database
  4.      Dim ItemSet as Recordset
  5.      Dim STRSQL as String
  6.      Dim i as Integer
  7.  
  8.     StrSQL = "SELECT TblItems.* FROM TblItems;"
  9.  
  10.     Set MyDb - CurrentDb
  11.     Set ItemSet = MyDb.OpenRecordset(StrSQL)
  12.  
  13.     With ItemSet
  14.         For i = 0 to Qty -1
  15.            .AddNew
  16.            !Vendor = Vendor
  17.            !ModelName = ModelName
  18.            !Tag = Tag + i
  19.            .Update
  20.            i = i + 1
  21.        Next i
  22.        .Close
  23.        Set ItemSet = Nothing
  24.    End With
  25.  
  26. End Sub
  27.  
Phil

Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
Try Aircode

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddItems_Click()
  2.  
  3.      Dim MyDb as Database
  4.      Dim ItemSet as Recordset
  5.      Dim STRSQL as String
  6.      Dim i as Integer
  7.  
  8.     StrSQL = "SELECT TblItems.* FROM TblItems;"
  9.  
  10.     Set MyDb - CurrentDb
  11.     Set ItemSet = MyDb.OpenRecordset(StrSQL)
  12.  
  13.     With ItemSet
  14.         For i = 0 to Qty -1
  15.            .AddNew
  16.            !Vendor = Vendor
  17.            !ModelName = ModelName
  18.            !Tag = Tag + i
  19.            .Update
  20.            i = i + 1
  21.        Next i
  22.        .Close
  23.        Set ItemSet = Nothing
  24.    End With
  25.  
  26. End Sub
  27.  
Phil
Jul 13 '17 #2

P: 25
Thank you Phil.

I didn't know about the AddNew method, so I learned something new today. I had to remove the i = i + 1 line because it wasn't necessary in a FOR... NEXT loop, it was skipping numbers. (Its output was 1, 3, 5, instead of 1, 2, 3, 4, 5.) Otherwise the code worked as intended.

Doug
Jul 14 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Glad to help Doug.

I have a feeling when you were getting 1,3,5 it was because the code said
!Tag = !Tag + i instead of
!Tag = Tag + i

Phil
Jul 14 '17 #4

Post your reply

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