1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003 Visual Basic

Discussion in 'Business Applications' started by woodsinvt, Jan 24, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    I am trying to use a form to update a table each time I process rates. I want to delete the old information and update with the new. The following is what I have now:

    Dim StDocName As String
    StDocName = "Process Rates"
    DoCmd.Open Query "Delete Old Rates", acViewNormal, acEdit
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.RunMacro "Proc Rates"

    The Process Rates Append Query appends the amount of a bill to the Fees table.
    The ProcRates macro opens the Process Rates Query.
    I want to delete the old readings and append the new ones without having duplicates.
    When I run now, I end up with duplicate readings each time I run the button on the form. I sometimes have to run several times to update the information in the view form.

    Thank you.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If the query doesn't do what you want, the best way to update data is using a VBA Recordset.
    Can you post a screenprint of your Table Relationships?
     
  3. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    Attached is the screen print for the relationships. I hope it's there.

    Thanks.
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry, I can't open Word 2007 docs, only Word 2003 and earlier
     
  5. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    Sorry about that, attached is the screen print in .doc format
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, which table & fields are you trying to update?
    Can you provide an example of how the "Rates" change?
     
  7. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    I'm trying to update the Fees and Readings table. The fields in the Fees table are Id, TotalBill, Date. The fields in Readings table Id, AcctNo, Date, XDate, Reading, XReading. An example of a rate change would be in a situation where I changed some readings and want to update the Fees and Readings table without adding duplicates. I have managed to put together a Delete Readings and Delete Old Rates queries to delete the duplicates from the Readings and Fees tables (separately). My goal is to have a form, macro, whatever is needed to do all this automatically. The Process button on the Process Rates form has the On Click property running the ProcRates macro which opens the Process Rates Append Query.

    I have also discovered that the amount of the bill is calculating incorrectly using the current rates. It's off by $11.52 (less). The current rate is in the Process Rates form in an unbound text box in the Default Value property. I double checked the rate to make sure it was right.

    I hope this helps. Thank you
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am obviously missing something. You say "My goal is to have a form, macro, whatever is needed to do all this automatically." and "An example of a rate change would be in a situation where I changed some readings and want to update the Fees and Readings table without adding duplicates."
    Does your updating Form work directly with the "Readings"?
    Is the Fees tale related to the Readings table via the ID field or aren't they related?

    What is the calculation for the bill?
     
  9. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    Yes, the form does work with the Readings table. The Fees table is related to the Readings table by the ID field. The calculation is reading - xreading = usage times the rate plus $19.50.

    Sorry for the lateness of my reply.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post a zipped copy of your database, it does not need any data in it, just the tables, queries and forms.
     
  12. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    I have attached the database without the data. Hope this helps.

    Thank you.
     

    Attached Files:

  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, I have had a look at the Table Relationships and I have a question.
    Can you get more than one Fees Record for a Readings Record, it does't look as if you do?
     
  14. woodsinvt

    woodsinvt Thread Starter

    Joined:
    Aug 25, 2010
    Messages:
    19
    No, I cannot have more than one Fee for a reading, hence not wanting duplicates.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The reason that I ask that question is because if you can only have one "Fee" per Pair of Readings there is no need ot have the fees in a different table. Just add the TotalBill and the date with a suitable name change to the Readings table. It will make processing that much easier.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/976711

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice