Calculation problem 1

[mru]

Hi Everyone

I have a form which I am creating which will allow me to list an account's numerous invoices and then allocate a payment against them. The form is a continuous form and I have the following fields.

The main fields on the form are the 'supplierID' & 'amountpayingfield' fields, then listed on the subform I have 'invamountoutstand' & 'amountpayingnow'. On the subform footer i have 'totalpaying'.

The code I have working (simplified) is " if the amount of the current invoice outstanding 'invamountoutstand' + 'totalpaying' is equal to or less than the 'amountpayingfield' then 'amountpayingnow'='invamountoutstand'

The problem I have is if I press the payment button or alt P too quickly or if I hold the keys down the payments are set against the invoices straight away and the re-calculation of the 'totalpaying' field doesnt get chance to work. I have tried various requery and refresh statements but the quick pressing of the keys bypasses them all.

I would appreciate any ideas anyone may have on this. I have spent days trying different things but cant find the answer.

Thanks

MU

 

[Darrylles]

Hiya,

I don't understand how you can click a button 'too quickly'.
Too quickly compared with what?

You must be doing something directly beforehand to be able to use the phrase 'too quickly'.

Can you explain further pls?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[mru]

Hi Darrylle

Thanks for your reply, what I mean is - I have a button on the form called '&PayInv'. I dont actually click the button what I do is press alt-P quickly or hold alt-P down.
The code I have working is on the 'onclick' property.
I hope this explains it better,

Thanks again

MU

 

[Darrylles]

Hi,

If you 'click' the button - it works ok?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." AND "A pointer to a solution is better than the solution - you'll remember the solution via a pointer". darrylles@totalise.co.uk

 

[mru]

Darrylle

Yes the button works fine if you click it, but if you click it repeatedly and quickly the effect is the same as holding the keys down.

Thanks

MU

 

[BillPower]

Hi mru,

Try using this function:

DoEvents 'after each instruction/procedure DoEvents tells the operating system to wait for this instruction to finish before going on to the next instruction.

Should work, it's not you being too quick, it's most likely your OS being too slow....

 

[mru]

Hi billpower

Thanks for your suggestion, I tried it last night, unfortunately it had made no difference. I tried placing 'DoEvents' in various places on the code but it didnt work.

I looked up 'DoEvents' in the help file to check if there was anything I was doing wrong and read somewhere that it does not work on certain functions / procedures. Maybe that is the reason.

Any more suggestions would be really appreciated.

Thanks

MRU

 

[mru]

Has anyone got any suggetsions ?

 

[BillPower]

Can you post all the code as it appears behind the Payment Button. Just copy and paste it into the reply window.

Also list all the fields that affect the calculation only, stating whether they are in the main form or in the sub form.

 

[mru]

Thanks billpower,

Here goes, the only field that affects the calculation on the main form is the (purpayamount) field, I have set up a hidden field on the sub form header (in order to make the calculations easier)called 'Paying', this is updated each time the field on the main form is updated.
The fields on the subform that affect the calculation are
'purpayoutstand','purinvpayingnow', 'purinvamountpaid', 'purpayamountpayingtotal and the 'Paying' field.

Private Sub purpaypaybtn_Click()
On Error GoTo Err_purpaypaybtn_Click
Dim dbs As Database
Dim rst As Recordset
Dim response As Integer
Dim Total As Variant
Dim Title

Set dbs = CurrentDb()
Set rst = RecordsetClone
Title = "Payment on account ?"
If rst.RecordCount = 0 Then
response = MsgBox("There are no invoices in this account. Do you want to post this payment as a payment on account ?", vbYesNo, Title)
If response = vbYes Then
DoCmd.OpenForm "frmPurchasePayOnAccount"
Forms![frmPurchasePayonAccount]![suppidcode] = Forms![frmPurchasePayments]![suppidcode]
Forms![frmPurchasePayonAccount]![purinvdate] = Forms![frmPurchasePayments]![purpaydate]
Forms![frmPurchasePayonAccount]![purinvcostnet] = Forms![frmPurchasePayments]![purpayamountpay] * -1
End If
ElseIf Me.purpayoutstand < 0 Then
Me!purinvpayingnow = Me!purpayoutstand
DoCmd.GoToRecord , , acNext

Exit Sub
ElseIf IsNull(Me.Paying) Then Exit Sub
ElseIf IsNull(Me!purpayamountpayingtotal) And Me.Paying < Me!purpayoutstand Then
Me.purinvpayingnow = Me.Paying
DoCmd.GoToRecord , , acNext
DoEvents
Exit Sub
End If

Total = Me!purpayoutstand + Me!purpayamountpayingtotal

If IsNull(Total) Or Total <= Me!Paying Then
Me!purinvpayingnow = Me!purpayoutstand
DoCmd.GoToRecord , , acNext
Exit Sub
Else
Me!purinvpayingnow = (Me!purpayoutstand - (Total - Me!Paying))
Beep
If Me!purinvpayingnow = 0 Then
Exit Sub
End If
DoCmd.GoToRecord , , acNext
DoEvents
End If

Exit_purpaypaybtn_Click:
Exit Sub

Err_purpaypaybtn_Click:

Resume Exit_purpaypaybtn_Click
End Sub

I have left a couple of 'doevnts' in the code and I tried them in other ways as well.

Thanks again

MRU

 

[BillPower]

Hi mru, is this &quot;purpaypaybtn&quot; Button on the main form or the sub form, in your code I can't see any reference to the sub form name.

If it's in the sub form, I don't think it should be.

If it's in the main form, you should be referencing the sub form name.

Can you supply the subform name, as it appears in the main form's property window. Thanks.

 

[Darrylles]

Hi bill,

Sounds good to me.

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk

 

[mru]

Hi billpower and Darrylle

The &quot;purpaypaybtn&quot; Button is on the footer of the subform, I had tried it on the main form first but found it easier to get working when placed on the subform.
The main form is 'frmPurchasePayments' and the subform is 'frmPurchasePaymentsSub'.

Thanks

MRU

 

[mru]

Hi everyone.

I have eventually got it working correctly by using a 'form.recalc' and the doevents.
Thanks for your help

MRU