Auto Generate ID Based on a Query

[KBNETGUY]

Hey Folks, brand new to this forum, Please be kind. LOL

I have a form with a button on it the when pressed takes the data from the fields of the form and automatically generates a NEW record. for example:

Record 1
UNIQUE ID Field: 000001 (auto generated)
Field 1 (first name): Bob
Field 2 (last name): Barker
Field 3 (id): 1

When the DUPLICATE button is pressed the following occurs:

Record 2
UNIQUE_ID Field: 000002 (auto generated)
Field 1 (first name): Bob
Field 2 (last name): Barker
Field 3 (id): 1

What I would like to happen is that when the DUPLICATE button is pressed i get the following

Record 2
UNIQUE_ID Field: 000002 (auto generated)
Field 1 (first name): Bob
Field 2 (last name): Barker
Field 3 (id): 2

So essentially 3 things need to happen
1) New record is generated based on duplicating the existing record
2) A variable needs to be created to be used in an update query which auto increments field 3 by a value of 1
for example: SET NEW_ID = (SELECT id FROM myTABLE WHERE UNIQUE_ID = MyFormValue) +1
3) an update query will then run to update field 3 based on step two above

Here is the current code that duplicates the records using all of the current record data WITHOUT incrementing the id by 1.

NOTE: Me.BatchNumber is the number we'd like incremented by 1. labeled as Field 3 (id) from my above example
NOTE: Me.BatchID is the UNQUIE_ID that gets created when the record is duplicated
NOTE: There is some code following rsNewBatch_Update which is being used for adding additional data associated in a SUB-FORM on the form page. I would suspect that the new code for what Im trying to do would happen just before this code.

Private Sub cmdDupBatch_Click()    Dim BID As Long
    
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    'DoCmd.DoMenuItem
    Dim strsql As String
    Dim db As Database
    Dim rs, newrs As Recordset
    Dim rsNewBatch As Recordset
    Dim rc, looper As Integer
    Set db = CurrentDb
    Set rsNewBatch = db.OpenRecordset("Batch Master")
    rsNewBatch.AddNew
        rsNewBatch("ProductCategory") = Me.cboCategory
        rsNewBatch("Batchnumber") = Me.BatchNumber
        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("costperpound")=me
        rsNewBatch("master") = 2
    rsNewBatch.Update
    rsNewBatch.Bookmark = rsNewBatch.LastModified
    BID = rsNewBatch("BatchID")
    strsql = "SELECT * FROM [Batch Ingredients] WHERE BatchID" & "=" & Me.BatchID
    Set rs = db.OpenRecordset(strsql)
    'rs.MoveFirst
    'rs.MoveLast
    'rc = rs.RecordCount
    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

[June7]

What do I not understand - why are there 2 fields with the same data: UniqueID and ID?

The actual field names are BatchNumber and BatchID? Why confuse us with dummy names? I don't see [first name] and [last name] in the code.

So why don't you increment the ID? rsNewBatch = Me.BatchID + 1

Or if you want to allow multiple new records, something like:

rsNewBatch = Nz(DMax("BatchID", "tablename", "BatchNumber=" & Me.BatchNumber), 0) + 1

[orange]

@kbnetguy

Is this some academic pursuit or is there a business rationale behind this?
Could you tell us a little about the requirement?

[KBNETGUY]

You are correct the examples I used are not the same as what you may see in the code as I was trying to over simplify this.

With that said, there is in fact a Unique ID (BatchID), The end user NEVER sees this ID. What they do see is what we call a BatchNumber.

Here is the business scenario.

There is a table in the DB Called "Batch Master" this table is used for creating paint batch formulas. Some of the records in this table are what we call MASTERS. Meaning that when we create a Batch of paint, the master record for a particular batch is the one we start from (duplicate from). We then make duplicate records from this master record but will tweek certain things about it. A user will search for the master record, once they find it they press the Duplicate button on the form which runs the code I sent in my initial post.

Currently the code simply takes what value in the field called rsNewBatch("Batchnumber") = Me.BatchNumber and duplicates it to the new record. The end users has to go to the record and increment it by one manually. For example there could be 10 records in this table for one particular batch called "021 ORANGE CV INK". that look like this:

BatchID | BatchDesc | BatchNumber | Master
1 | 021 ORANGE CV INK | 00000 | Yes
2 | 021 ORANGE CV INK | 00001 |No
3 | 021 ORANGE CV INK | 00002 | No
4 | 021 ORANGE CV INK | 00004 |No
5 | 021 ORANGE CV INK | 00005 |No
6 | 021 ORANGE CV INK | 00006 | No
7 | 021 ORANGE CV INK | 00007 | No
8 | 021 ORANGE CV INK | 00008 | No
9 | 021 ORANGE CV INK | 00009 | No
10 | 021 ORANGE CV INK | 00010 | No

After hitting the duplicate button on the master record it duplicates the master record (Batch Number - 000000). NOTE: ALL NEW BATCHES MUST START FROM A DUPLICATED MASTER RECORD
11 | 021 ORANGE CV INK | 00000 | No

The user then looks up what the last BatchNumber based on the master they've duplicated and manually updates their new record to look like this:
11 | 021 ORANGE CV INK | 00011 | No

Notice that the next record BatchNumber is manually changed to 00011

I would like it so that a query is ran within the code that finds the MAX value Where BatchDesc = the current record and then dynamically assign a new BatchNumber incremented by one for the newly created record.

For Example:
SET strMaxBatchNumber = (Select MAX(BatchDesc) FROM Batch Master WHERE BatachDesc = Me.BatchNumber +1)
NOTE: This is not the correct code however I need the query to assign the new BatchNumber Dynamically based on the Max Value from the last record created for this particular MASTER record.

