I have a textbox on a form (txtCurrentBill) where I would like to display the user's current bill.
I have globally declared the the unique Account number (lngBarNo) based on the logged in user.
I would like to reference the Transactions table (tblTransactions) and display the Sum of the Amount field in tblTransactions for the given account number and where the Invoiced field = No
I tried using a SQL statement but this didn't work too well :(
Any idea what's wrong with my code or is there a better/cleaner way to do this?
Thanks in advance
Dim mydb As Database, rsEnq As Recordset
Dim sqlCurrentBill As String
sqlCurrentBill = "SELECT tblTransactions.BarNo, Sum(tblTransactions.Amount) AS SumOfAmount, tblTransactions.Invoiced " & vbCrLf & _
"FROM tblTransactions " & vbCrLf & _
"GROUP BY tblTransactions.BarNo, tblTransactions.Invoiced " & vbCrLf & _
"HAVING (((tblTransactions.BarNo)= '" & lngBarNo & "') AND ((tblTransactions.Invoiced)=""No""));"
' Create database.
Set mydb = DBEngine.Workspaces(0).Databases(0)
' Create dynaset.
Set rsEnq = mydb.OpenRecordset(sqlCurrentBill, DB_OPEN_SNAPSHOT)
' Populate text box controls.
On Error Resume Next
Me![txtCurrentBill].Value = rsEnq.Fields("[SumOfAmount]").Value