Results 1 to 7 of 7

Thread: DAO troubles

  1. #1
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33

    Unanswered: DAO troubles

    I'm having some trouble with a coding process that is probably very simple. I have a form (named Form) which is linked to a table (Jobs). There are two subforms present on the form containing two tables, Payments and Billing. What I'm trying to do on the main form is calculate the total billed and total paid and display them in text boxes (txtTotalPayments and txtTotalBilled). So far I have been able to get the payments to calculate and display properly and billing will calculate and display properly too but only if there is a value in payments. Not sure why this is happening exactly, here is my code:

    Private Sub Form_Current()

    On Error GoTo Err_Form_Current

    Me!txtTotalPayments = "$0.00"
    Me!txtTotalBilled = "$0.00"

    Dim txtTotal As Currency
    Dim strSQL As String
    Dim rs As DAO.Recordset

    Dim txtTotal2 As Currency
    Dim strSQL2 As String
    Dim rs2 As DAO.Recordset

    strSQL = "SELECT Payments.JobID, Sum(Payments.Amount) AS TotalPayments " & _
    "FROM Payments GROUP BY Payments.JobID " & _
    "HAVING ((([Payments].[JobID])=" & Me!JobID & "));"

    strSQL2 = "SELECT Billing.JobID, Sum(Billing.Amount) AS TotalBilled " & _
    "FROM Billing GROUP BY Billing.JobID " & _
    "HAVING ((([Billing].[JobID])=" & Me!JobID & "));"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    txtTotal = rs.Fields(1)
    Me!txtTotalPayments = txtTotal
    rs.Clone
    Set rs = Nothing

    Set rs2 = CurrentDb.OpenRecordset(strSQL2, dbOpenDynaset)
    txtTotal2 = rs2.Fields(1)
    Me!txtTotalBilled = txtTotal2
    rs2.Clone
    Set rs2 = Nothing

    Exit_Form_Current:
    Exit Sub

    Err_Form_Current:
    MsgBox Err.Description
    Resume Exit_Form_Current


    I know the problem lies in my lack of knowledge with using the DAO functionality. If I move the code for generating the total billed above the payments code it will always calculate a billing total and will only calculate a payment total when a billing total is present (which is the exact opposite of what it's doing now). I even attempted to write a query so that I would only need to use one DAO recordset which looked like this:

    strSQL = "SELECT Payments.JobID, Sum(Payments.Amount) AS TotalPayments, Billing.JobID, Sum(Billing.Amount) AS TotalBilled" & _
    "FROM Payments INNER JOIN Billing ON Payments.JobID = Billing.JobID " & _
    "HAVING ((([Payments].[JobID])=" & Me!JobID & ")) And ((([Billing].[JobID])=" & Me!JobID & "));"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    txtTotal = rs.Fields(1)
    txtTotal2 = rs.Fields(2)
    Me!txtTotalPayments = txtTotal
    Me!txtTotalBilled = txtTotal2
    rs.Clone
    Set rs = Nothing

    I couldn't get this query to work however, I kept receiving an error saying that JobID was not part of the aggregate function. I don't know if either of these examples is the right way to go about doing this... If anyone has any insight into what I'm wrong here I would greatly appreciate it. Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see if dsum() does what you want:
    me.txtTotalPayments = dsum("amount", "payments", "JobID = " & me.JobID)
    me.txtTotalBilled = dsum("amount", "billing", "JobID = " & me.JobID)

    meanwhile, your DAO: you don't mention any error message - i would have expected .clone to throw an error. is that supposed to be .close ?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    The first portion of code I posted doesn't return an error which I found to be odd. Thanks for the advice, I'll try this and let you know how it works.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    in addition I unless that sql statement returns one record - you still (to the best of my knowledge have to movefirst and cycle through the records to compute the two fields - am I right Izy?

    And if the recordset is designed to return one record you really do not need the dao - use the dsum function as perscribed.

    also I believe the clone should be a close as well.

    are you using on error resume next at the beginning of the code?
    Dale Houston, TX

  5. #5
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    Thank you! The DSum function worked like a charm. I knew I was probably making things harder than they had to be. Out of curiosity I changed .clone to .close (which is what I meant to have there anyway) and still didn't get results (or errors), oh well. Thanks again for the help.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad it worked out!
    DXxxx() functions are useful but don't have them repeating (SQL, loops etc) since they are relatively slow.

    but the behaviour you were seeing was very strange:
    rs.clone should have thrown an error (the syntax i know is something like set rsX = rs.Clone)
    and your two rs are completely unrelated other than sharing the same error handler - you should not have seen an empty result from one killing the attempt to get a result from the other unless you see an error msg.

    possibly your db is heading into a corruption zone. save it, copy it, compact/repair the copy (next level of panic: import everything to a new empty db (next level of panic: /decompile a copy of the db)).

    izy


    and to Dale - yes you still need .movelast to fully-populate a multi-record recordset, but this SHOULD have been one record given the GROUP BY & WHERE combination (ooops, not WHERE but HAVING)
    Last edited by izyrider; 09-11-07 at 12:33.
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    That sounds about right... Unfortunately I've had corruption problems with this db in the past and it looks like its time to compact and repair and/or create a new mdb file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •