Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Displaying a SUM value in a textbox using VBA

Posted on 2008-11-01
3
Medium Priority
?
1,560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

721 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