Problem with Refresh after code runs on Activate or On Load.

[Phred]

Access 2007, Sql Server 2008 R2. Problem with refresh.



Form 1, (Single Form) Parent Form, contains Property Year Detail data. PK = PropYrDetID. This is a SINGLE FORM

Form 2, (Single Form) Child form, contains land square footage data from multiple records. PK = SPYDID FK = PropYrDetID. This is a SINGLE FORM.

The code below executes every time Form 1 opens, activates or whatever. The reason is that data in Form 2 changes frequently. Thus every time Form 1 opens the end user will see the latest data.

"Form 1" receives a series of values that it obtains from VBA code that runs when it opens or activates.

"Form 2" has the data being summarized in Form 1.

All of the code and queries below run fine and return the correct values from Form 2 to Form 1. My problem is Refresh on Form 1.

I've tried the code in the On Current, On Activate, On Load of Form 1. Mostly the data shows up in Form 1. Other times parts of the data are left out. I have to hit F5 or close and reopen the form, and then the data appears. When I don't see data on Form 1, I check the underlying table and the correct data exists. I don't know how to achieve a 100% refresh success. I have tried me.refresh and different Events all over the place.

All of the code below runs when Form 1 opens. Queries are fine, Equations are fine, Tests run fine. It's the results showing up that I am having the problem with.

I don't think the issue is with the code but with the Refresh. Here it is for your review.

The term "Equations" is the customers. There are 5 equations. Some have a series of tests after the Equation to determine the result.

Private Sub Form_Activate()

Dim rs As Variant
Dim varAOProp As Variant
Dim varAOIni As Variant
Dim varAOCert As Variant
Dim varBORIni As Variant
Dim varBORFinal As Variant
Dim varZero As Variant
Dim Rs1 As Variant
Dim RS2 As Variant
Dim RS3 As Variant
Dim RS4 As Variant
Dim RS5 As Variant
Dim intRecordSet As Variant

'Total Squart Footage from sPIN for Property
intRecordSet = DFirst("sumOFLandSF", "TestTotalspinSFforPYD")
Me.TotalLandSF = intRecordSet
Me.Refresh

'Number of Property Year Detail records
rs = Nz(DCount("*", "TestNumPYDRecords"), 0)
Me.TXTCountPYD = rs
Me.Refresh

'Number of SPIN records
rs = Nz(DCount("*", "TestNumSPINRecords"), 0)
Me.TXTCountSPINS = rs
Me.Refresh

'Number of SPYD records
rs = Nz(DCount("*", "TestNumSPYDRecords"), 0)
Me.TXTCountSPYD = rs
Me.Refresh

'EQUATION 1 RS1.

        Me.AOProposedAV = Null
        Me.AOinitialAVresult = Null
        Me.AOResult = ""
        Me.AOcertifiedAV = Null
        Me.AOreReviewResult = ""
        Me.BORInitialAVResult = Null
        Me.BORresult = ""
        Me.BORfinalAV = Null
        Me.BORreReviewResult = ""
 
        Rs1 = DSum("total", "TotalAOProposedAVResult")
        If IsNull((Rs1) = True Or (Rs1) = 0) Then
        
        Exit Sub
        End If
        Me.AOProposedAV = Rs1

'No tests for equation 1

'EQUATION 2 RS2.

        RS2 = DSum("total", "TotalAOInitialAVResult")
        If IsNull((RS2) = True Or (RS2) = 0) Then
        
        Me.AOinitialAVresult = Null
        Me.AOResult = ""
        Me.AOcertifiedAV = Null
        Me.AOreReviewResult = ""
        Me.BORInitialAVResult = Null
        Me.BORresult = ""
        Me.BORfinalAV = Null
        Me.BORreReviewResult = ""
        Me.Refresh
                
        Exit Sub
        End If
        
        Me.AOinitialAVresult = RS2
'EQUATION 2 Test 1
                If RS2 = Rs1 Then
                Me.AOResult = "NC"
                Me.Refresh
                End If
'EQUATION 2, Test 2
                If RS2 < Rs1 Then
                Me.AOResult = "RED"
                Me.Refresh
                End If
'EQUATION 2, Test 3
                
                If RS2 > Rs1 Then
                Me.AOinitialAVresult = Null
             
                    Call MsgBox("AO Initial AV Result will be greater than AO Proposed AV total." _
                    & vbCrLf & "" _
                    & vbCrLf & "This indicates a Data Error in the SPYD records." _
                    & vbCrLf & "" _
                    & vbCrLf & "All related fields will be cleared." _
                    & vbCrLf & "" _
                    & vbCrLf & "Click OK to Exit, Locate, and Correct the data." _
                    , vbCritical, "Incorrect Data")
                
                Me.AOinitialAVresult = Null
                Me.AOResult = ""
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.BORInitialAVResult = Null
                Me.BORresult = ""
                Me.BORfinalAV = Null
                Me.BORreReviewResult = ""
                Me.Refresh
                               
                Exit Sub
                End If
'EQUATION 3 RS3
        RS3 = DSum("total", "TotalAOCertifiedAVResult")
        If IsNull((RS3) = True Or (RS3) = 0) Then
       
        Me.AOcertifiedAV = Null
        Me.AOreReviewResult = ""
        Me.BORInitialAVResult = Null
        Me.BORresult = ""
        Me.BORfinalAV = Null
        Me.BORreReviewResult = ""
        Me.Refresh
        
        End If
        
        Me.AOcertifiedAV = RS3
'EQUATION 3, Test 1.
If RS3 = RS2 Then
                Me.AOreReviewResult = "NC"
                Me.Refresh
                End If
            'EQUATION 3, Test 2.
            
