Event Procedure

[Zanstemic]

I'm having difficulty getting the following code to run. AutoCaseNumber is
the Control that has the value I need to save. It increments correctly when
opening however this is a multi user environment so if two forms open at the
same time, the numbers are the same. I'm trying to get AutoCaseNumber to add
one if the number already exists when saving.

The error is "validation rule violation"
If I setup a test where the number exists it does not seem to do the
incrementation.

Private Sub Save_Data_Click()
Dim AutoNumber As Long
Dim jetSQL

AutoNumber = Me!AutoCaseNumber

' pick a number to start at

Do While True
jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber,
CaseNumber) VALUES (Forms![Create_Claim]![Registration Date],
Forms![Create_Claim]![txtRegistration Case Number],
Forms![Create_Claim]![AutoCaseNumber]);"
' db.Execute jetSQL, dbFailOnError
DoCmd.RunSQL jetSQL

If Err.Number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
AutoNumber = AutoNumber + 1
Me![AutoCaseNumber] = AutoNumber

Else
' okay it worked, this number now belongs to us
Exit Do
End If
Loop

' return myNumber to the calling procedure
End Sub

 

[Leo]

I have always found ADO easier to add records than Action Queries in Access.
Here is the syntax.

Hope this helps.
Leo

Dim rst as ADODB.Recordset
Set rst = new ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from EmployeeTable; "'
.AddNew
!FirstName = Me.FirstName
!LastName = Me.LastName
!Manager = Me.Manager
.Update

End With
me.ID = rst!ID

rst.Close
Set rst = Nothing

 

[Zanstemic]

Thanks for the guidance. It looks straight forward but I'm not sure how to
edit the following:

me.ID = rst!ID

It's coming back with a data object me.ID not found.

I have always found ADO easier to add records than Action Queries in Access.
Here is the syntax.

Hope this helps.
Leo

Dim rst as ADODB.Recordset
Set rst = new ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockPessimistic
.Open "Select * from EmployeeTable; "'
.AddNew
!FirstName = Me.FirstName
!LastName = Me.LastName
!Manager = Me.Manager
.Update

End With
me.ID = rst!ID

rst.Close
Set rst = Nothing

I'm having difficulty getting the following code to run. AutoCaseNumber is
the Control that has the value I need to save. It increments correctly when
opening however this is a multi user environment so if two forms open at the
same time, the numbers are the same. I'm trying to get AutoCaseNumber to add
one if the number already exists when saving.

The error is "validation rule violation"
If I setup a test where the number exists it does not seem to do the
incrementation.

Private Sub Save_Data_Click()
Dim AutoNumber As Long
Dim jetSQL

AutoNumber = Me!AutoCaseNumber

' pick a number to start at

Do While True
jetSQL = "INSERT INTO Attendees ([Date Received],CPSAutoNumber,
CaseNumber) VALUES (Forms![Create_Claim]![Registration Date],
Forms![Create_Claim]![txtRegistration Case Number],
Forms![Create_Claim]![AutoCaseNumber]);"
' db.Execute jetSQL, dbFailOnError
DoCmd.RunSQL jetSQL

If Err.Number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
AutoNumber = AutoNumber + 1
Me![AutoCaseNumber] = AutoNumber

Else
' okay it worked, this number now belongs to us
Exit Do
End If
Loop

' return myNumber to the calling procedure
End Sub