Unanswered: Append Query Duplicating to Orginal Table
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.
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.
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.
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.
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.
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
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.
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.
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.
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!
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.
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!