Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Question Unanswered: Query is appending duplicates

    Hi, I'm running an append query to update a table after a user enters a value into a field and exits the field. Once the user exits the field, some text boxes are calculated on the continuous form related to the record the user is editing. Then the append query runs to add the calculated fields to the table. Next, the form closes and then reopens so that the calculated text boxes are empty again so that the user can update another record on the continuous form. The problem I'm having is that sometimes the query will append the 5 or 6 fields it is pulling from the form once...sometimes it appends the data twelve times. It seems random just how many times the query appends the data to the table, but I need to make sure it only adds it one time. Any help is MOST appreciated!

    Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The form is bound?

    What triggers the update? Is it On_Current, After_Update, On_Change, something else?

    How are you performing the update (code sample for us)?

    You say "add the calculated fields to the table" - the same table the form is bound to or a different table?

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Ok, yes, the form is bound to a table which holds project information(customer, city, state, etc). The form contains a continuous subform which shows much of the project info from the table. One field on the subform(bound to the table) is % complete(lets call it: total % complete).

    So what happens is the user enters into a text box the % complete as of today(lets call it: % as of this month). Upon exiting that text box, another invisible text box calculates the difference between the box that was just updated and the total % complete box in the table. This invisible box that was calculated is % this month. In addition, two other text boxes calculate what $ value the % this month equates to(revthismonth) and what is remaining(remaining).

    So once the user exits the % as of this month box, a macro is run to calculate:

    % this month
    revthismonth
    remaining

    The macro then sets a few other values before opening an append query which pulls all of the calculated fields off of the subform and appends them to a seperate table(which tracks the % complete each month). Then the query closes; then the form closes, then the form reopens in an effort to clear all of the text boxes which have been calculated so that the user can make another %complete entry for a different project.

    I'm not sure if I covered everything...please let me know what I missed and thanks any help!

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ok, so what's missing is that I need to know what event the update is triggered by - is it On_Exit on the text box the user adds data too? Or After_Update?

    I believe the problem most likely lies in the what triggers the update - if you get multiple updates, the update is probably being triggered multiple times.

  5. #5
    Join Date
    Dec 2007
    Posts
    4
    It runs on After Update..

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I suspect that as you change records getting to your first one and then to each one after that, the event is firing, calculating the values and updating the table.

    To get an idea of when it's posting the updates, go into the code and put a break point in the update procedure. Then run it. Each time it goes to update, you be in the code.

    Try that and let us know ...

    You may want to consider using an unbound form and taking full control of what data goes where and when.

    tc

  7. #7
    Join Date
    Dec 2007
    Posts
    4

    Thanks

    Thanks tc, I'll see if I can figure it out using the methodology you suggested...I'm using Access 97 and I'm really not a programmer, lol. Unfortunately I'm trying to build this for 5 or 6 people to be able to login and use(and have successfully built another version which works great) and they want everything to be a certain way and the only thing standing in the way is this duplicate entry. Anyways, thanks for your help!

Posting Permissions

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