Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Unanswered: DAO - AddNew PK Value

    I have this code that locks out other users and creates a unique ID incrementing up from the last highest value.

    The code generates the new value as my tblCustomCounter is updated with the new value and the MsgBox displays the right value. However, I can not get that value inserted into the CustomerID field for my Customers table whenever a new record is created.

    I felt this should be part of an Event - Like BeforeUpdate or Dirty but since I can't get it to work I'm open to suggestions to get this going. The code that I added to update the CustomerID field & isn't working is in bold otherwise the rest runs well.

    Private Sub CustomerID_BeforeUpdate()
    Was Public Function Custom_Counter
    On Error GoTo Custom_Counter_Err

    Dim NextAvailableCustomerID As Long
    Dim db As DAO.Database
    Dim BaseData As DAO.Recordset
    Dim tblCounterTable As DAO.Recordset
    Dim qMaxCustID As DAO.Recordset
    Dim Customers As DAO.Recordset

    Const RiErr = 3000
    Const LockErr = 3260
    Const InUseErr = 3262
    Const NumReTries = 20#

    'Variables for the Retry Counts
    Dim NumLocks As Integer
    Dim lngX As Long

    'Variables Used in the Code
    Dim NextAvailableCounter As Long
    Dim lngOldCustomerID As Long
    Dim lngNewCustomerID As Long
    Dim lngBigCustomerID As Long
    Dim CustomerID As Long

    Set db = CurrentDb()
    Set qMaxCustID = db.OpenRecordset("qMaxCustID", dbDenyRead)
    Set BaseData = db.OpenRecordset("Customers")
    Set tblCounterTable = db.OpenRecordset("tblCounterTable", dbDenyRead)
    Set Customers = db.OpenRecordset("Customers", dbAppendOnly)

    lngOldCustomerID = qMaxCustID!CustomerID
    lngNewCustomerID = tblCounterTable!NextAvailableCounter 'C
    lngBigCustomerID = lngNewCustomerID 'Big=C
    If (lngOldCustomerID > lngBigCustomerID) Then
    lngBigCustomerID = lngOldCustomerID
    End If

    If (NextAvailableCounter > lngBigCustomerID) Then
    lngBigCustomerID = NextAvailableCounter
    End If

    'Increment the ID
    lngBigCustomerID = lngBigCustomerID + 1

    'Update the ID Value
    With cmdNewRecord
    .Edit
    !NextAvailableCounter = lngBigCustomerID
    .Update
    End With

    'AddNew NextAvailableCounter value to the CustomerID field
    With Customers
    .AddNew
    !NextAvailableCounter = CustomerID
    .Update

    'Move to the row just added
    .Bookmark = .LastModified


    lngNewCustomerID = lngBigCustomerID
    NormExit:
    Set BaseData = Nothing
    Set db = Nothing

    Exit Sub 'Return

    Custom_Counter_Err:

    'Check For the expected errors
    If ((Err = InUseErr) Or (Err = LockErr) Or (Err = RiErr)) Then

    'If one of the expected ones, increment the counter
    NumLocks = NumLocks + 1

    If (NumLocks < NumReTries) Then
    For lngX = 1 To NumLocks ^ 2 * Int(Rnd * 20 + 5)
    DoEvents
    Next lngX
    Resume Next
    Else
    End If
    Else
    MsgBox "Error" & Err.Number & ": " & Err.Description, _
    vbOKOnly & vbCritical, "Get CustomerID"
    GoTo NormExit
    End If

    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    isn't it just a typo in
    !NextAvailableCounter = CustomerID
    or do you really have a field "nextavailablecounter" in table customers

    maybe you want
    !CustomerID = lngBigCustomerID -1 'or something like that

    ??


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9
    Thanks for the reply Izy,

    Yes that was poor logic to put
    !NextAvailableCounter = CustomerID

    However, I had already tried:
    With Customers
    .AddNew
    !CustomerID = lngBigCustomerID
    .Update
    End With

    Right Now qMaxCustID = 22161. When the sub runs properly lngBigCustomer should also equal 22161.

    That's a new value which I want the CustomerID field in a new record to equal.

    Right now I get Error 3251: Operation Not Supported for this type of object.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and i think you are missing an endwith (Customers).
    ...probably not the cause of your problem, but worth fixing anyhow, and sometimes this sort of stuff throws the wrong error msg.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •