Control.Requery misbehaving (1 Viewer)

[JamesMcS]

So then, yet another odd one - I've got this form, image attached. As you can see there are a number of check boxes to the left, these are used to 'tag' a particular record for including in reports etc.

The text boxes at the top are supposed to sum up the sales figures for those records that are tagged, example control source would be:

=Sum(IIf([Tagged]=-1,[AEM CY-1],0))

I've labelled the field AEM CY-1 on the image. In the on click of the check box I've got

Private Sub Tagged_Click()
For Each Control In Me.Controls
If InStr(Control.Tag, "Totals") Then
Control.Requery
End If
Next
End Sub

Each of the summing-up text boxes has a tag that includes "Totals". So the goal is that every time you click a tagged check box, the totals change to reflect what you've selected. Now for the strange bit: the change in totals seems to be one click behind what you're clicking - i.e. if you uncheck the first row, nothing happens. If you uncheck the second row, the totals reduce by the amount of the first row, and so on - no matter what order you check/uncheck them in, it's always one behind.

Also, super weirdly, check box in the header row executes the following code:

Sub TagAll(Called_From As Form)
Dim Rst As DAO.Recordset
Set Rst = Called_From.Recordset
Rst.MoveFirst
Do Until Rst.EOF
Rst.Edit
If Called_From.Tag_All.Value = -1 Then
Rst.Fields("Tagged") = -1
Else
Rst.Fields("Tagged") = 0
End If
Rst.Update
Rst.MoveNext
 
Loop
Rst.MoveFirst
End Sub

And this, despite not requerying the controls, has an immediate effect on the total boxes.

"Me.Requery" works fine in the tagged check box's on click. Unfortunately it also has the disadvantage of moving the form back to the top, which I don't want it to do.

Any ideas?? I think this one is worthy of a

 

[JamesMcS]

Update: I've also tried "Me.ReCalc" but it too moves the form back to the top of the list.

 

[JamesMcS]

Another update: The code I wrote to export whatever's tagged to Excel also suffers from this one-behindedness. Dammit!

So does that mean that the table's not getting updated when the check box gets checked?

Of course.... I need to save the record On Click. I'll have a crack at that now.

Success. Self high five! (or a clap, I suppose)

 

[vbaInet]

So does that mean that the table's not getting updated when the check box gets checked?

The record is dirty after making a change so (like you've figured out), it needs to be saved.

If you're going to use Sum(), I'll recommend this instead:

=Sum(IIf([Tagged],[AEM CY-1],[COLOR=Red][B]Null[/B][/COLOR]))

 

[JamesMcS]

What would the difference be with Null as opposed to a zero?

 

[vbaInet]

Null is neglected in the summation, 0 is included. A miniscule difference in performance.

 

[JamesMcS]

Ah, I gets ya. Thanks!

 

[boblarson]

Update: I've also tried "Me.ReCalc" but it too moves the form back to the top of the list.

Just a note - Me.Recalc, in and of itself, does not change the records positions or move the record pointers. So, if it does when you used it, something else was causing the problem.

 

[JamesMcS]

Hmmm strange.... I must have had a bit of code somewhere else - hey ho, the last chunk of code I put here seems to work, slthough slowly - is there a way to get a whole record written at once, rather than cycling through the recordset's fields individually? It's not so bad when there's only a few lines, but obviously the more records there are, the longer it takes...

 

[vbaInet]

Save and Execute an Update query.

 

[JamesMcS]

I'm replying thinking this is another thread... eejit... thanks for the input though both!

Coffeeeee.......