Refreshing Controls

[SixHat]

I unfortunately am having the same issue... I've been researching it and have found many similar cases... but so far this seems to be the closest.

For me I am not new to Access development or VBA but I've been stuck on this one for hours and am reaching out. I can however provide more background as to why my situation is running into this problem.

I just recently did a SQL Server Migration and am now troubleshooting the issues that have sprung up with the new SQL back end. This is the last one that I can not figure out:

I have a split form with a check box control... currently the form is not updatable because the form is bound to a view through a DSN-Less connection.

Here is the ringer: I have a check all check box at the top of the form... and even though the bound form is not updatable I update the records like so:

    Dim rsSelect As DAO.Recordset
    Dim rsUpdate As DAO.Recordset
    Dim SQL As String
    Dim CurrDb As Database
    Dim currFilter As String
 
 
   On Error GoTo chkSelect_Click_Error
   ' Capture current filter
    If Me.FilterOn Then currFilter = Me.Filter
    Set rsSelect = Me.RecordsetClone
 
    Set CurrDb = CurrentDb
 
     rsSelect.MoveFirst
    Do While Not rsSelect.EOF
         SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & rsSelect("TimesheetID")
        Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
        If Not rsUpdate.EOF Then
            If Me.chkSelect Then
                With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = True
                    .Update
                End With
            Else
                 With rsUpdate
                    .Edit
                    rsUpdate("TimesheetSelect") = False
                    .Update
                End With
            End If
        End If
        rsSelect.MoveNext
     Loop
    rsUpdate.Close
    rsSelect.Close
    Me.Requery
    If currFilter > "" Then
        Me.Filter = currFilter
        Me.FilterOn = True
    End If
    If Me.chkSelect Then
        Me.lblSelect.Caption = "Select None"
    Else
        Me.lblSelect.Caption = "Select All"
    End If
 
   On Error GoTo 0
   Exit Sub
 
chkSelect_Click_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure chkSelect_Click of VBA Document Form_frmTimesheetSummary"
 

Here is the kicker... I replicated this same code for when a user click's an individual checkbox... and this formely worked with the Access back end... but now I can't get the individual checkbox to update but the check all/ none code still works!

Here is the code for the individual checkbox:

    Dim rsUpdate As DAO.Recordset
    Dim SQL As String
    Dim CurrDb As Database
    Dim currFilter As String
 
   ' Capture current filter
    If Me.FilterOn Then currFilter = Me.Filter
 
    Set CurrDb = CurrentDb
     SQL = "SELECT * FROM tblTimesheet WHERE [TimesheetID] = " & Me.TimesheetID
    Set rsUpdate = CurrDb.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    If Not rsUpdate.EOF Then
        If Me.TimesheetSelect Then
            With rsUpdate
                .Edit
                rsUpdate("TimesheetSelect") = False
                .Update
            End With
        Else
             With rsUpdate
                .Edit
                rsUpdate("TimesheetSelect") = True
                .Update
            End With
        End If
    End If
 
 
    rsUpdate.Close
    Me.Form.Requery
    'Me.Repaint
    Me.Refresh
    If currFilter > "" Then
        Me.Filter = currFilter
        Me.FilterOn = True
    End If
 

I run this code on the Checkboxes MouseDown Event. I hope this makes sense... so in short my first bit of code which checks or unchecks all of the checkboxes works... but my second bit which a user would use to check each one individually does not work. But note it is updating the back end... If I close and reopen the form like the SO then it shows the updated records checked... but I can not get it to update... AND I've tried what worked for the SO i.e. Repaint, and Refresh... although I really feel all that should be needed is a Requery.

Any help in this would be greatly appreciated!

[zmbd]

Split this from:
Refreshing Checkbox control after programmatic update

SixHat - Please do not hijack threads, start a new thread with a link to the old if needed for context.

I have a split form

A creation from the evil mind of a sadistic program engineer.

These forms have no end of troubles when doing anything beyond the basic display and edit of bound record-sets. Bytes.com is full of threads regarding this torture device.

...SQL Server Migration and am now troubleshooting the issues...but now I can't get the individual checkbox to update but the check all/ none code still works!

+Me.Form.Requery
Curious about this construct, it can cause some issues.