[KBNETGUY]

You are correct the examples I used are not the same as what you may see in the code as I was trying to over simplify this.

With that said, there is in fact a Unique ID (BatchID), The end user NEVER sees this ID. What they do see is what we call a BatchNumber.

Here is the business scenario.

There is a table in the DB Called "Batch Master" this table is used for creating paint batch formulas. Some of the records in this table are what we call MASTERS. Meaning that when we create a Batch of paint, the master record for a particular batch is the one we start from (duplicate from). We then make duplicate records from this master record but will tweek certain things about it. A user will search for the master record, once they find it they press the Duplicate button on the form which runs the code I sent in my initial post.

Currently the code simply takes what value in the field called rsNewBatch("Batchnumber") = Me.BatchNumber and duplicates it to the new record. The end users has to go to the record and increment it by one manually. For example there could be 10 records in this table for one particular batch called "021 ORANGE CV INK". that look like this:

BatchID | BatchDesc | BatchNumber | Master
1 | 021 ORANGE CV INK | 00000 | Yes
2 | 021 ORANGE CV INK | 00001 |No
3 | 021 ORANGE CV INK | 00002 | No
4 | 021 ORANGE CV INK | 00004 |No
5 | 021 ORANGE CV INK | 00005 |No
6 | 021 ORANGE CV INK | 00006 | No
7 | 021 ORANGE CV INK | 00007 | No
8 | 021 ORANGE CV INK | 00008 | No
9 | 021 ORANGE CV INK | 00009 | No
10 | 021 ORANGE CV INK | 00010 | No

After hitting the duplicate button on the master record it duplicates the master record (Batch Number - 000000). NOTE: ALL NEW BATCHES MUST START FROM A DUPLICATED MASTER RECORD
11 | 021 ORANGE CV INK | 00000 | No

The user then looks up what the last BatchNumber based on the master they've duplicated and manually updates their new record to look like this:
11 | 021 ORANGE CV INK | 00011 | No

Notice that the next record BatchNumber is manually changed to 00011

I would like it so that a query is ran within the code that finds the MAX value Where BatchDesc = the current record and then dynamically assign a new BatchNumber incremented by one for the newly created record.

For Example:
SET strMaxBatchNumber = (Select MAX(BatchDesc) FROM Batch Master WHERE BatachDesc = Me.BatchNumber +1)
NOTE: This is not the correct code however I need the query to assign the new BatchNumber Dynamically based on the Max Value from the last record created for this particular MASTER record.

[KBNETGUY]

Just noticed a mistake in declaring what I was looking for help with. Can anyone help me with this?

Here is the business scenario.

There is a table in the DB Called "Batch Master" this table is used for creating paint batch formulas. Some of the records in this table are what we call MASTERS. Meaning that when we create a Batch of paint, the master record for a particular batch is the one we start from (duplicate from). We then make duplicate records from this master record but will tweek certain things about it. A user will search for the master record, once they find it they press the Duplicate button on the form which runs the following Code:

Private Sub cmdDupBatch_Click()    Dim BID As Long
    
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    'DoCmd.DoMenuItem
    Dim strsql As String
    Dim db As Database
    Dim rs, newrs As Recordset
    Dim rsNewBatch As Recordset
    Dim rc, looper As Integer
    Set db = CurrentDb
    Set rsNewBatch = db.OpenRecordset("Batch Master")
    rsNewBatch.AddNew
        rsNewBatch("ProductCategory") = Me.cboCategory
        rsNewBatch("Batchnumber") = Me.BatchNumber
        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("costperpound")=me
        rsNewBatch("master") = 2
    rsNewBatch.Update
    rsNewBatch.Bookmark = rsNewBatch.LastModified
    


End Sub

Currently the code simply takes what value in the field called rsNewBatch("BatchNumber") = Me.BatchNumber and duplicates it to the new record. The end users has to go to the record and increment the BatchNumber by one based on the MAX BatchNumber manually. For example there could be 10 records in this table for one particular batch called "021 ORANGE CV INK". that look like this:

BatchID | BatchDesc | BatchNumber | Master
1 | 021 ORANGE CV INK | 00000 | Yes
2 | 021 ORANGE CV INK | 00001 |No
3 | 021 ORANGE CV INK | 00002 | No
4 | 021 ORANGE CV INK | 00004 |No
5 | 021 ORANGE CV INK | 00005 |No
6 | 021 ORANGE CV INK | 00006 | No
7 | 021 ORANGE CV INK | 00007 | No
8 | 021 ORANGE CV INK | 00008 | No
9 | 021 ORANGE CV INK | 00009 | No
10 | 021 ORANGE CV INK | 00010 | No

After hitting the duplicate button on the master record it duplicates the master record (BatchNumber - 000000).
NOTE: ALL NEW BATCHES MUST START FROM A DUPLICATED MASTER RECORD
11 | 021 ORANGE CV INK | 00000 | No

The user then looks up what the last BatchNumber (i.e. MAX BatchNumber, in this case its 00010) based on the master they've duplicated and manually updates their new record to look like this:
11 | 021 ORANGE CV INK | 00011 | No

Notice that the next record BatchNumber is manually changed to 00011

I would like it so that a query is ran within the code that finds the MAX BatchNumber value Where BatchDesc = the current record and then dynamically assign a new BatchNumber incremented by one for the newly created record.

For Example:
SET strMaxBatchNumber = (Select MAX(BatchNumber) FROM Batch Master WHERE BatachDesc = Me.BatchNumber +1)
NOTE: This is not the correct code however I need the query to assign the new BatchNumber Dynamically based on the Max Value from the last record created.

[June7]

Can't use a SELECT statement like that but can use a DMax() domain aggregate function. Suggest you do some research on them.