Calculated fields not updating

[Elysyn]

Good morning,

Problem 1:
I have a form that I could use some assistance on. I have three fields on Tab A that pull data from a query. On Tab C, I have several fields that run calculations based on the query result. However, after the query runs, the calculated fields do not update. The only way I can get them to update is to click into the boxes where the query poplulates.

Problem 2:
The control source for these fields is a table where the queried data (along with several other pieces) will be stored. After the query runs, the data does not save to the table. Manually saving the record does not remedy this.

The current code behind it is based on a single command buttone (Problem source maybe?)

Here is what is going on so far...

Option Compare Database
Option Explicit
Private Sub Command312_Click()

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Evaluator
     
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub
Sub Evaluator()
    
    
    If Me.txtQpts >= 15 Then
        Me.chkQualEvalOverallExceeds.Value = -1
        Me.chkQualEvalOverallMeets.Value = 0
        Me.chkQualEvalOverallNeeds.Value = 0
    Else
        Me.chkQualEvalOverallExceeds.Value = 0
    End If

    If Me.txtQpts > 11 Then
        If Me.txtQpts < 14 Then
            Me.chkQualEvalOverallMeets.Value = -1
            Me.chkQualEvalOverallExceeds.Value = 0
            Me.chkQualEvalOverallNeeds.Value = 0
        End If
    Else
        Me.chkQualEvalOverallMeets.Value = 0
    End If

    If Me.txtQpts < 10 Then
        Me.chkQualEvalOverallNeeds.Value = -1
        Me.chkQualEvalOverallExceeds.Value = 0
        Me.chkQualEvalOverallMeets.Value = 0
    Else
        Me.chkQualEvalOverallNeeds.Value = 0
    End If
    
End Sub

Please let me know if there is any additional information that would help to better understand our plight.

Thanks for your help!

 

[mdmilner]

Try inserting a:

Me.Refresh

Near the end (inside the click event)

Mike

 

[Elysyn]

I have tried that, and it did not work.

I also tried me.Repaint and Recordset.Update , just to see and that didn't work either.

 

[mdmilner]

I'm not sure that I follow everything you're saying (I plead insanity) but...

ok, I'd have thought Me.Refresh would fix the fields in the form. I remember experimenting with Me.Requery & Me.Refresh just a few months ago and thinking that Me.Refresh is the right route for a Form based on a Table/Query.

On problem 2
(this is where I'm hazy)
Have you thought about basing the form on a temporary table and using an append query to kick the data over to a permanent table?

Are you using multiple methods to update table fields?
1) Via the Form
2) Via action queries?

Mike

 

[Elysyn]

I had thought the me.refresh would have worked too... I was puzzled when it did not.

Could it be because I was trying to insert it into the code for the Command312_Click()?
This is where I had it inserted...

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 

Me.Refresh

Evaluator

Could it be that I am running too many things under a single command?

With regards to problem 2, that is the direction we have started heading down and it seems to be working. My only concern is that the code is getting to be pretty heavy... And given that we are only on the first third of this form it is only going to get heavier. I was looking for an efficient solution (there may not be one...) knowing that this is only going to be growing.

Regarding the multiple methods question:

Yes, the table is being updated by multiple methods both through the query and input from the form. (I think I hear a subform suggestion coming... )

Thanks for your help!

 

[mdmilner]

I'd put it at the end. Last thing before your: End Sub
(but before any variable cleanup which you don't have here)

Only pitfalls I had when self-teaching to do similar tasks was making sure I reduced code duplication via Subroutines as much as possible and streamlining code. Heading off to a meeting in a couple minutes or I'd try to answer more fully.

By the nature of your comments, it sounds like you have a real good idea how to build this.

 

[Elysyn]

I'll have to try putting it at the end when we have a bit more time. My partner and I have gone with our long code work around solution that does the trick for now for Problem two. (Problem 1 was resolved in that fix also.)

That is great advice regarding the Subroutines. One thing to be said about self-teaching is that it is a never-ending learning experience!

Thanks for all your help, and have a great day!