Unbound sum record problem

[ailyn]

Hi!
I have an several unbound boxes, that calculate the final price for an order. The first one gets the subtotal from the subform the second one discounts the reduction percentage, the third one adds the VAT and the last one gets the transport costs and adds them getting the total.
It's pretty simple. The 'totalctrl' gets inserted into a table with an update SQL in the VBA when you save the order.

the problem: although the 'totalctrl' shows the exact value when you try to save it into the table it only saves the transport costs instead of the total. I converted the totalctrl into a string and add it to the update but it doesn't work, any ideas why this might be happening? My code is:

Dim strSetTtl As String
Dim strTotal As String
Dim stLinkCriteria As String
stLinkCriteria = "[ProformaId] =" & Me![ProformaId]
strTotal = Me.ttlCtrl.Value
strSetTtl = "UPDATE Proformas " & _
"SET Proformas.[TOTAL] ='" & strTotal & "' " & _
"WHERE '" & stLinkCriteria & "' ;"
DoCmd.RunSQL strSetTtl

 

[ZmrAbdulla]

Try
[tt]Dim strTotal As StringCurrency[/tt]

Or double ?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[ailyn]

Thanks Zameer!

I just realised that the update was not recognising the ProformaId for the Where value so I changed it too.

The problem is that it doesn't save the appropriate value yet. You click on save once, it saves the transport costs, the second time you try it saves the right amount. ?.? why? I tried both with double and currency, the same results:

Dim strSetTtl As String
Dim strTotal As Double 'or Currency
Dim stLinkCriteria As String
stLinkCriteria = "[ProformaId] =" & Me.ProformaId.Value & ""
strTotal = Me.ttlCtrl.Value
strSetTtl = "UPDATE Proformas " & _
"SET Proformas.[TOTAL] ='" & strTotal & "' " & _
"WHERE " & stLinkCriteria & " ;"
DoCmd.RunSQL strSetTtl

 

[JerryKlmns]

ailyn,

strTotal is numeric and you should not use ' in this line

"SET Proformas.[TOTAL] ='" & strTotal & "' " & _

but

"SET Proformas.[TOTAL] =" & strTotal & " " & _

 

[ailyn]

thanks for the help, Jerry but I get the same results.

 

[ailyn]

anyone can help on this one, pleeaaase?!

 

[ZmrAbdulla]

Pls post all relevent code behind the form..

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[ailyn]

This is the code for the button that is supposed to set the info:

Private Sub SvProforma_Click()
On Error GoTo Err_SvProforma_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'set Total in Proformas
Dim strSetTtl As String
Dim strTotal As Currency 'or Currency
Dim stLinkCriteria As String
stLinkCriteria = "[ProformaId] =" & Me.ProformaId.Value & ""
strTotal = Me.ttlCtrl.Value
strSetTtl = "UPDATE Proformas " & _
"SET Proformas.[TOTAL] = '" & strTotal & "'" & _
"WHERE " & stLinkCriteria & " ;"
DoCmd.SetWarnings False
MsgBox strTotal
DoCmd.RunSQL strSetTtl
MsgBox "Record Saved"

Exit_SvProforma_Click:
Exit Sub

Err_SvProforma_Click:
MsgBox Err.Description
Resume Exit_SvProforma_Click

End Sub

It only works the second time, the first one it saves the FreightCharges only.

the controls that get the value are:

RECOUNT: =((ProformasSubform.Form!Subtotal)-((ProformasSubform.Form!Subtotal)*(Nz([Discount]))))

RERECOUNT:
=((Nz([recount]))+((Nz([recount]))*(Nz([VAT]))))

&

ttlCtrl:
=(([rerecount])+(Nz([FreightCharges])))

I hope its clear enough. It shows the exact value but when you click on ttlCtrl the first time it loads the value of the FreightCharges for a second and then shows the total as it should.
I don't understand why because I have the same controls on another form and they work perfectly.

 

[ZmrAbdulla]

Try commending out the DoCmd.DoMenuItem...

Private Sub SvProforma_Click()
On Error GoTo Err_SvProforma_Click

    [COLOR=green]'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/color]
    'set Total in Proformas
    Dim strSetTtl As String
    Dim strTotal As Currency 'or Currency
    Dim stLinkCriteria As String

also see thread702-1163653 for another way to store the calculation from the form to table.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[ailyn]

Sorry, Zameer already tried but the result is the same.

 

[ZmrAbdulla]

Did you try the thread that I have directed..

________________________________________________________
Zameer Abdulla
Help to find Missing people
Seek counsel of him who makes you weep, and not of him who makes you laugh.

 

[ailyn]

Sorry I didn't but now I did and it didn't work. I always get only the FreightCharges. I'll have to recheck all the values one by one, because otherwise I don't see what could be wrong.

 

[ailyn]

Could it be some mysterious option of the form? I copied and pasted the same boxes from another form and they show their values but keep having the same problem with the freightcharges. Any ideas?

I tried this simple code but in this form nothing seems to work!
Private Sub Form_Current()
Dim val1 As Integer
Dim val2 As Integer
Dim val3 As Integer
Dim val4 As Integer
Dim rslt As Integer
val1 = (Nz(Forms.Proformas.ProformasSubform.Form!Subtotal.Value))
val2 = ((Nz(val1)) - ((Nz(val1)) * (Nz(Me.Discount.Value))))
val3 = ((Nz(val2)) + ((Nz(val2)) * (Nz(Me.VAT.Value))))
val4 = ((Nz(val3)) + (Nz(Me.FreightCharges.Value)))
Me.Totalctrl.Value = val4
End Sub

 

[ailyn]

Well, I solved it by setting the TOTAL value from tblProformas in the ctrl Totalctrl and setting its value from the previous form. I really couldn't get it to work otherwise.

Thanks a lot for your help, guys!