+ You haven't explicitly stated this; however, inferring from the first portion of your post try changing all of your Me.Requery entries to:

    With Me
        .RecordSource = Me.RecordSource
        .Refresh
        .Repaint
    End With

(this is a cut-n-paste from a working front-end so it should work for you too)

The repaint shouldn't be needed; however, never hurts to force the re-draw of the form.

You most likely will loose the current record pointer when you reset the record source... see what happens and if you do lose the position then use a form level variable to store the current record's primary key and then use findfirst to find that record against the primary key (this is one reason why I always use a single field primary key :) ) then the bookmark to move the form's bookmark to that record.

[SixHat]

@Zmbd... Thank you for the reply... and sorry for the "Hijacking".

I am new to Bytes, and am used to other forums that would have considered this question a duplicate because it is pretty much the same situation as the initial thread I posted the question on... except the resolution that worked for that OP didn't work for me.

I copied and pasted in yours as well and unfortunately it still didn't repaint... unless I close and re-open the form. It's the craziest thing!

[zmbd]

+ Well, I'm at a bit of a loss here TBH so the following are just some things that occur to me in no particular order (other than this first one... :) )

+ your code to the on_click event of the control not the mouse_down event. May not make a difference. If the record is showing as edited in the form this may be causing some disconnect between the form record-set the backend. The record should be in a saved state. We may need to do this in the before_update event and set the cancel to true, me.undo, and then requery once your backend is edited.

+ Split forms have some very unusual behaviors.
To test this I would create a new, non-split, form based on your record source and try the same codes etc... if this behavior reoccurs then this may be a quirk in the connection. It could be the underlying functioning of the split form that is running like a timer event or other code as suggested by Nico5038 in the old thread that is causing the issue.

+ A hypothesis: Because the record-source is being opened as "read-only" Access may be too smart for its own good and assuming that there can be no changes to the record-set; thus, when one attempts to requery the program assumes that there can be no changes so it simply bypasses the form update.

+ There appears to occasionally be a disconnect between the SQL-Server and Access when it comes to updating. One article I've just perused suggested that all of the tables on the SQL-Sever side have a timestamp field as Access will use this field when pulling data.

Because you are altering the underlying record source directly, it may be that the timestamp isn't being properly updated in the backend until after the form is closed.

It would be best to find a way to make the record directly editable.

+ Check the default refresh time.
Accss2013>Ribbon>File>Options>Client Settings>Advanced
You might try reducing the ODBC refresh (manual requery should over-ride this setting)

++ On a side note:

