Results 1 to 11 of 11
  1. #1
    ckulow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8

    VBA Novice needs help changing code

    Hello – After splitting db into front and backend, I have the Public Function below in frontend that creates a new counter in table tblFlexAutoNum which is located in the backend. I am using linked table manager to link to this table from the frontend. The BeforeUpdate event for my form inserts the new counter into the form – code also below. Issue: get “could not get a counter” message. I need help to change the function and/or BeforeUpdate event for the form. Thank you!

    Code:
    Public Function acbGetCounter() As Long
       'Get a value from the counters table and increment it
    
       Dim db As DAO.Database
       Dim rst As DAO.Recordset
       Dim blnLocked As Boolean
       Dim intRetries As Integer
       Dim lngTime As Long
       Dim lngCnt As Long
       Dim lngCounter As Long
    
       'set number of retries
       Const conMaxRetries = 5
       Const conMinDelay = 1
       Const conMaxDelay = 10
    
       On Error GoTo HandleErr
    
       Set db = CurrentDb()
       blnLocked = False
    
       Do While True
          For intRetries = 0 To conMaxRetries
             On Error Resume Next
             Set rst = db.OpenRecordset("tblFlexAutoNum", _
                dbOpenTable, dbDenyWrite + dbDenyRead)
             If Err.Number = 0 Then
                blnLocked = True
                Exit For
             Else
                lngTime = intRetries ^ 2 * _
                   Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
                For lngCnt = 1 To lngTime
                   DoEvents
                Next lngCnt
             End If
          Next intRetries
          On Error GoTo HandleErr
    
          If Not blnLocked Then
             If MsgBox("Could not get a counter: Try again?", _
                vbQuestion + vbYesNo) = vbYes Then
                intRetries = 0
             Else
                Exit Do
             End If
          Else
             Exit Do
          End If
       Loop
    
       If blnLocked Then
          lngCounter = rst("CounterValue")
          acbGetCounter = lngCounter
          rst.Edit
          rst("CounterValue") = lngCounter + 1
          rst.Update
          rst.Close
       Else
          acbGetCounter = -1
       End If
       Set rst = Nothing
       Set db = Nothing
    
    ExitHere:
       Exit Function
    
    HandleErr:
       MsgBox Err.Number & ": " & Err.Description, , "acbGetCounter"
       Resume ExitHere
    
    End Function

    BeforeUpdate event for form inserts the new counter into the form:
    Private Sub Form_BeforeUpdate(Cancel As Integer)


    'Try to get a unique counter and write it to the CourseID field

    Code:
       Dim lngCounter As Long
    
       If IsNull(Me.txtCourseID) Then
          lngCounter = acbGetCounter()
          'If no counter is available...
          If lngCounter < 1 Then
             'cancel the update event.
             Cancel = True
          Else
             'write the key field.
             Me.txtCourseID = lngCounter
          End If
       End If
    Last edited by RuralGuy; 07-17-2017 at 11:38 AM. Reason: Added Code tags and indenting

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this for starters:

    Set rst = db.OpenRecordset("tblFlexAutoNum", dbOpenDynaset)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To add to Paul's answer, see https://access-programmers.co.uk/for...ad.php?t=79351 Post #2.

    From https://msdn.microsoft.com/en-us/lib.../ff197799.aspx
    "When creating a Recordset object using a non-linked TableDef object in a Microsoft Access workspace, table-type Recordset objects are created.
    Only dynaset-type or snapshot-type Recordset objects can be created with linked tables or tables in Microsoft Access database engine-connected ODBC databases."

  4. #4
    ckulow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8

    Thank you both!



    Thank you both!! This worked!!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ckulow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    Hello Paul, Steve - have a similar VBA issue today - code below throws an error after back-end tables were migrated to SQL server and tblUser changed from a local table to a linked table. I looked at similar threads but am not comfortable enough with VBA to make the appropriate changes - can you help? Thank you!

    Public Function FullName() As String
    'Looks for the login for the current machine and looks to see if it is found on tblUser
    'if it is found, then returns the contents of field !FullName else, errors
    With CurrentDb.OpenRecordset("tblUser")
    .Index = "PrimaryKey"
    .Seek "=", statUser
    If Not .NoMatch Then
    FullName = !FullName
    Else
    MsgBox "User not found. Please re-check your user table.", vbInformation, "Missing Data"
    End If
    End With
    End Function

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Some things, like Seek, don't work with linked tables. Much more efficient anyway to open your recordset on the desired record:

    CurrentDb.OpenRecordset("SELECT * FROM tblUser WHERE FieldName = '" & statUser & "'")

    and change your test to:

    If Not .EOF Then

    delete the single quotes if the field isn't text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ckulow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    Thanks for quick reply!! I get compile error - invalid or unqualified reference on If Not .EOF Then

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it within your With block? Normally it would be:

    If Not rs.EOF Then

    I don't personally do it the way you have it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    ckulow is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    8
    I figured it out - thank you very much!

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-23-2017, 08:07 AM
  2. Replies: 8
    Last Post: 12-16-2016, 07:10 PM
  3. Changing export Spec breaks code
    By tagteam in forum Access
    Replies: 4
    Last Post: 01-27-2016, 06:13 PM
  4. Changing iff into VBA code
    By masond in forum Access
    Replies: 1
    Last Post: 02-21-2014, 08:28 AM
  5. Changing order by using code
    By FRAZ in forum Queries
    Replies: 4
    Last Post: 01-22-2014, 12:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums