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 = intPIDEnd Sub
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
that is, if your db application is a single user, for multiuser you will used @@identity
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
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?
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Use the IDENTITY keyword in SQL Server.