Seth (IIRC) had an issue with the Y/N fields when they contained a null value. This is indeed a known issue between Access and SQL-Server that I don't believe MS has addressed. Double check that you have no null values in this field and set the field to not allow nulls and to have a default value.
(this article reminded me of that issue:
Five Common Pitfalls When Upgrading Access to SQL Server. It's an old article; however, I see the same comments over and over again in the other blogs/articles regarding the migration to SQL-Server.

No Worries about the posting in the old thread... Bytes has a different "feel" than many of the other sites. Especially in this case, the old solution found in the prior thread didn't work for you; thus, although related, this is fundamentally a new question - sort of like cousins are related, yet different.

[jforbes]

zmbd touched on it... but I would make sure that every Table in SQL has a timestamp field. This is the type of thing that can happen without it.

To rule out the Split Form possibility, when does your code above run? When a Command Button is clicked or in the Checkbox AfterUpdate Event? If it is on the Checkbox, does it error the same from the Dataview portion of the Form and the Single Form portion of the Form?

I'm also curious how you are Binding your Form. I don't see why the Form wouldn't be updatable, unless you are binding to a complex query or you are not supplying a Key when linking to the Table.

[SixHat]

@Zmbd & @jforbes I used SSMA for the initial migration which beautifully adds the timestamp field... although I have found in the past sometimes tables get missed but I've double checked and its there.

I used the click event for the "Check All" checkbox as it was unbound and fully clickable... but the "Check Individual" check box is as stated bound to an non-updatable view... since the record source is not updatable then the checkboxes Click_Event never fires... thus the switch to the Mouse_Down event.

Yes it is a complex query that holds aggregate values and joined on at least 5 tables. And yes the table does have a primary key. The craziest part about this is that the table is being updated... I've confirmed this several times by looking directly at the table after manually clicking a checkbox... the form just refuses to reflect this update.

Zmbd's hypothesis makes sense... but again the confusing part is that the check all logic works... but the check individual logic does not work?

I will try recreating the form without the split and let you know.

[zmbd]

Lets force the engine to complete any pending tasks that might be the result of your mouse method or the split form...

    DBEngine.Idle dbRefreshCache 
    With Me
         .RecordSource = Me.RecordSource
         .Refresh
         .Repaint
     End With

[SixHat]

Thanks to all who posted... the answer turned out to be what @Zmbd stated from the outset about split forms: "A creation from the evil mind of a sadistic program engineer"

When I redesigned the form in a Parent/ Subform design it all worked again.

[zmbd]

I'm glad that solved the issue... this was a real head=scratcher.

If you wouldn't mind, could you try the last bit of code with the DBEngine.Idle dbRefreshCache would be nice to know if this helped with the split form and would be a better solution to your original question if it does allow the form to update the way it should.

[jforbes]

If you haven't put all your toys away yet, there is something you can attempt to address your problem on your Split Form.

My understanding of Split Forms has been largely shaped by this article: https://bytes.com/topic/access/answe...-a#post3778277 You can read it if you want, but the basic idea is that when a Split Form is created and displayed, there are two instances of the Form created and placed in a single Form Container. One is the Datasheet View and the other is the Form View. Each has their own copy of controls, variables and code. When one of the Forms is updated, Access updates the other for you, with Magic. Unfortuantly, this magic isn't foolproof, and I would agree with zmdb that there might be some sadistic and evil goings on here.

So, if you are up for it, there is a trick that might work for you. The trick is to include a check to see what the current view happens to be (Form or Datasheet) and if it is a Datasheet then hop out of the current running procedure and call the procedure on a Form in the Forms Collection. This way it will force the Form View version of the Form's code to be executed.

To do this, the first thing to do is move the code in the MouseDown event to it's on Subroutine and add the Datasheet/Form Check, kind of like this:

Public Sub updateTimeSheet()
 
    Dim rsUpdate As DAO.Recordset
    Dim SQL As String
    Dim CurrDb As Database
    Dim currFilter As String
 
 
    If Me.CurrentView = 2 Then
        If isLoaded(Me.Name) Then Forms (Me.Name).updateTimeSheet
    Else
        ' Capture current filter
        If Me.FilterOn Then currFilter = Me.Filter
 ... 
        Me.Refresh
        If currFilter > "" Then
            Me.Filter = currFilter
            Me.FilterOn = True
        End If
    End If
 

You'll also need this function. Well you don't need it, but it's handy. Put it in a module somewhere:

Function isLoaded(ByRef sFormName As String) As Boolean
    ' Determines if a Form is loaded
    Dim i As Integer
 
    isLoaded = False
    For i = 0 To Forms.Count - 1
        If Forms(i).FormName = sFormName Then
            isLoaded = True
            Exit Function
        End If
    Next
End Function

You'll then need to go back to the Checkbox and have it call the new Subroutine. One other thing I would change while you are at this is to use the AfterUpdate Event instead of the MouseDown Event. It probably won't make a difference, but it might.

Private Sub chkSomeCheckbox_AfterUpdate()
    Call updateTimeSheet()
End Sub

If you don't want to mess with this, I will understand.

[SixHat]

@Zmbd, @JForbes For sure... I've actually made the changes already but can draw it back as it's in source control... so I'll give'em a try and let you know. I probably will not have time to do this until tomorrow... but I'll be sure to keep you both posted. Thanks

[SixHat]

Customer was a little behind this morning so had time to give it a try now.

@Zmbd... DBEngine.Idle dbRefreshCache did not work. I tried it with DoEvents and without...

@JForbes Thanks for the info on Split Forms... I was not aware that it was constructed that way... but now that you explain it... it makes sense with some of the weird behavior that I've seen them display in the past...

In trying your code my first instinct was to make your Public Sub Private... but after stepping through it I quickly understood why you made it public so switched it back...

If you had not explained how split forms were constructed the code would have not made sense as it's a procedure calling itself... but after the explanation I was really hopeful and thought for sure that it would work... To my great disappointment however it did not. I even tried throwing in a Me.Requery but that did not get it either.

Thank both of you for your time and help

[zmbd]

That's most unfortunate - thank you taking the time to try both J's and my suggestions - we can't win them all :)
-z