473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How do I run an append query with sequential numbers?

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

3 1309
PhilOfWalton
1,430 Expert 1GB
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
dstorms
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
1,430 Expert 1GB
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

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

Similar topics

2
by: Tony Williams | last post by:
I recently posted a message asking for help with sequential numbers. I want to create an autonnumber reference number that reverts back to 1 at the start of each year. GlenAppleton gave me some...
1
by: JD | last post by:
I'm using MS Access 97 in an XP environment. I have an append query at the top of a query tree that is behaving strangely. From design view, I can choose view datasheet - and I see one set of...
14
by: amywolfie | last post by:
Hi All: I know this is simple, but I just can't seem to get there: I need to sort a table by a text field (txtDescription), then assign sequential numbers to the field SEQUENCE in table. ...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
3
by: Finomosec | last post by:
Hi, i have a table of number-objects with beginning and endnr: 10-15 16-20 25-30 32-32 35-35 36-36 37-40
1
by: hr833 | last post by:
hi.. i'm using a append query to filter some new records that must be updated in the lookup table. In this lookup table it consist of the part number and the type of the product. the part number...
3
by: Excel 009 | last post by:
Hi, Is there a way to populate sequential numbers in a field using SQL or VBA? If yes, how? Assume the following is my existing table: Fruit ID Apply Banana
18
by: Joel Miller | last post by:
I found an article that was somewhat like what I was trying to do. The article was titled: SQL Query - Find block of sequential numbers Here is the article...
21
by: neelsfer | last post by:
Ok i am back with a question about recordsets. I can add a record to two different recordsets at the sametime.(RacetimingT and RaceEntry5) My problem: how do i add a sequential number to both...
3
by: Katie Howard | last post by:
Hi, I’m desperately looking for some help… I maintain a Contacts database which has about 27,000 records. It was initially set up for the field Contact_ID to be an autonumber (also my PK), and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.