Programaticly implementing an autonumber when duplicating a record

Posted on 2012-08-31
Last Modified: 2012-09-01
I have a form that duplicates a record. Code is posted below.

The problem I am having is with the autonumber. I would like to increment it whenever I create a record. With this code I am getting an error. Index or Primary Key Can Not Contain a Null Value. Anyone know what I need to do?

Private Sub CmdSaveProdAsNew_Click()
Call AllowChanges

'Disables the lookup and sets the cmdSaveToolAsNew command button to bold
'and to red. Also, this hides all the other default commands
Me.cboProductsLookUp.Locked = True
Me.CmdSaveProdAsNew.ForeColor = RGB(255, 255, 255)
Me.CmdSaveProdAsNew.BackColor = RGB(192, 80, 77)
Me.CmdSaveProdAsNew.FontBold = True

'Hides the other commands
Me.CmdAddNewProduct.Visible = False
Me.cmdDeleteProduct.Visible = False
Me.CmdEditCurrentProduct.Visible = False

Call ShowEditCommands

'Sets the prodnumber and description text to red
Me.txtProdPartNo.ForeColor = RGB(242, 13, 13)
Me.txtProdItem.ForeColor = RGB(242, 13, 13)

'Copies all data from within the current record and places it in a new record
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![ProdPartNo]

' Add new record to end of Recordset object.
With rst
    !ProdPartNo = Me!txtProdPartNo & "?"
    !ProdItem = Me!txtProdItem
    !ProdFamily = Me!txtProdFamily
    !ProdType = Me!txtProdType
    !ProdTypeSub1 = Me!ProdTypeSub1
    !ProdTypeSub2 = Me
    !ProdQtyOnHand = Me
    !T1 = Me!txtT1
    !T2 = Me!txtT2
    !T3 = Me!txtT3
    !P1 = Me!txtP1
    !P2 = Me!txtP2
    !P3 = Me!txtP3
    !ProdSearch1 = Me!txtProdSearch1
    !ProdSearch2 = Me!txtProdSearch2
    !ProdSearch3 = Me!txtProdSearch3
    !ShankDia = Me!txtShankDia
    !Size = Me!txtSize
    !ProdLength = Me!txtProdLength
    !ProdWidth = Me!txtProdWidth
    !ProdHeight = Me!txtProdHeight
    !ProdWeight = Me!txtProdWeight
    !ProdIsActive = Me!chkProdIsActive
    !ProductSaleIsActive = Me!chkSaleIsActive
    !ProdPrice = Me!txtProdPrice
    !ProdSalePrice = Me!txtProdSalePrice
    !ToolOAL = Me!txtToolOAL
    !ToolTip = Me!txtToolTip
    !ToolCoated = Me!txtToolCoated
    !ToolFlutes = Me!txtToolFlutes
    !ToolDOC = Me!txtToolDOC
    !ToolNeck = Me!txtToolNeck
    !ToolExt = Me!txtToolExt
   .Update                     ' Save changes.
   .Move 0, .LastModified
End With

Me.Bookmark = rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

DoCmd.SetWarnings False
'DoCmd.OpenQuery "Duplicate Order Details"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
DoCmd.GoToControl ("txtProdPartNo")

Exit Sub

MsgBox Error$
Resume Exit_btnduplicate_Click:

Open in new window

Question by:jleval
    LVL 29

    Accepted Solution

    What field or fields are you using for your primary key?  I'm guessing it's not listed in your rst.AddNew method.

    Author Comment

    The Field I am using for a primary key is ProdNumberID
    LVL 29

    Expert Comment

    ProdNumberID is not the autonumber field, right?  If it's not, you need to assign it a value when you do your recordset AddNew update.

    Author Comment

    It is an autonumber

    Author Comment

    Oh, never mind someone sent me a table that didn't have the prod key set as an autonumber, my bad. Enjoy the points

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now