Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Displaying a SUM value in a textbox using VBA

Posted on 2008-11-01
3
Medium Priority
?
1,565 Views
Last Modified: 2013-11-28
Hi,

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
 
    mydb.Close

Open in new window

0
Comment
Question by:itmtsn
  • 2
3 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 2000 total points
ID: 22857609
Me![txtCurrentBill]=DSum("Amount","tblTransactions", "BarNo= " & lngBarNo & " AND Invoiced='No')

This assumes lngBarNo is a number.
0
 

Author Comment

by:itmtsn
ID: 22858711
When I copy/paste that into my code, I get

Compile error:

Expected: List seperator or )

0
 

Accepted Solution

by:
itmtsn earned 0 total points
ID: 22858774
Managed to get it working, thanks for the help :)
    Dim curX As Currency
    On Error Resume Next
    curX = DSum("[Amount]", "tblTransactions", _
    "[BarNo] = " & lngBarNo & " AND [Invoiced] = 'No'")
    Me.txtCurrentBill.Value = curX

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question