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

    Unanswered: Multiuser PK Lockout

    I'm trying to get the code from the KB article #210194 to
    work. Creating a Custom Counter....
    http://support.microsoft.com/default...b;en-us;210194

    As far as I can tell my code and actions are identical to
    the article. However when I click on the form I get an
    Error: 3265 msg. This error appears to be due to an object
    being referenced before it is in the library? (link -
    http://support.microsoft.com/default...b;en-us;223212)

    Here is my code which compiles fine:
    Function Next_Custom_Counter()

    On Error GoTo Next_Custom_Counter_Err

    Dim rs As ADODB.Recordset
    Dim NextCounter As Long

    Set rs = New ADODB.Recordset

    rs.Open "CounterTable", CurrentProject.Connection,
    adOpenKeyset, adLockOptimistic

    NextCounter = rs!NextAvailableCounter

    rs!NextAvailableCounter = NextCounter + 10
    NextCounter = rs!NextAvailableCounter
    rs.Update

    MsgBox "Next Available Counter is " & Str(NextCounter)

    rs.Close

    Set rs = Nothing

    Next_Custom_Counter = NextCounter

    Exit Function

    Any suggestions?
    What I'd really like to do is have this code put a new value in my CustomerID field but since I can't get their test to work my confidence is low.
    Last edited by Nedley; 04-16-04 at 18:36.

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Try this code instead:

    Code:
    Function Next_Custom_Counter()
    
    On Error GoTo Next_Custom_Counter_Err
    
    Dim rs As Recordset
    Dim NextCounter As Long
    
    Set rs = CurrentDb.OpenRecordset("CounterTable", dbOpenDynaset, dbSeeChanges)
    
    NextCounter = rs!NextAvailableCounter
    
    rs!NextAvailableCounter = NextCounter + 10
    NextCounter = rs!NextAvailableCounter
    rs.Update
    
    MsgBox "Next Available Counter is " & str(NextCounter)
    
    rs.Close
    
    Set rs = Nothing
    
    Next_Custom_Counter = NextCounter
    
    Exit Function
    
    Next_Custom_Counter_Err:
        MsgBox "Error " & err & ": " & error$
        If err <> 0 Then Resume
        End
    End Function

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about a little .edit?

    rs.edit
    rs!NextAvailableCounter = NextCounter + 10
    NextCounter = rs!NextAvailableCounter
    rs.Update


    (...and the same line is missing in the DAO re-write)



    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    Whoops, you're right! (need more coffee!) Here's the COMPLETE, correct code:

    Code:
    Function Next_Custom_Counter()
    
    On Error GoTo Next_Custom_Counter_Err
    
    Dim rs As Recordset
    Dim NextCounter As Long
    
    Set rs = CurrentDb.OpenRecordset("CounterTable", dbOpenDynaset, dbSeeChanges)
    
    NextCounter = rs!NextAvailableCounter
    
    rs.Edit
    rs!NextAvailableCounter = NextCounter + 10
    NextCounter = rs!NextAvailableCounter
    rs.Update
    
    MsgBox "Next Available Counter is " & str(NextCounter)
    
    rs.Close
    
    Set rs = Nothing
    
    Next_Custom_Counter = NextCounter
    
    Exit Function
    
    Next_Custom_Counter_Err:
        MsgBox "Error " & err & ": " & error$
        If err <> 0 Then Resume
        End
    End Function

  5. #5
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Invalid Use of Property?

    Gentleman,

    Thanks for your help with this matter. I've done what I know to get your suggestions to work.

    1) I selected my DAO 3.6 reference and moved it up in priority above the ADO references.

    2) Modified the code.

    3) I got Compile error: Invalid Use of Property.
    rs.EditMode is highlighted.

    I don't understand, rs is a variable which is being edited? What could I do to fix this up or is there something I should read up on to better understand what is going on with the code?

    I'll admit I don't understand the comment about the same line being missing in the DAO re-write but I am going to be researching the comment this morning.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forget ...mode

    rs.edit

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and my "missing" comment:

    you got ADO and DAO suggestions that are almost identical, including the fact that both are missing the essential

    rs.edit

    before attempting to modify the data in the recordset


    izy

    LATER: correction - you found an ADO version on the web and received a DAO re-write on this site
    Last edited by izyrider; 04-19-04 at 13:53.
    currently using SS 2008R2

  8. #8
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Error 3265

    Did I put EditMode in there? Right now I'm in denial mode. It must have been Microsoft somehow.

    First of all they put garbage code in their KB article. Then they autofill my command to EditMode.

    Sorry, about not seeing that. Mondays are usually better for me than that.

    Now I don't mean to be stupid. Hopefully I'm not missing another Mode that autofilled in there somewhere but I am still getting Error 3265: Item Not Found In This Collection.

    When I go to Debug it has the
    If Err <> 0 Then Resume
    section highlighted. I tried adding a value to the table but no success.

    In the meanwhile I've been reading Alison Balter's 2002 Mastering Access Dev. This book has coverage on ADO and DAO and I'm beginning to better understand the code and it's implications.

    I'm sure this can be fixed. Any directions?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the tail end of the DAO suggestion currently reads:
    Code:
    rs.Close
    Set rs = Nothing
    Next_Custom_Counter = NextCounter
    
    Exit Function
    
    Next_Custom_Counter_Err:
        MsgBox "Error " & err & ": " & error$
        If err <> 0 Then Resume
        End
    End Function
    my version of the truth is:
    Code:
    rs.Close
    Next_Custom_Counter = NextCounter
    
    exit_Custom_Counter_Err:
    Set rs = Nothing
    Exit Function
    
    Next_Custom_Counter_Err:
        MsgBox err.number  & ": " & err.description, vbcritical, "ERROR!"
        resume exit_Custom_Counter_Err
    End Function

    but there are a million variations on the theme.

    NOTE also that you can use Mr Gate$ ADO version if you prefer - the key thing was that missing .edit otherwise the ADO looks OK (to me who doesn't write ADO).

    izy

    deleted a spare " in the original post!!
    Last edited by izyrider; 04-19-04 at 15:00.
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hold on, this whole thing is riddled with problems!

    here's the full "my flavour" DAO:

    Code:
    private function Next_Custom_Counter() 
        On Error GoTo err_Next_Custom_Counter
    
        Dim rs As dao.Recordset
        Dim NextCounter As Long
    
        Set rs = CurrentDb.OpenRecordset("CounterTable", dbOpenDynaset, dbSeeChanges)
    
        NextCounter = rs!NextAvailableCounter
    
        rs.Edit
            rs!NextAvailableCounter = NextCounter + 10
            NextCounter = rs!NextAvailableCounter
        rs.Update
    
        MsgBox "Next Available Counter is " & str(NextCounter)
    
        Next_Custom_Counter = NextCounter
    
    exit_Custom_Counter_Err:
        Set rs = Nothing
        Exit Function
    
    err_Next_Custom_Counter:
        MsgBox err.number & ": " & err.description, vbcritical, "Error!"
        Next_Custom_Counter = -999999 ' an error
        Resume exit_Custom_Counter_Err
    End Function
    izy
    Last edited by izyrider; 04-19-04 at 15:10.
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    Grande Prairie, AB
    Posts
    9

    Totally Unsure

    Izy,

    I feel a little bad about this but I just gave up on this code.

    I went to another solution because I felt it was more likely to stand up to real world use.

    I originally tried out Microsoft's solution because it was the only one I could find that was ADO. I'm using Access 2003.
    When it wouldn't work and I had no support from them I was willing to go to DAO. Then when I got to the stage where your solution compiled fine but I couldn't figure out how to update my actual fields I thought that's it, I've had enough.

    Even thought I don't Know you I really appreciated your feedback and knowing then what I know know I would have never started this thread....

Posting Permissions

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