Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2023
    Posts
    15

    Adding certain amount of incremental values to table, if conditions are met.

    Hi everyone,
    I'm a novice in Access, SQL and VBA. I've run into an issue I don't know how to approach. I simply don't have a programmers logical mindset.

    I'm trying to create a form in Access 2016 where I can add a specified amount of incremental serial numbers to a table at the push of a button.
    Click image for larger version. 

Name:	concept.png 
Views:	34 
Size:	4.6 KB 
ID:	49883
    What I have in my head is:
    - When button is clicked
    - If "Check" is true,
    - Add "amount" of "Name" incrementally
    - to table with a new serial number.



    The whole thing is meant to add checked names to the table with the amount specified in the "amount"-box.

    I'm not sure if I have described what I'm trying to do well enough. Feel free to ask questions and thank you in advance. Any help or direction is highly appreciated.

  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by SanguineWizard View Post
    I'm not sure if I have described what I'm trying to do well enough.
    Not really, for me anyway.

    Where to start with the serial number?
    What If there are already serial numbers for that name?
    Should the name exist in an other table?
    What is the bigger picture? Why you want to do this?
    Groeten,

    Peter

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Requirement is not clear. Suggest you provide a few examples showing a starting scenario, and the result of some incremental add.

    The
    amount of name
    is confusing.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In the button's Click event your code would need to get the existing Max serial number for the entered Name then simply do a loop from 1 to Amount and add the record:

    Here is some air code that would achieve that, you will obviously need to adapt it to match your naming convention and data types (if the serial has alphanumeric characters you will need to isolate the numeric part that you want incremented).
    Code:
    Dim i as Integer, vSerial as Variant
    vSerial=dmax("SerialNumber","tblYourTable","NameField='" & Me.Name & "'")
    For i=1 to Me.Amount
        CurrentDB.Execute "INSERT INTO tblYourTable (NameField,SerialNumber) VALUES ('" & Me.Name & "','" & vSerial+i & "'")",dbfailOnError
    Next i
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Mar 2023
    Posts
    15
    Where to start with the serial number?: I've set the serial numbers to start from 50000 and be incremental.
    What If there are already serial numbers for that name?: I've made sure that no existing serial number is higher than 49000.
    Should the name exist in an other table?: Eventually it should be transferred to the serial number table.
    What is the bigger picture? Why you want to do this?: My colleague needs to pull the serial numbers from a table, when he makes the product labels in a software called Bartender.
    As of right now, he is entering the serial numbers manually in the "serial numbers" table. I need to make a function where he can chose a product and make as many new serial numbers as he needs. Ideally, he would put in, 10 for example, in the form, click the button, and then it automatically makes 10 new serial numbers tied to the name of the product. Moving fields from one table to another is easy, but making entirely new ones is a different story. Atleast for me.
    Last edited by SanguineWizard; 03-16-2023 at 04:48 AM.

  6. #6
    Join Date
    Mar 2023
    Posts
    15
    Basic500 is the name of the product. This is one field in the table.
    Where the "5" is, is where the person should enter the amount of serial numbers needed to be created for "Basic500".
    "Opret SerieNr" is just the button to execute the function.
    Hope this is clearer. Sorry for my poor explanations.
    Click image for larger version. 

Name:	createeeepng.png 
Views:	24 
Size:	2.7 KB 
ID:	49887

  7. #7
    Join Date
    Mar 2023
    Posts
    15
    Thx. I'll try to fiddle around with this. It makes somewhat sense to me. With the help of "VBA for dummies" book, maybe I can figure it out. Still interested to see how many ways this can be made.

  8. #8
    Join Date
    Mar 2023
    Posts
    15
    Quote Originally Posted by Gicu View Post
    In the button's Click event your code would need to get the existing Max serial number for the entered Name then simply do a loop from 1 to Amount and add the record:

    Here is some air code that would achieve that, you will obviously need to adapt it to match your naming convention and data types (if the serial has alphanumeric characters you will need to isolate the numeric part that you want incremented).
    Code:
    Dim i as Integer, vSerial as Variant
    vSerial=dmax("SerialNumber","tblYourTable","NameField='" & Me.Name & "'")
    For i=1 to Me.Amount
        CurrentDB.Execute "INSERT INTO tblYourTable (NameField,SerialNumber) VALUES ('" & Me.Name & "','" & vSerial+i & "'")",dbfailOnError
    Next i
    Cheers,
    I've made it this far. Would you be able to explain the error?

    Click image for larger version. 

