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:
- Find all records with the same ProductDescription,
- identify the MAX BatchNumber of the last copied record
- 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'