Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

Programaticly implementing an autonumber when duplicating a record

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

  • 3
  • 2
1 Solution
What field or fields are you using for your primary key?  I'm guessing it's not listed in your rst.AddNew method.
jlevalAuthor Commented:
The Field I am using for a primary key is ProdNumberID
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.
jlevalAuthor Commented:
It is an autonumber
jlevalAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now