asked on

Creating a new ID in VBA

Hello,

I'm trying to create a new ID for any additional records we add into our database. I started out by creating an entry form that (all unbound text boxes) with 2 buttons one which I will append the fields to the table and one to fetch the current max id +1 for this record. the ID button is not working and I've tried different variations. We have 2 tables one which permanent and is a SQL table (linked to MS SQL) and the other a temprary table, both of which have IDs. I'm trying to pull that max number ID and 1 to it and assign it to a variable where I can send the value to a text box on the form. below is my code, what am I doing wrong?

Private Sub cmdGetNewID_Click()
'Get new ID for respondent
 
    Dim intPID As Integer
    Dim db As Database
    Dim rst As Recordset
    
    Set db = CurrentDb()
    
    'check which contact type it will be
    'If radio button 1 is selected then Phys table (Permanent)
    'If radio button 2 is selected then Temp table (Temprary)
    Select Case Me.frmContactType.Value
        Case 1
            Set rst = CurrentDb.OpenRecordset("SELECT Max(tblPhysContactInfo.ID)+1 AS [NewPID] FROM tblPhysContactInfo;")
            If Not rst.EOF Then
                rst.MoveFirst
                intPID = rst![NewPID]
            End If
        Case 2
            Set rst = CurrentDb.OpenRecordset("SELECT IIf(Max([tblTempContactInfo].[ID])+1=1,'2000001',Max([tblTempContactInfo].[ID])+1) AS [NewPID] FROM tblTempContactInfo;")
            If Not rst.EOF Then
                rst.MoveFirst
                intPID = rst![NewPID]
            End If
    End Select
    
    Me.txtPID.Value = intPID
End Sub

Open in new window

MikeToole

Why not set the ID column your tables to automatically increment?
Use the IDENTITY keyword in SQL Server.

Rey Obrero (Capricorn1)

try this

Private Sub cmdGetNewID_Click()
'Get new ID for respondent

Dim intPID As Integer
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()

'check which contact type it will be
'If radio button 1 is selected then Phys table (Permanent)
'If radio button 2 is selected then Temp table (Temprary)
Select Case Me.frmContactType.Value
Case 1
' Set rst = CurrentDb.OpenRecordset("SELECT Max(tblPhysContactInfo.ID)+1 AS [NewPID] FROM tblPhysContactInfo;")
' If Not rst.EOF Then
' rst.MoveFirst
' intPID = rst![NewPID]
' End If

intPID = Nz(DMax("ID", "tblPhysContactInfo"), 0) + 1

Case 2
' Set rst = CurrentDb.OpenRecordset("SELECT IIf(Max([tblTempContactInfo].[ID])+1=1,'2000001',Max([tblTempContactInfo].[ID])+1) AS [NewPID] FROM tblTempContactInfo;")
' If Not rst.EOF Then
' rst.MoveFirst
' intPID = rst![NewPID]
' End If

intPID = Nz(DMax("ID", "tblTempContactInfo"), 0) + 1

End Select

Me.txtPID.Value = intPID
End Sub

Rey Obrero (Capricorn1)

that is, if your db application is a single user, for multiuser you will used @@identity

AIdoHSG

ASKER

Thanks capricorn1, but when I clicked the button it gave me a Run-time error '6': Overflow
and it takes me to line 21 or 31 based on my selection.
Any ideas?

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

AIdoHSG

ASKER

Thanks capricorn1, this is excatly what I was looking.