FredixBlog

It is sometimes needed to show the running total of the records. On a report, there is no problem as this feature in built-in. But on a form, a bit of coding is needed.
Here is a function that will work on any form. The most of it is that you can sort or filter the records, the running total still works because it works with the form’s recordsetclone.
Parameters of the function:
[Amountfield] is the field name we want to totalize
[Keyfield] is the field name of the primary key of your form’s table or query, or simply an unique field.

Public Function RunTotal(AmountField, KeyField)
Dim SumRs As Recordset
Dim frm As Form
Dim RunningValue As Double
Dim IsCancelled As Boolean

Set frm = Forms(AmountField.Parent.Name)
Set SumRs = frm.RecordsetClone

With SumRs
    ' Setting first amount
    RunningValue = 0
    On Error Resume Next
    .FindFirst "True"
   
    ' Summing all other
    While .Fields(KeyField.Name) <> frm.Controls(KeyField.Name) And Not IsCancelled
        RunningValue = RunningValue + .Fields(AmountField.Name)
        On Error Resume Next
        .FindNext "True"
        If Err <> 0 Then IsCancelled = True
        On Error GoTo 0
    Wend
       
    ' Do the current one
    RunningValue = RunningValue + .Fields(AmountField.Name)
End With

Set SumRs = Nothing
RunTotal = RunningValue
End Function

• AddReference()
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Dirty Flag for unbound form
• Custom Autonumber Access 2000
• Create ODBC DSN

Quertion: I am trying to create a custom autonumber, due to an upgrade from a previous approach database that used a double as the autonumber and primary key.

Got to the first session, paused. Try to contimue with F8. The record has been locked by the other session, the record can’t anymore be updated with a pessimistic lock.

here is the code I used to generate the autonumber. The table I created contains only one field (autonum).

This will be used in a multi-user environment in Access 2000 SR-1 runtime
and I need advice on how I can ensure an error will not kick the user out.

I tried to simulate simultaneous use but did not get an error as I have
expected to.

Is their a better way to test it before finding out the hard
way, it don’t really work?

Is their a better way of doing this?

Any suggestions will be appreciated.

Here’s my code

Module Code:

Public Function GetAutoNum()
'Returns Job Number from autonum table in autonum field.
'then adds one to autonum.
Dim num As Double
Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "autonum", CurrentProject.Connection, adOpenDynamic,
    adLockPessimistic
    rst.MoveFirst
    num = rst("autonum")
    rst("autonum") = num + 1
    rst.Update
    rst.Close
    Set rst = Nothing
    GetAutoNum = num
End Function

Form Code:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.jobnum = work.GetAutoNum
End Sub

Thanks in advanced
-Adam

With a regular lock, this is the second session that will fail to update.

So, here the locking method doesn’t matter, but you have to add error trapping code according to your locking scheme. When failed, the rst.update should retry and the appropriate code should be added. Here the code for retries you will add shall work on both lock schemes.

Regards.

atrout77 asked this follow-up question on 6/5/2002:
Thank you, this is the type of answer I hoped to get. I knew I should have gotten an error when i tried to simulate the situation, but for some reason or another it didn’t
(I tried to have another user insert a record at the exact time I added it, probably didn’t do it at the exact same time so no error occured as I expected)
Fredix gave this response on 6/6/2002:
Yes, try it. Your code just need a few more lines. Tell me how it goes.

I will try this as it is a great suggestion, just wonder why I didn’t think of it, thanx again.

 

Also, in my previous post, replace pessimistic by optimistic.

The optimistic lock fires a lock for the user who first updates the record.

With your code, the lock method doesn’t makes any difference, as the transaction should be very quick.

atrout77 asked this follow-up question on 6/10/2002:
Ok, I tried what you suggested and recieved an error as i should have, so now I can trap it.

I however never really wrote code to perform a retry of something and was wondering if i should use a loop (To time the next retry) and retry then or it their a better way?

Can you give an example?

Thanks again,
-Adam

Fredix gave this response on 6/10/2002:
Sure,

Yes, this is just a small loop to write. Here is one, which will replace the rst.Update line:

Dim Retries as Integer
Dim OtherError as string
...
    Retries = 0
    On Error Resume Next ' No break when error occurs
    rst.Update
    While Err <> 0 and Retries < 10 ' We will retry 10 times
        Retries = Retries + 1
        rst.Update
        OtherError = Error$
    Wend
    On Error Goto 0 ' Back to normal error break
    If Retries = 10 then
        Msgbox "Update was unsuccessful.", OtherError
    EndIf

10 retries is a very large number. 3 could be enough, it all depends on the potential number of users.
I added the OtherError variable, in case you have another error (disk full, disk error, duplicate record, etc)

• Répartir les tâches du crontab unix
• Bon Jovi not dead opening a restaurant will be there for me
• La Serre 1 – Le trou
• Get Jet Connection String
• Opening an ADO recordset
• Check if file exists
• Running total on a form
• Replace()