Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Sep 2013
    Posts
    16

    Unanswered: Append Query Duplicating to Orginal Table

    Hello,

    I have searched google (as well as this site) for a good hour trying to find out what I am doing wrong here. Here is the set-up

    I have a table tbl_Activities which has data about marriage activities a couple must go through. Then I have a table tbl_Default_Activities with a standard set of activities a couple may have to go through (32 in all)

    I have a form frm_Activities that displays all current events for a couple. on this form I have a button that run an append query which pulls all fields from tbl_Default_Activites and pulls the couple_ID number from the form that is open and appends it to tbl_Activities. This seems to work flawlessly, yet it also writes the same information (including the couple_ID) back to tbl_Default_Activities as well.

    I thought about hard coding all of the defaults into VBA but I figured if they ever wanted to modify the list that would be to much effort.

    Any ideas would be quite welcome. I double checked all of the record sources to make sure its not pointing back at tbl_Default_Activities. I even delete the records from one table and it deletes them in the other... I am really not sure what's going on.

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Maybe I'm not understanding you, but is not the form based on tbl_Activities? If not, what is the record source for this form and does it have a subform? If you posted at least a screenshot of the qry it might help. I don't understand the append qry! More details are needed to provide help.

  3. #3
    Join Date
    Sep 2013
    Posts
    16

    Sorry for delay

    Sorry for the delay. Had to get back into the office to gain access to my work >_<

    Yes the form is based off of tbl_activities. The append query is based off tbl_Default_Activities. I haven't really used append queries before (never had the need) so I am more than a little confused. I have attached (or tried to?) a screenshot of the append query, and less helpful the form itself.

    If it just wrote back to itself it would make more sense. But the fact that it writes to both tables seems odd. There is no fancy code, the fill default button simply runs a macro to open the append query.
    Attached Thumbnails Attached Thumbnails Activities Form.jpg   Append Query.jpg  

  4. #4
    Join Date
    Sep 2013
    Posts
    16
    Ok, revise that statement, the form is based on tbl_Couples which is the over all link on all records, then it has a subform that is based on tbl_Activity_Records (messed up the names of my own tables in the previous posts....) which keeps track of the couple_FK and the activity type, and the dates of the activity. tbl_Activitiy_Records is then in theory appended to from tbl_Default_Activities.

    Maybe I have gone about this whole thing the wrong way, who knows.

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    I still don't see the need for a append qry as you are only using the same table.Just save the record and the data will be updated.I also don't see a 2nd table in your append qry, so it is impossible to append to a 2nd table this way! If the data is already stored in 1 table, why would you want to store it again in another table? Maybe a sample db would help.Of course remove all confidential data, compile and zip. Sorry for not understanding your dilemma.

  6. #6
    Join Date
    Sep 2013
    Posts
    16
    The append query is mainly so I can add 32 records with one button press. And so if they decide to add 2 new activities its just a matter of changing the default table.

    I will strip down the database and share it here in the near future. Forgive how poorly constructed it is >_<

  7. #7
    Join Date
    Sep 2013
    Posts
    16
    Ok, here is a stripped database. All forms and tables are still there just confidential data removed. As I said I apologize that it is so sloppy. Its been about 8 years since I made my last database so I was a little sloppy with naming conventions and normalization...

    To get to the form in question the Navigation form pops up first. Click the activities record button, that will open the Records form based on the Couple_Pk. The button is question is the Fill Default in the top right.

    Related pieces are as follows.
    TABLES: tbl_Activities_List, tbl_Activities_Records, Default_tbl_Activities_Records, Couples

    Queries: Append_Activities_records

    FORMS: frm_Wedding_Activities, sub_Activity_People, sub_Activity_Add, Sub_Activity_Activitylist
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2013
    Posts
    16

    Doh

    126 views and no leads >_< I guess I will try isolating variables in a new database.

    If anyone has a better way to fill a "many" record with sets of default data please let me know.

    -Greg

  9. #9
    Join Date
    Nov 2011
    Posts
    413
    I downloaded your db. I converted your macros to vb. I see the popup form and it adds 1 record at a time.I assume you want to add multiple records at a time, is this correct? If so, why open the form with 1 ID and not multiple ID's? If you could walk me thru with a little more detail, I will try and help you.

  10. #10
    Join Date
    Sep 2013
    Posts
    16
    I am not to familiar with append queries so I will try and explain the form the best I can.

    The form is based on Couples, which is my "one" the subform is based on activities, which is my "many" I have 2 ways to edit data on the form. First is a bar (at the middle) that allows you to add one single record at a time. This is so that you can make a completely custom form line by line. These added records are sorted into order by numbers in their underlying table.

    The second method for updating the form is the "fill default" button at the top. This is to add multiple records to the "many" record set for the "one" recordset. This fill default button simply runs an append query from Default_tbl_Activities with a target of tbl_Activities. When you first create an append query it asks what table is the target, and that is where I selected it. Then it asks what field you would like to copy information from, and to what field you want to copy it too. So the fill default button has to create 32 new records and link them through the Couple_FK to the original person. The fill default button currently has no coding other than to run the append query.

    I think I could hard code all of this record creation into VBA (I am novice but have some passing understanding of the language) but the problem with that is then if the list changes it has the modified in VBA...

    I appreciate you trying to help me ^_^ I know it can be difficult to sort through novice explanations lol.

  11. #11
    Join Date
    Nov 2011
    Posts
    413
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Append_Activity_Records", acViewNormal, acEdit
    DoCmd.SetWarnings True
    Me.Requery
    This code eliminates the prompt for the append qry if that is what you want. If your wanting to add multiple people at a time, seems to me, a listbox would work better.

  12. #12
    Join Date
    Sep 2013
    Posts
    16
    I don't want to remove the promt, I like that it adds a "double check" and I don't want to add multiple people, though maybe a list box could be used to add multiple events to one person... I will have to check into that.

  13. #13
    Join Date
    Nov 2011
    Posts
    413
    Are you saying that if the list, that is, the fields in the append qry will have to be changed? Normally if a list changes, you simply do a requery to see the latest data. If your field names changes for the append qry, then yes, you would have to edit this each time, although if this is true I don't understand it? Maybe I am just not getting it!

  14. #14
    Join Date
    Sep 2013
    Posts
    16
    Field names are all identical. Basically I am trying to copy the data from the default table to the "activities" table and only changing the data in one field the couples field.

    That way the same generic data will be copied but still allow me to set dates and time for that specific couples activities.

    Imagine you have a form that 9 times out of 10 has mostly the same data, just different dates. I am just trying to find a way to duplicate that generic data but still be able to customize that form for the 1 time out of 10.

    Maybe it is a feature I will sideline for a later date ^_^ Later being forever lol.

  15. #15
    Join Date
    Nov 2011
    Posts
    413
    So, you just need a way to check for a new value and if it exists, then update with the new value, YES? If so, then one way would be to create a new field and then add whatever value you want and if it is not null then use That value, else use old value!

    Do I understand?

Posting Permissions

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