Results 1 to 6 of 6
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question "Dirty"/Save issue


    I have the below code execute in a check box's "Click" event. Every time blnUserActive = True I receive the message pictured in attachment....How do I avoid this? I have tried putting the dirty check before and after the save record command...neither work.

    Code:
    Private Sub chkUserActive_Click()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        
        Dim blnUserActive As Boolean
        Dim dtCurrDate As Date
        Dim strUserID As String, mySQL As String
    On Error GoTo ErrorHandler
    
        Set db = CurrentDb
        
        strUserID = txtUserID.Value
        mySQL = "SELECT * FROM tblUsers WHERE [UserID] = '" & strUserID & "';"
        Set rs = db.OpenRecordset(mySQL, dbOpenDynaset)
        rs.MoveLast
        rs.MoveFirst
        
        dtCurrDate = Date
        blnUserActive = chkUserActive.Value
        If blnUserActive Then
            rs.Edit
            rs("UserInactiveDate").Value = dtCurrDate
            rs.Update
            If Me.Dirty Then Me.Dirty = False
            RunCommand acCmdSaveRecord
        Else
            rs.Edit
            rs("UserInactiveDate").Value = Null
            rs.Update
            RunCommand acCmdSaveRecord
        End If
        rs.Close
        
        Exit Sub
        
    ErrorHandler:
        MsgBox "Critical Error!" & vbCrLf & vbCrLf & Err.Description, vbCritical, "Error # " & Err.Number
        
    End Sub

  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
    Is the form bound to tblUsers? If so, that is likely the cause of the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Yes it is bound...Reason being is I am using a split form to display all users.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    hey bud,

    your code is just updating the current user's record with the current date, right? why use all of that code to do that? why can't you simply run a one line sql statement in vba!?

    in addition to that, why are you updating the user's record with the current date? all logging attempts that I've ever seen record everything from log on times to log off times. and there's one huge table with all of the information in it.

    what are you trying to accomplish with this overwriting effort? just curious...

  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
    Why not just update the value on the form then? You either want to update via bound form or code, not both.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Updating via SQL would be much simpler. The user id is a PK field so I could use that....One of my problems lately is I will get stuck on a method (i.e. recordsets) and make it work for everything! The reason I'm updating the date is because it is to just track when the user was made inactive. If they are inactive they do not have access to the DB. However, I don't want to delete them because I am tracking when they access certain features in a separate table. Thanks to both of your for your suggestions and snapping me into shape!

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

Similar Threads

  1. Replies: 1
    Last Post: 04-14-2011, 03:46 PM
  2. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  3. "AfterUpdate" issue
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 03-04-2011, 01:46 PM
  4. Replies: 11
    Last Post: 11-26-2010, 10:53 PM
  5. Replies: 5
    Last Post: 07-19-2009, 08:37 AM

Tags for 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
  •  
Other Forums: Microsoft Office Forums