If RS3 < RS2 Then
                Me.AOreReviewResult = "RED"
                Me.Refresh
                End If
'EQUATION 3 Test 3.
                If RS3 > RS2 Then
               
                    Call MsgBox("AO Certified AV will be greater than AO Initial AV Result total." _
                    & vbCrLf & "" _
                    & vbCrLf & "This indicates a Data Error in the SPYD records." _
                    & vbCrLf & "" _
                    & vbCrLf & "All related fields will be cleared." _
                    & vbCrLf & "" _
                    & vbCrLf & "Click OK to Exit, Locate, and Correct the data." _
                    , vbCritical, "Incorrect Data")
            
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.Refresh
          
            Exit Sub
            End If
'EQUATION 4, RS4
        
RS4 = DSum("total", "TotalBORInitialAVResult")
        Me.BORInitialAVResult = RS4
        If IsNull((RS4) = True Or (RS4) = 0) Then
        
                Me.BORresult = ""
                Me.BORfinalAV = Null
                Me.BORreReviewResult = ""
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.Refresh
                End If
'EQUATION 4, Test 1.
            
If RS4 = RS3 Then
                Me.BORresult = "NC"
                End If
'EQUATION 4, Test 2.
            
If RS4 < RS3 Then
                Me.BORresult = "RED"
                End If
'EQUATION 4, Test 3.
            
If RS4 > RS3 Then
                
                    Call MsgBox("BOR Initial AV Result will be greater than AO Certified AV Result total." _
                    & vbCrLf & "" _
                    & vbCrLf & "This indicates a Data Error in the SPYD records." _
                    & vbCrLf & "" _
                    & vbCrLf & "All related fields will be cleared." _
                    & vbCrLf & "" _
                    & vbCrLf & "Click OK to Exit, Locate, and Correct the data." _
                    , vbCritical, "Incorrect Data")
                
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.Refresh
                Me.BORInitialAVResult = Null
                
                Exit Sub
                End If
'EQUATION 5, RS5
RS5 = DSum("total", "TotalBORFinalAVResult")
        Me.BORfinalAV = RS5
        If IsNull((RS5) = True Or (RS5) = 0) Then
        
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.Refresh
                Me.BORInitialAVResult = Null
                Me.BORresult = ""
                Me.Refresh
                Me.BORfinalAV = Null
                Me.BORreReviewResult = ""
                Me.Refresh
        Exit Sub
        End If
'EQUATION 5, Test 1.
                If RS5 = RS4 Then
                Me.BORreReviewResult = "NC"
                
                End If
            'Equation 5, Test 2.
            
                If RS5 < RS4 Then
Me.BORreReviewResult = "RED"
End If
'Equation 5, Test 3.
            If RS5 > RS4 Then
                      
                    Call MsgBox("BOR Final AV will be greater than BOR Initial AV Result." _
                    & vbCrLf & "" _
                    & vbCrLf & "This indicates a Data Error in the SPYD records." _
                    & vbCrLf & "" _
                    & vbCrLf & "All related fields will be cleared." _
                    & vbCrLf & "" _
                    & vbCrLf & "Click OK to Exit, Locate, and Correct the data." _
                    , vbCritical, "Incorrect Data")
            
            Me.BORInitialAVResult = Null
                
                Me.AOcertifiedAV = Null
                Me.AOreReviewResult = ""
                Me.Refresh
                Me.BORInitialAVResult = Null
                Me.BORresult = ""
                Me.Refresh
                Me.BORfinalAV = Null
                Me.BORreReviewResult = ""
                Me.Refresh
                Exit Sub
                End If
End Sub

Thanks again, Phred

[ItsMe]

I guess the Private Sub Form_Activate() procedure is in form 1? This code will only fire when From gets focus. So, the form would have to lose focus and then get focus before the code would fire. You need to find an event that detects change in any of the given tables.

I do not use the Refresh method, perhaps I should. The way I understand it, if you want to refresh the values of controls that are bound to a table/query so they display the data that is currently in the table, use the .Refresh method. How you are employing it does not seem to be useful. It seems you execute the method after assigning a value to a an unbound control. The unbound control is not affected by the Refresh method.

[Phred]

You are correct in your assumptions. It all fires on Form1 Activation. What you said makes sense on the me.refresh, They are not bound to a field. Yes, the form would lose focus and then get focus. That's the only thing I could think of. Is there an Event that detects change in tables? Perhaps there is a better approach. Perhaps someone can point me in a better direction to display data that doesn't need to be stored redundantly. I could create fields that can be bound to the Form 1 underlying table. That way an update query could write the new values and the refresh problem would be solved.

Phred

[Phred]

This is what I’m thinking. There are only about six fields on Form 1 that receive, and display, the data. I will bind these six fields to the table underlying Form 1. While it breaks normal form it’s not that much of a load.

I will move the code that processing the data to Form 2 and its underlying table. I will set the code to fire in the After Update Event on Form 2. So it will only execute when the number changes. This action will run the various tests and then run a update query that writes to the underlying table for Form 1.
What do you think of that approach? Is there any way to improve it?

All comments are welcome. I am learning a lot. Thanks It's Me.

Phred

[ItsMe]

An "Event" is something that the User does. When the user does something, it can trigger an event handler, like a button click event.

You can reference a form that is open from another form. So if a user does something in form A, you can reference form B from form A at that time.
Forms!frmName.Requery or Forms!frmName.Requery
http://access.mvps.org/access/forms/frm0031.htm

[Phred]

Thanks, this did the trick. Gotta test but it looks good and makes sense.

I will mark this solved. Thank you for your insight.

Phred