Experts Exchange connects you with the people and services you need so you can get back to work.

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

AIdoHSG

Asked: 2008-06-17

Advertise Here

1 Solution

LVL 27

MikeTooleCommented: 2008-06-17

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

LVL 120

Rey Obrero (Capricorn1)Commented: 2008-06-17

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

LVL 120

Rey Obrero (Capricorn1)Commented: 2008-06-17

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

AIdoHSGAuthor Commented: 2008-06-17

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?