RE: Event Procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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
.

Relevant Pages

  • Re: 2007 Access
    ... the number increment starting from a given number say 550000, ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... 'get the current max id in the table Test (autonumber named ID) ...
    (comp.databases.ms-access)
  • RE: Event Procedure
    ... Dim rst as ADODB.Recordset ... Set rst = new ADODB.Recordset ... Dim AutoNumber As Long ... ' db.Execute jetSQL, dbFailOnError ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to get Autonumber key thru code
    ... If you use ADO, the recordset "stays" on the record you just added, so, ... the autonumber is determined as soon as you get the ... > Dim cnn As ADODB.Connection ... > Set rst = New ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: 2007 Access
    ... that I should not use a key autonumber field to create JOB NUMBERS. ... want the order numbers to automatically increment to the next number, ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Need Help | Rolling out new App. | Trouble with some computers.
    ... Dim varReturn As Variant ... Dim lngAuth As Long, strDept As String, strFirst As String, strLast As ... Set rst = db.OpenRecordset ... ' Data file not found in application folder - try to ask the user ...
    (microsoft.public.access.modulesdaovba)