fire code every Nth record

[martinmike2]

Hello,

I am trying to figuer out a way to fire some vba every 10th new
record. I am wondering what the best way to do this is? I am
thinking of storing the new records in a temp table until I hit the
magic number and then fire the code, but that would leave my form bare
until i get to 10, then fire the code to remind the user to print a
report, then append the records to the main table and del them from
the temp table.


Like I said, I am looking for a better way to do this. Any and all
ideas will be apprecated.

 

[BruceM]

I can think of several ways, but the parameters are not clear. If this is a
multi-user database, is it every tenth record for that user, or every ten
records total by all users?
What constitutes a new record? In Access terms a new record is one that has
been created but not saved. However, by going to another new record you
save the previous one, so there is only one new record at a time.
Would you print the "new" records, or what exactly? In other words, is the
report to contain only the "new" records?
Why ask the user to stop and print a record? What if they don't do that?

 

[martinmike2]

I can think of several ways, but the parameters are not clear.  If thisis a
multi-user database, is it every tenth record for that user, or every ten
records total by all users?
What constitutes a new record?  In Access terms a new record is one that has
been created but not saved.  However, by going to another new record you
save the previous one, so there is only one new record at a time.
Would you print the "new" records, or what exactly?  In other words, isthe
report to contain only the "new" records?
Why ask the user to stop and print a record?  What if they don't do that?








- Show quoted text -

sorry, I should have ben more specific. it is going to be a single
user system. Bussiness rules dictate making the user stop and print a
report every ten records. By "new" I ment the last 10 records saved.
There will be more than ten records in the main table, but i can just
use the MAX() function to pull the last ten.

 

[Clifford Bass]

Hi Martin,

One possible way would be to use the form's open and after update
events. Create a variable that is global to the form's module that keeps
track of the next record count at which you want to print. In the form open
event set it to the current number of records in the table plus your print
interval. In the after update event, check to see how many records are in
the table and if equal to or more than the next print count, do the print
routine and increase your next print count by the print interval.

Clifford Bass

 

[BruceM]

You could put an unbound text box (txtCount) on the form. In the form's
Load event:

Me.txtCount = Me.Recordset.RecordCount

Then in the form's After Update event:

Dim lngCount As Long, lngStart As Long

lngCount = Me.Recordset.RecordCount
lngStart = Me.txtCount

If lngCount - lngStart >= 10 Then
' Reset txtCount
Me.txtCount = Me.Recordset.RecordCount
MsgBox "Print now"
End If

You could print automatically instead of generating the Print Now message,
if you prefer.

There may be cases where Recordset.RecordCount does not work as expected in
the form's Load event. If this happens you could do:

Me.RecordsetClone.MoveLast
Me.txtCount = Me.RecordsetClone.RecordCount

I wish I could remember more about why this approach may be indicated in
some cases, but all I have is a vague recollection that using the
RecordsetClone.RecordCount may be more reliable.

I can think of several ways, but the parameters are not clear. If this is
a
multi-user database, is it every tenth record for that user, or every ten
records total by all users?
What constitutes a new record? In Access terms a new record is one that
has
been created but not saved. However, by going to another new record you
save the previous one, so there is only one new record at a time.
Would you print the "new" records, or what exactly? In other words, is the
report to contain only the "new" records?
Why ask the user to stop and print a record? What if they don't do that?








- Show quoted text -

sorry, I should have ben more specific. it is going to be a single
user system. Bussiness rules dictate making the user stop and print a
report every ten records. By "new" I ment the last 10 records saved.
There will be more than ten records in the main table, but i can just
use the MAX() function to pull the last ten.