Results 1 to 10 of 10
  1. #1
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26

    Auto Increment ID

    Folks I have another post that technically is similar to one I posted last week, however new development have changed slightly and Im hoping to have this resolved by today.


    ANY And all Help Is Very Appreciated. LOOKING for Code Assistance in solving this

    I have a form page that allows users to duplicate records based on the information that is presented on the form. By pressing a button the current records is duplicated using this code:

    Code:
    Private Sub cmdDupBatch_Click()
        Dim BID As Long
        Dim db As Database
        Dim rsNewBatch As Recordset
        Set db = CurrentDb
        Set rsNewBatch = db.OpenRecordset("Batch Master")
        rsNewBatch.AddNew
            rsNewBatch("Batchnumber") = Me.BatchNumber
            rsNewBatch("productdesc") = Me.ProductDesc
        rsNewBatch.Update
        rsNewBatch.Bookmark = rsNewBatch.LastModified
        BID = rsNewBatch("BatchID")
    
    Me.Requery
    End Sub
    
    Private Sub fraMaster_AfterUpdate()
        If Me.fraMaster.Value = 1 Then
            Me.txtPounds.Visible = False
            Me.BatchNumber = "000000"
        Else
            Me.txtPounds.Visible = True
        End If
    End Sub
    Their is also code that assigns a new BatchNumber to "000000" for the record just copied

    What I need to happen, is when the new record has been copied I need to assign it a unique BatchNumber. The new BatchNumber needs to be generated by doing the following:
    1. Find all records with the same ProductDescription,
    2. identify the MAX BatchNumber of the last copied record
    3. use this value and add it by +1 to get the new BatchNumber for the copied record



    Here is a sample of the table along with some records.
    What code can I use in order to SELECT the Max(BatchNumber) FROM TABLE WHERE ProductDesc = 'Product XYZ'
    Use the value from the results, add the value by 1 and then use it for the newly copied record??

    NOTE: BELOW I HAVE THE INSERT CODE IN SQL. CAN I EXECUTE THIS CODE OR SIMILAR UPDATE QUERY CODE IN VB OR IS THEIR ANOTHER WAY?

    Code:
    DROPTABLE NETGUYKB.Table2;
    CREATETABLE NETGUYKB.Table2 (
    	BatchNumber intNULL,
    	ProductDesc nvarchar(32) NULL
    );
    
    
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000000, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000001, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000002, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000003, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000004, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000005, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000006, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000007, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000008, 'YELLOW DISPERSION')
    INSERTINTO Table2 (BatchNumber, ProductDesc) VALUES (000009, 'YELLOW DISPERSION')
    
    
    
    INSERTINTO Table2 (BatchNumber, ProductDesc)
    SELECT (MAX(BatchNumber)+1), 'YELLOW DISPERSION'
    FROM Table2
    WHERE ProductDesc = 'YELLOW DISPERSION'
    
    SELECT BatchNumber, ProductDesc FROM Table2
    WHERE ProductDesc = 'YELLOW DISPERSION'
    
    

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ln cases like this, you should include a link to your prior post. If I'm right, you left that hanging and didn't respond to what looks like the answer you're still looking for. Not only that, you might benefit from having the original responders weigh in again seeing as how they're likely familiar with the issue.
    Wondering how the current situation is all that different from the last thread. Note - I didn't post the link in case it's the wrong one, plus, it's for you to do.

    EDIT
    If this request really is all that different, you should specify what the table/field names that are involved. Hard to write code to affect that which is unknown. Wondering why you need the 00000 number instead of just getting the next number, but you might have a good reason for that. I'm thinking all you need to do is run Update sql or query against the table that your recordset is based on, but that's just a guess because the things involved are unknown as I said. Or possibly Update your rs field for the batch number at the same time.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    Excellent Suggestions. Sorry I did not include the link from the last post.
    Folks here is the original post. Its messy and I thought by slowing down and re-articulating is would be clearer: https://www.accessforums.net/showthread.php?t=71266

    The last post became so incredibly long and I felt like I elaborated and made mistakes when making subsequent posts clarifying.

    The situation is slightly different given I was able to clean the data up so that incrementing by one (1) would not fail given that most of the BatchNUmbers were not numbers.
    I guess based on that alone the request is still the same but was hoping this made more sense.

    Technically the Table in the DB is Named "Batch Master" The Example Scripts that I passed along I named it Table2. Lets call it Batch Master
    In terms of "Wondering why you need the 00000 number " This was used as a way that if the record being copied was what we refer to as a "Master" record then we thought it would be nice for user to not have to type it in. We've now made the decision that No More Master will be created via the forms presented to the users and that all NEW Master would be created on the back end.

    I again am imaging that as part of the script above that copies the current record as a new record, there must be a way to run SQL that updates the BatchNumber based in getting the MAX BatchNumber WHERE ProductDesc = Me.ProductDesc I.E. The Value from the form field

    You Indicated: "I'm thinking all you need to do is run Update sql or query against the table that your recordset is based on, but that's just a guess because the things involved are unknown as I said. Or possibly Update your rs field for the batch number at the same time."

    I would agree, but im not 100% sure on how to code that into the existing VB code.

    We would use the ID That Access creates for each new record "BatchID". SO after the record is copied we grab the new ID
    Set Variable from the previous rs
    BID = rsNewBatch("BatchID")


    Use this BID to support a second query for updating the BatchNumber
    strSql = "SELECT BatchID, MAX(BatchNumber)+1 FROM [Batch Master] WHERE BatchID" & "=" & BID OR Alternatively use Me.BatchID (the form value)
    Set rs = db.OpenRecordset(strSql)

    Now update the table
    strSql2 = "UPDATE [Batch Master] SET BatchNumber = "& rs![Batchnumber] &" WHERE BatchID" & "=" & rs![Batchnumber]
    Set rsNEW = db.OpenRecordset(strSql)
    rsNew.Update

    Not sure if there is code to actually EXECUTE the above after we open the query, I thought it was .Update

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Since you are already controlling the creation of the new record to begin with, why not assign the new BatchNumber right then? I don't see the logic in creating the record, then going and searching for that record, just so you can modify a value in the record you just created. Example (air code);

    Code:
    Dim rs As Recordset
    Dim strNewBatchNumber As String   'this assumes your BatchNumber field is a text data type
    
    strNewBatchNumber  = Nz(DMAX("BatchNumber", "[Batch Master]", "BatchDesc=""" & Me!BatchDesc & """") + 1, "00000")
    
    With CurrentDb
        Set rs = .OpenRecodset("Batch Master")
        With rs
            .AddNew
            !BatchDesc = Me!BatchDesc
            !BatchNumber = strNewBatchNumber
            .Update
        End With
    End With
    Other observations;
    • Your data appears to lack normalization. Having said that, I realize that there are situations when you might have to deal with a denormalized set of data because it comes from another system, or for whatever reason you don't have complete control of the structure. If you do have control of the structure in this application, then you might want to look into normalization at some point.
    • You should avoid using spaces or special characters in object naming (i.e. table names, field names, etc). It just creates more work for you when you need to reference those objects in another part of your application (again, maybe you don't have control of the table/field naming).

  5. #5
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    WELL, Wasn't that easy it worked liked a charm. Thanks for all your help. Unfortunately I am not the creator of this DB. There are many flaws in it and Im just someone helping them out with making some changes. Eventually I am going to end up proposing they rebuilt. Its taken me many hours to do cleanup on the data before I could implement any changes. CRAZY but certainly an opportunity to improve.

  6. #6
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    Hey there Beetle I have one more question around the final Code that was created BUT let me first briefly describe what it does:

    When a button is pressed on a form page the current record gets copied as a new record. Because the form page also contains a SUB-Form with related records, there is a query ran to also COPY the contents from the current sub form to the newly created master record. There is a Strung Variable that captures the New BatchID from the parent record so that It can be used for associating all sub records. Hope That Makes Sence

    At the very end of the script we perform a Me.Requery. The process runs quickly and sends the user back to the very first record in the DB (Obviously).
    I'd like to add a Bookmark as part of the Me.Requery (I Assume this is where I would add the code) to take the user to the new record that was duplicated.

    Any Help Would Be Appreciated.

    I have tried a few things Including replacing Me.Requery with - DoCmd.GoToRecord acDataForm, "Batches", acGoTo, BID

    BID was the Id from the new Copied record found in the script

    Heres the Full Code

    Code:
    Private Sub cmdDupBatch_Click()    Dim BID As Long
        Dim strSql As String
        Dim db As Database
        Dim rs, newrs As Recordset
        Dim rsNewBatch As Recordset
        Dim rc, looper As Integer
        Dim strNewBatchNumber As String
        'Lets increment ID by one based on the max Batch Number
        strNewBatchNumber = Nz(DMax("BatchNumber", "[Batch Master]", "ProductDesc=""" & Me!ProductDesc & """") + 1, "00000")
        Set db = CurrentDb
        Set rsNewBatch = db.OpenRecordset("Batch Master")
        rsNewBatch.AddNew
            rsNewBatch("ProductCategory") = Me.cboCategory
            rsNewBatch("Batchnumber") = strNewBatchNumber
            rsNewBatch("productdesc") = Me.ProductDesc
            rsNewBatch("CodeNumber") = Me.CodeNumber
            rsNewBatch("customer_number") = Me.cboCus
            rsNewBatch("specialinstructions") = Me.SpecialInstructions
            rsNewBatch("viscometer") = Me.ZahnCupUsed
            rsNewBatch("totlbsinbatch") = Me.txtPounds
            rsNewBatch("weightpergallon") = Me.WeightPerGallon
            rsNewBatch("master") = 2
        rsNewBatch.Update
        rsNewBatch.Bookmark = rsNewBatch.LastModified
        BID = rsNewBatch("BatchID")
        'UPDATE INGREDIANTS TABLE
        strSql = "SELECT * FROM [Batch Ingredients] WHERE BatchID" & "=" & Me.BatchID
        Set rs = db.OpenRecordset(strSql)
        Set newrs = db.OpenRecordset("Batch Ingredients")
        rs.MoveFirst
        While Not rs.EOF
            newrs.AddNew
                newrs![BatchID] = BID
                newrs![RawMaterial] = rs![RawMaterial]
                newrs![Percentof100] = rs![Percentof100]
                newrs![PoundsReqInBatch] = rs![PoundsReqInBatch]
                newrs![ActualAddition] = rs![ActualAddition]
                newrs![NewPercent] = rs![NewPercent]
                newrs![MixOrder] = rs![MixOrder]
                newrs![ExtendedCostofProduct] = rs![ExtendedCostofProduct]
            newrs.Update
            rs.MoveNext
        Wend
        
    Me.Requery
    
    
    
    
    End Sub

  7. #7
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    You have already captured the Batch ID of the newly created record in your BID variable, so after the requery just search the RecordsetClone for that ID and use the bookmark property to move the cursor there (added code in red, untested air code)

    Code:
                        Private Sub cmdDupBatch_Click()    Dim BID As Long
    
    • Dim strSql As String Dim db As Database Dim rs, newrs As Recordset Dim rsNewBatch As Recordset Dim rc, looper As Integer Dim strNewBatchNumber As String 'Lets increment ID by one based on the max Batch Number strNewBatchNumber = Nz(DMax("BatchNumber", "[Batch Master]", "ProductDesc=""" & Me!ProductDesc & """") + 1, "00000") Set db = CurrentDb Set rsNewBatch = db.OpenRecordset("Batch Master") rsNewBatch.AddNew rsNewBatch("ProductCategory") = Me.cboCategory rsNewBatch("Batchnumber") = strNewBatchNumber rsNewBatch("productdesc") = Me.ProductDesc rsNewBatch("CodeNumber") = Me.CodeNumber rsNewBatch("customer_number") = Me.cboCus rsNewBatch("specialinstructions") = Me.SpecialInstructions rsNewBatch("viscometer") = Me.ZahnCupUsed rsNewBatch("totlbsinbatch") = Me.txtPounds rsNewBatch("weightpergallon") = Me.WeightPerGallon rsNewBatch("master") = 2 rsNewBatch.Update rsNewBatch.Bookmark = rsNewBatch.LastModified BID = rsNewBatch("BatchID") 'UPDATE INGREDIANTS TABLE strSql = "SELECT * FROM [Batch Ingredients] WHERE BatchID" & "=" & Me.BatchID Set rs = db.OpenRecordset(strSql) Set newrs = db.OpenRecordset("Batch Ingredients") rs.MoveFirst While Not rs.EOF newrs.AddNew newrs![BatchID] = BID newrs![RawMaterial] = rs![RawMaterial] newrs![Percentof100] = rs![Percentof100] newrs![PoundsReqInBatch] = rs![PoundsReqInBatch] newrs![ActualAddition] = rs![ActualAddition] newrs![NewPercent] = rs![NewPercent] newrs![MixOrder] = rs![MixOrder] newrs![ExtendedCostofProduct] = rs![ExtendedCostofProduct] newrs.Update rs.MoveNext Wend Me.Requery With Me.RecordsetClone .FindFirst "BatchID=" & BID If Not .NoMatch Then Me.Bookmark = .Bookmark End WIth End Sub
    Other observations about your code, in case you are not aware;

    This;
    Dim rs, newrs As Recordset

    does not declare both of these variables as Recordsets. It declares rs as a Variant and newrs as a Recordset. You can declare variables on the same line separated by commas, but you still need to explicitly declare each one (unless a variant data type is what you want);

    Dim rs As Recordset, newrs As Recordset

    Also, you could consolidate your code and save yourself a lot of keystrokes if you learn to use With blocks

  8. #8
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    Thanks for the tips, very good to know. I did make changes based on your suggestions.

    I also tried the code in red and it works flawlessly.

    Thanks again!!

  9. #9
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    LOL, SO WEIRD It worked one time when I made all code changes and NOW its not. So weird. No error just simply stays on the original record. I swear though it worked the first time

  10. #10
    KBNETGUY is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    26
    Oppps Looks like i forgot the Me.Requery

    Fixed

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-29-2016, 11:42 AM
  2. Auto increment Letter
    By andy-29 in forum Access
    Replies: 4
    Last Post: 11-08-2012, 05:23 PM
  3. Auto Increment Reset
    By Sibtain in forum Programming
    Replies: 3
    Last Post: 10-25-2012, 01:59 AM
  4. Replies: 5
    Last Post: 11-12-2010, 12:10 PM
  5. Auto Increment by 1 Letter
    By Cinquefoil22 in forum Database Design
    Replies: 4
    Last Post: 07-02-2010, 10:35 AM

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