Checkbox afterupdate issue

[khurgan]

Hi,

I'm simply trying to show the user the number of records he has checked on his form.

I thought I'd use AfterUpdate on the checkbox, but it seems that the recordset that I get is not up to date with the checkbox that triggered the event (afterupdate)... and I don't understand why?
I have to call a ShowAllRecords or Requery to have the exact number of records with the checkbox checked (=-1) but doing so, my form is repainted, and the user is taken at the top of the list (which is unfortunate!)
My code looks like :

Dim rs_nb
Set rs_nb = CurrentDb.OpenRecordset("SELECT count(Num_Mdat) as nb FROM tbl_mandats WHERE b_chk = -1;")
   If rs_nb.nb = 0 Then
      Form_frm_mdat.btn_term.Enabled = False
      Form_frm_mdat.txt_nb.Visible = False
   Else
      Form_frm_mdat.btn_term.Enabled = True
      Form_frm_mdat.txt_nb.Visible = True
      Form_frm_mdat.txt_nb.Value = rs_nb.nb & IIf(rs_nb.nb = 1, " mandat", " mandats")
End If

'tbl_mandats' is the data source for this form...

Why am I always missing the current record in my query? If I have 3 checkbox checked, it shows 2 until I requery...

And ultimately, how do I get to show the user how many checkbox are checked (again, without going through the requery)?

I'm still using Access 2002, sp3.

Thanks in advance

 

[spikepl]

AfterUpdate on the checkbox does not mean that the new record has been stored. It means that the field has been dirtied with a new value. You need the form's AfterUpdate event to get hold of the new values. Alternatively, in the on-click you could explicitly trigger a save.

 

[vbaInet]

=Count(IIF([FieldName] = -1, 1, Null))

If you put this in the Control Source of a textbox located in the Header or Footer section of the form, it will show you how many checks they are.

 

[khurgan]

Thanks for the replies;
But I still have the same results with both solutions: I'm always 1 count behind... And the problem is the same if I uncheck the box.
I'm probably doing something 'very' wrong because I can't find anyone else having the same problem on any forum!!!

 

[Taruz]

Hi..


refresh 'try adding it..

Dim rs_nb
Set rs_nb = CurrentDb.OpenRecordset("SELECT count(Num_Mdat) as nb FROM tbl_mandats WHERE b_chk = -1;")
If rs_.....
...........
...................
............................

 

[khurgan]

OK, the count is good as soon as the control (checkbox) loses focus... It is not as 'real time' as I would like it to be, but I mightl try to switch to another control and come back to the original one...

 

[khurgan]

Yeah! The Refresh in the checkbox afterupdate works wonderfully!
Thank you Taruz!! (And vbaInet and spikepl too)

 

[vbaInet]

I hope you understand that by refreshing you are continuously commiting to the dbase.

 

[gemma-the-husky]

in the after update event put this first

runcommand accmdsaverecord

this will save the record, and now your dcount will work correctly

 

[boblarson]

in the after update event put this first

runcommand accmdsaverecord

this will save the record, and now your dcount will work correctly

I'm with Dave on this. Use the save record command INSTEAD of using REFRESH. There is less impact on the database, the network traffic, etc.