Count(*) in form

[Judy]

I am trying to "Do While" based on the number of records on a form.
There is a text box on the form CNT which has a control source
=Count(*). This accurately displays the record count. I then set a
variable Cntr to be equal to the number of records. I had a bunch
more code, but then I realized the cntr was not initializing so i took
everthing else out. I added another test text box to my form (Text22)
to verify that the counter was not initializing. Two odd things: 1) I
use this exact scenario elsewhere and it works fine. 2) If the form is
opened already it works.

I have included my code and would be very grateful for any help.
Thanks. Judy


Private Sub Command48_Click()
Dim Cntr As Integer

DoCmd.OpenForm "frmMYVAr", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "frmMyVar", acFirst


Cntr = Forms![frmMyVar]![CNT]
Forms![frmMyVar]![Text22] = Cntr

Do While Cntr > 0


Forms![frmMyVar]![ActForYtd] = "55"


DoCmd.GoToRecord acForm, "frmMYVar", acNext
Cntr = Cntr - 1
Loop


End Sub

 

[Dirk Goldgar]

I am trying to "Do While" based on the number of records on a form.
There is a text box on the form CNT which has a control source
=Count(*). This accurately displays the record count. I then set a
variable Cntr to be equal to the number of records. I had a bunch
more code, but then I realized the cntr was not initializing so i took
everthing else out. I added another test text box to my form (Text22)
to verify that the counter was not initializing. Two odd things: 1) I
use this exact scenario elsewhere and it works fine. 2) If the form is
opened already it works.

I have included my code and would be very grateful for any help.
Thanks. Judy


Private Sub Command48_Click()
Dim Cntr As Integer

DoCmd.OpenForm "frmMYVAr", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "frmMyVar", acFirst


Cntr = Forms![frmMyVar]![CNT]
Forms![frmMyVar]![Text22] = Cntr

Do While Cntr > 0


Forms![frmMyVar]![ActForYtd] = "55"


DoCmd.GoToRecord acForm, "frmMYVar", acNext
Cntr = Cntr - 1
Loop


End Sub

I think the problem is that aggregate functions in controlsources are
executed asynchronously, so it's probably a timing issue: the control's
value hasn't been computed yet when you check it immediately after
opening the form. And since the form is already on the first record,
your "DoCmd.GoToRecord ... acFirst" doesn't delay things enough.

If you need the record count, it's probably safest and easiest to get it
from the form's RecordsetClone:

With Forms![frmMyVar].RecordsetClone
.MoveLast
Cntr = .RecordCount
End With

But what are you trying to accomplish with your sample code? It looks
like the whole thing could be accomplished by executing a single update
query, along the lines of

CurrentDb.Execute _
"UPDATE <recordsource of frmMyVAr> " & _
"SET ActForYtd = '55'",
dbFailOnError

I understand that your sample code may be much simplified over what
you're really trying to do, but still the approach of looping through
the records on a form just to update them in code seems much less
efficient than just updating the records in the table where they are
stored.

 

[Judy]

Thanks Dirk. I am obviously a novice. Record sets are a mystery to me.
I use forms so i can write little bits of code at a time and make sure
they are working. I'll get out my "For Dummies" book and see if I can
come up to speed in record sets, because that will ultimatley be the
most efficient way to go. Much thanks. Judy

 

[Dirk Goldgar]

Thanks Dirk. I am obviously a novice. Record sets are a mystery to
me. I use forms so i can write little bits of code at a time and
make sure they are working. I'll get out my "For Dummies" book and
see if I can come up to speed in record sets, because that will
ultimatley be the most efficient way to go. Much thanks. Judy

You're welcome, Judy. The main thing to remember is that forms do not
hold data, they're just windows on the tables that actually hold the
data. Forms provide a means for the user to view and edit the data, but
for manipulating data in code, other mechanisms are much more efficient.
Generally, executing action queries (e.g., update, delete, and append
queries) is most efficient, and recordset operations are less so -- but
still more efficient than manipulating the Form user-interface object.

Good luck in your learning! It will be very rewarding. And come on
back to the newsgroups when you have questions. Google Groups is a
great tool, too, since most questions you might have have been asked
before.