Name:	code.png 
Views:	23 
Size:	21.1 KB 
ID:	49889
    Last edited by SanguineWizard; 03-16-2023 at 04:50 AM.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    try this

    Code:
    CurrentDb.Execute "INSERT INTO SerieNr_Label (SerieNr,50003) VALUES (""" & Me.Name & """,""" & vSerial + i & """)", dbFailOnError
    Note that Name is a reserved word and shouldn't be used as a field or object name. Not sure about using a number as a field name but I wouldn't do it.

    If vserial is all numeric then drop some of the quotes
    Code:
    CurrentDb.Execute "INSERT INTO SerieNr_Label (SerieNr,50003) VALUES (""" & Me.Name & """," & vSerial + i & ")", dbFailOnError
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  10. #10
    madpiet is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by Gicu View Post
    In the button's Click event your code would need to get the existing Max serial number for the entered Name then simply do a loop from 1 to Amount and add the record:

    Here is some air code that would achieve that, you will obviously need to adapt it to match your naming convention and data types (if the serial has alphanumeric characters you will need to isolate the numeric part that you want incremented).
    Code:
    Dim i as Integer, vSerial as Variant
    vSerial=dmax("SerialNumber","tblYourTable","NameField='" & Me.Name & "'")
    For i=1 to Me.Amount
        CurrentDB.Execute "INSERT INTO tblYourTable (NameField,SerialNumber) VALUES ('" & Me.Name & "','" & vSerial+i & "'")",dbfailOnError
    Next i
    Cheers,
    loops. YUCK. If you have a tally/numbers table (table of numbers from 1 to whatever max number you need), then you just cross join them and filter the tally table. Then you do ONE insert. No loops. =)

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Until the day you need more than you thought you needed in your tally table...The trusted loop will never let you you....
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    madpiet is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    122
    Quote Originally Posted by Gicu View Post
    Until the day you need more than you thought you needed in your tally table...The trusted loop will never let you you....
    but a tally table with say 1000 records is still TINY.

    Shame you can't create virtual tables in Access. Been the same gimped engine for 25 years. Sheesh.

    Doing this without loops will be a LOT faster.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Only if you're dealing with 1000s plus records or if you use archaic hardware
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Join Date
    Mar 2023
    Posts
    15
    First of all, Thank you all for the answers. It is highly appreciated that you're taking the time to help a n00b out. I'm slowly learning more and more. Unfornately, code is like german to me. I barely understand enough to survive. xD
    I feel like I need to post some pictures of what I have done so far, because I still can't get it to work, and I feel like I'm missing something very obvious. The names are in danish, don't ask me why, but I'll translate.

    My form where I want to put in the number of serial numbers to be generated (SerieNr =Serial Number, Opret = Create):
    Click image for larger version. 

Name:	form.png 
Views:	10 
Size:	10.9 KB 
ID:	49904
    The table (SerieNr_label) with some examples(Varenavn = Product name):
    Click image for larger version. 

Name:	table.png 
Views:	10 
Size:	12.5 KB 
ID:	49905
    The procedure code as of right now:
    Click image for larger version. 

Name:	procedure.png 
Views:	10 
Size:	9.4 KB 
ID:	49906

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Me.Name is the name of the form?
    Criteria should likely be
    Code:
    "Varenavn = '" & "Basic500" & "'"
    However you need to replace Basic500 with whatever holds that value, associated label? etc

    https://stackoverflow.com/questions/...ol-access-2007
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-24-2022, 08:46 AM
  2. Replies: 21
    Last Post: 01-10-2019, 03:21 PM
  3. Replies: 3
    Last Post: 06-08-2017, 08:03 PM
  4. Replies: 25
    Last Post: 06-08-2015, 04:25 PM
  5. Replies: 2
    Last Post: 10-25-2010, 10:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums