Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New Problem - Unable to Update Form Data (Access 97)

    Well, with help from you, I have my data entry form working exactly the way I want it to run--except now I can't add or edit any of the data! I know the problem stems from adding a query to the record source for the form, because if I remove it, everything is fine. But I don't know specifically what the problem is. I think that perhaps I need to use some DSum calculations instead of what I've done with the series of queries (below) that build the qryTotalsDue information, but I'd really appreciate it if someone could confirm this before I invest a lot of time in trying to change things around. DSum is a new function to me, and I'm a little unsure how and where to use it. So if that is the solution, where do I put it? Does it go in the record source design, or do I put them in the text fields themselves? The last parameter, where the examples show ID=ID, do I need to equate the value in a text control to a field or fields from two tables or queries?

    Please let me know if you need any additional information. The form collects registration information for a conference, including (on subforms) selections for meals, tours, and souvenirs. The text boxes that I added, that you helped me update using a requery, show the totals due for each category, then the total due and the total paid (with balance information). MANY thanks!

    --Karyl

    This is the record source for the form (with the fields that don't matter left out for simplicity):

    tblAttendees and qryTotalsDue
    SELECT tblAttendees.BadgeID, tblAttendees.LName, tblAttendees.FName, tblAttendees.EarlyReg, tblAttendees.Donation, qryTotalsDue.RegFee, qryTotalsDue.Souvenirs, qryTotalsDue.Tours, qryTotalsDue.Meals, qryTotalsDue.TotalDue, qryTotalsDue.Paid
    FROM tblAttendees LEFT JOIN qryTotalsDue ON tblAttendees.BadgeID = qryTotalsDue.BadgeID
    ORDER BY tblAttendees.LName, tblAttendees.FName;

    This is the SQL statement for the qryTotalsDue query (used in the recordset for the form):

    SELECT qryRegTotals.BadgeID, qryRegTotals.FullName, qryRegTotals.RegFee, qryRegTotals.Donation, IIf([MTotal] Is Null,0,[MTotal]) AS Meals, IIf([TTotal] Is Null,0,[TTotal]) AS Tours, IIf([STotal] Is Null,0,[STotal]) AS Souvenirs, Sum([RegFee]+[Donation]+[Meals]+[Souvenirs]+[Tours]) AS TotalDue, qryPaid.SumOfAmount AS Paid
    FROM (((qryRegTotals LEFT JOIN qryToursTotals ON qryRegTotals.BadgeID = qryToursTotals.BadgeID) LEFT JOIN qrySouvenirsTotals ON qryRegTotals.BadgeID = qrySouvenirsTotals.BadgeID) LEFT JOIN qryPaid ON qryRegTotals.BadgeID = qryPaid.BadgeID) LEFT JOIN qryMealsTotals ON qryRegTotals.BadgeID = qryMealsTotals.BadgeID
    GROUP BY qryRegTotals.BadgeID, qryRegTotals.FullName, qryRegTotals.RegFee, qryRegTotals.Donation, IIf([MTotal] Is Null,0,[MTotal]), IIf([TTotal] Is Null,0,[TTotal]), IIf([STotal] Is Null,0,[STotal]), qryPaid.SumOfAmount
    ORDER BY qryRegTotals.FullName;

    And these are the queries from which qryTotalsDue is made (I could probably combine some--or all--of these into one query, but I do better by not trying to do too many things at once):

    qryMealsTotals (qrySouvenirsTotals and qryToursTotals follow the same format using their respective table data):
    SELECT DISTINCTROW tblMealDetails.BadgeID, Sum([amount]*[qty]) AS MTotal
    FROM tblMeals INNER JOIN tblMealDetails ON tblMeals.MealID = tblMealDetails.MealID
    GROUP BY tblMealDetails.BadgeID;

    qryRegTotals:
    SELECT tblAttendees.BadgeID, [LName] & ", " & [FName] AS FullName, Format(IIf([EarlyReg],30,35),"$#") AS RegFee, tblAttendees.Donation
    FROM tblAttendees
    ORDER BY [LName] & ", " & [FName];

    qryPaid:
    SELECT tblPayments.BadgeID, Sum(tblPayments.Amount) AS SumOfAmount
    FROM tblPayments
    GROUP BY tblPayments.BadgeID;

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem - Unable to Update Form Data (Access 97)

    Total Queries (Group By) are per definition not updatable. Each record is the result of a calculation (sum, count, average, ...) on the records of the underlying table (or query). Calculated fields are not updatable.

    As for Dsum if you want to repalce the query :
    qryPaid:
    SELECT tblPayments.BadgeID, Sum(tblPayments.Amount) AS SumOfAmount
    FROM tblPayments
    GROUP BY tblPayments.BadgeID;
    use as controlsource of the textbox =
    =DSum("Amount","tblPayments","BadgeID = " & Me.BadgeID
    This assume that BadgeID is numeric and that there is a control with BadgeID on your form. If you don't want the user see it, set his visible property to False No
    another sample :
    = DSum("[RegFee]+[Donation]+[Meals]+[Souvenirs]+[Tours]","tblAttendees","BadgeID = " & Me.BadgeID)
    Francois

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem - Unable to Update Form Data (Access 97)

    Well, it took most of the afternoon, but I got it all redone. I had to write new queries that didn't include any calculations but still had the necessary values for the various DSUMs. It got pretty convoluted by the time it was done, but it WORKS!! It isn't as fast as the way I was doing it before, but I can update the data, which is the whole point, after all. So, many thanks--again!

    Here is a sample of the new control source for the Meals Total text box on the form (in case anyone else searches here looking for an answer to a similar problem):

    =IIf(DSum("[Amount]*[Qty]","qryMeals","[BadgeID] = [Forms]![frmRegistrationForm]![txtBadgeID]") Is Null,0,DSum("[Amount]*[Qty]","qryMeals","[BadgeID] = [Forms]![frmRegistrationForm]![txtBadgeID]"))

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem - Unable to Update Form Data (Access 97)

    =IIf(DSum("[Amount]*[Qty]","qryMeals","[BadgeID] = [Forms]![frmRegistrationForm]![txtBadgeID]") Is Null,0,DSum("[Amount]*[Qty]","qryMeals","[BadgeID] = [Forms]![frmRegistrationForm]![txtBadgeID]"))

    This can be simplified as :
    =Nz(DSum("[Amount]*[Qty]","qryMeals","[BadgeID] = [Forms]![frmRegistrationForm]![txtBadgeID]") ,0)
    Francois

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Problem - Unable to Update Form Data (Access 97)

    Definitely much easier! I think I knew this function at one time, but I forgot about it. One of the hazards of being out of the business for a couple years!

    Funny, I couldn't find information about it in Access XP using any of the Help options, although it is listed in the expression builder. But it came right up in Access 97 (which I'm using for this project) when I looked in the Help Index. I'm sure it is in XP Help somewhere, but they sure made it hard to find!

    Again, many thanks!

    --Karyl

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: New Problem - Unable to Update Form Data (Access 97)

    You are observing the gradual degradation of Access Help from being helpful to being hardly useful! Let's hope the next iteration improves it some.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New Problem - Unable to Update Form Data (Access 97)

    Maybe Microsoft is finally going to position Access as a developer product and so they're making the help unusable to scare off the end users. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    On second thought, that wouldn't explain why the HTML help is useless in the rest of Office, would it. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

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