CODEWITHSUNDEEP
sqlms-accessvba
Sam 'Evilsam' Spiller
Sam 'Evilsam' Spiller

Reputation: 129

IF the sum of a column is <= 0 then

I am making a till system for a project in access and I have hit a block.

Background There are numerous forms covered in buttons. clicking a button adds data to a table (TblCurSale) including description and price of item. each form also has a "total" button which sends you to the payment screen, doing so copies the data from the TblCurSale to another table (TblCalc)

TblCalc has columns SaleID, Item(name of item), SalePrice. the report auto adds the sale price column

The total form has two sub reports on it, the TBLCurSale and TblCalc.

On the total screen there is a text filed in which users an input money and then press pay which inputs that figure into the TblCalc as a negative number and then refreshes the page so the new total comes up. at the bottom of the subreport.

Problem I need an IF vba code so that I can put it so that when the total of the SalePrice column <= 0 I can run a few lines of code. what I have so far is below, so any help would be greatly appreciated.

Private Sub Pay_Click()

Dim SQLPay As String
Dim SQLToTable As String
Dim SQLMoney As Variant

SQLPay = "INSERT INTO TblCalc(SalePriceTotal) VALUES (-'" & TxtPayment & "')"
SQLToTable = "INSERT INTO TblTotalSale (CurrentSaleID, SalePrice, Item) SELECT CurrentSaleID, SalePrice, Item FROM TblCurrentSale"
SQLMoney = "IF (SUM(SalePriceTotal) FROM TblCalc) <= 0 SELECT '1' ELSE '0'"

DoCmd.SetWarnings False
DoCmd.RunSQL SQLPay
DoCmd.RunSQL SQLMoney

If SQLMoney = 1 Then
DoCmd.RunSQL SQLToTable
Me.TxtPayment = ""
Me.Refresh
DoCmd.OpenReport "rptCalc"

Else

Me.TxtPayment = ""
Me.Refresh
Me.Refresh
End If

DoCmd.SetWarnings True

End Sub

Upvotes: 1

Views: 256

Answers (1)

ChrisPadgham
ChrisPadgham

Reputation: 870

I think you would be better to structure it like a transaction table. So instead of inserting the payment into a separate table, add another row to the TblTotalSale with an Item description of "Payment" and the value as a negative number. You can then simply sum the SalePrice column to give you the balance outstanding. It also allows you to record multiple payments against the one sale.

wrt to the negative sale, I think you should put some validation code on your form to prevent users from entering negative item prices, (in the beforeInsert and beforeUpdate events on the form)

Upvotes: 1

Related Questions