Solved

Displaying a SUM value in a textbox using VBA

Posted on 2008-11-01
3
1,551 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Direct Mail software 4 42
Storing Combo Box Selection in Table 12 41
How to make query not show results in table form go straight to report 4 21
Search Form not Querying 2 10
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now