Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Update or Cancel Update without add new or edit?


    Members - Update - Copy2.zip

    Good Evening all,
    I have two forms on this db that are basically the same, different info and tables but for the most part same coding.

    The MembersFrm works very well with one exception, when I use the MemberSearch Combo in footer, when I select the first record the record counter doesn't reset? It works fine unless I select the first record?

    The EventFrm is creating an error when I select the EventSearch combo in footer that states "Update or Cancel Update without add new or edit" I have read a lot of things but I cannot fine the problem and why this is acting
    this way?

    Update.... I have been taking things out and testing and the following code I believe is the issue?
    Code:
            If Me.TxtStartDate <= Date And Me.TxtEndDate > Date Then
            Me.IsActive = True
        Else
            Me.IsActive = False
        End If
        
        If Me.TxtEndDate < Date Then
            Me.Complete = True
        Else
            Me.Complete = False
        End If
    This code was in the on current event so most likely the issue? I need this but could I get some assistance of maybe where to put this or change it so it will work correctly?



    Have added update db
    thanks
    Dave
    Last edited by d9pierce1; 05-04-2019 at 07:50 PM. Reason: Possible Issue Found?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The EventFrm opens and immediately puts record in edit mode. Because code is in form Current event, each record is immediately in edit mode when navigated to. This is not conventional code. This is causing conflict with the BeforeUpdate event code trying to save Now() value into field, for some reason only after selection in combobox. Should only set value of IsActive and Complete when the date fields are changed. IsActive and Complete don't even have to be fields in table. Just have expression in Checkbox ControlSource to calculate this for each record. I deactivated the IsActive and Complete code so there is no error but the combobox code does not find record.

    SearchForRecord is using textbox name when should use field name in the WHERE argument:

    DoCmd.SearchForRecord , , acFirst, "[EventID] = " & Me.CboEventSearch
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you June7

    If I understand you on this, I should put the IF statements in the after update on the dates? I think I will need the check boxes as I plan to query them for active, not, complete, not later on in a different form?
    Not completely clear on this but will play with it some.
    Thank you so much
    Dave

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Further, I am suggesting you don't need VBA code for checkboxes at all. The same expression in ControlSource property can provide the same output, just not saving calculated value to record. The same calculation can be done on other forms.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    thank you,
    I will read some things about that as that I wasn't aware of. Learning as I go....
    Thanks

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    for Memberfrm:
    Code:
    Private Sub CboMemberSearch_AfterUpdate()
        On Error GoTo Err_Handler
        DoCmd.SearchForRecord , "", acFirst, "[MemberID] = " & Str(Nz(Screen.ActiveControl, 0))
        CboMemberSearch = Null
        Call Form_Current
    Exit_Handler:
        Exit Sub
    Err_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "CboMemberSearch_AfterUpdate()"
                Resume Exit_Handler
        End Select
    End Sub
    
    for EventFrm
    Code:
    Private Sub CboEventSearch_AfterUpdate()
        On Error GoTo Err_Handler
        DoCmd.SearchForRecord , "", acFirst, "[EventID] = " & CboEventSearch
        CboEventSearch = Null
    Exit_Handler:
        Exit Sub
    Err_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "CboEventSearch_AfterUpdate()"
                Resume Exit_Handler
        End Select
    End Sub
    
    At end of Form_Current
    Code:
        If ctl.Tag = "Lock" Then
            If RecordLock = True Then
                ctl.Locked = True
            Else
                ctl.Locked = False
            End If
        End If
        Next ctl
    CboEventSearch.Requery
    Exit_Handler:
        If Me.Dirty Then Me.Dirty = False
        Set rst = Nothing
        Exit Sub
    Err_Handler:
        Select Case Err
            Case Else
                MsgBox "Error " & Err & ": " & Error$, vbExclamation, "Form_Current()"
                Resume Exit_Handler
        End Select
    End Sub
    

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you, Worked like a smooth running machine!

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Updated, Delete and Cancel buttons in question with misc code added to on current?

    Members - Update - Copy3.zip

    Hi Davegri,
    Thanks again for the assistance. I marked this as solved maybe prematurely? in any event, I just dont know where to put the following code and i have tried various places within the forms controls.
    [CODE
    If Me.TxtStartDate <= Date And Me.TxtEndDate >= Date Then
    Me.IsActive = True
    Else
    Me.IsActive = False
    End If

    If Me.TxtEndDate < Date Then
    Me.Complete = True
    Else
    Me.Complete = False
    End If][/CODE]

    So, I made the changes to as suggested, and have uploaded the db with changes made. When testing if I leave this above code in on current, then if I add event, decide to cancel, then it wont allow the cancel and
    keeps blank record. Wont Delete and Undo.

    I just don't know where I can put this code at? What the intent is to have the IsActive and Complete auto checked if the criteria in the above code is reached and I would think it would have to be on current but that creates
    issues with other code as described.

    Any ideas where I can stick this code into and it will work with the rest of the form? EventFrm is the form it is on.

    Thanks
    Dave

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    How about here? You would have to tab out of the end date field after it is automatically populated.

    Code:
    Private Sub TxtEndDate_LostFocus()
        If Me.TxtStartDate <= Date And Me.TxtEndDate >= Date Then
            Me.IsActive = True
        Else
            Me.IsActive = False
        End If
        
        If Me.TxtEndDate < Date Then
            Me.Complete = True
        Else
            Me.Complete = False
        End If
    End Sub
    
    Last edited by davegri; 05-05-2019 at 08:57 AM. Reason: more

  10. #10
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks,
    Will this allow it to auto update as the date changes or will this require to have to tab out of this each time to update it?

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Will this allow it to auto update as the date changes or will this require to have to tab out of this each time to update it?
    Don't understand the circumstances of the question. The start date is locked by code once entered and cannot be changed.

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Sorry for confusion,
    I was curious and yes, that once I enter the start date, then the end date is set auto. I don't have to revisit those fields. However, with each day that passes will this allow the IsActive and Completed to be updated automatically? Such as if the date is passed the completion date will it set value to completed to true? and so on.....????
    Thanks
    dave

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    It should work if the control gets focus and you tab out of it every time move to record.

    Really just hit me you want the IsActive and Completed to be dynamic based on current date, not fixed at the time StartDate and EndDate are entered.

    If you stick with VBA approach, regardless of which event, means if on any day you run report without first visiting EVERY record on form, the values on report could be incorrect. Would have to run an UPDATE query every day before using records in report or form to make sure calcs are up to date.

    You want to calculate a time period based on fixed input and current date. This is the same concept of calculating a person's age. It changes every day and therefore Age is not saved, it is calculated when needed. Your inputs are StartDate and EndDate.

    There would be no issue if you did as I suggested and do the calculations in ControlSource or in query instead of VBA. Again, there is really no reason to save these calculated values and actually attempting to complicates the process and data integrity is not assured.
    Last edited by June7; 05-05-2019 at 04:20 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    allow the IsActive and Completed to be updated automatically?
    No, it will not. That's why the original code was in the form_current event.
    You might investigate June's idea in post #4, that it might not be required for IsActive and Completed be stored in the table at all, but only displayed on the form.

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Moving along with the previous suggestions,
    Use this SQL to make a named query, like qEventTbl. Use that query as the recordsource for EventFrm. The query will always keep IsActive and Completed accurate using the current date. No calculations in the form needed. Remove the two fields from the table. Any time the fields might be needed elsewhere in the project, use the query instead of the table as the source.
    Code:
    SELECT EventTbl.EventID, EventTbl.EventName, [StartDate]<=Date() And [EndDate]>=Date() AS IsActive, EventTbl.StartDate, EventTbl.EndDate, [EndDate]<Date() AS Complete, EventTbl.BlockCount, EventTbl.BannerCount, EventTbl.Created, EventTbl.Modified, EventTbl.Notes, EventTbl.RecordLock
    FROM EventTbl;
    
    The problem you encountered here demonstrates in spades why it is usually bad practice to store calculated fields in tables.
    Last edited by davegri; 05-06-2019 at 01:00 PM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cancel Update without Discarding Changes
    By MatthewR in forum Forms
    Replies: 3
    Last Post: 09-17-2015, 01:13 PM
  2. Replies: 1
    Last Post: 07-18-2015, 07:59 PM
  3. Replies: 5
    Last Post: 06-12-2015, 02:15 PM
  4. Replies: 2
    Last Post: 03-19-2015, 01:33 PM
  5. Detecting a Before Update Cancel
    By EddieN1 in forum Programming
    Replies: 3
    Last Post: 06-09-2013, 08:04 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