VBA To differentiate data from three subforms into one table

[kmerc]

I'm sure this is an easy problem and I couldn't find the answer anywhere. I have three subforms that lead into the same table (It had to be set up this way for the sake of a different query). So I would like to identify which Subform the data came from by entering a 1 2 or 3 in field "Type" of my table which is called "Action Items". I was thinking of using event after update. All of the subforms enter data into the field "Action Item" of "Action Items" table. I hope this makes sense, thanks!

 

[pdevito3]

You can use a "Type" variable for all of the forms. When the user submits the data, you could also code in "Type" as 1, 2, or 3 appropriately.

 

[kmerc]

I don't think I explained that enough. I don't want the user to have to type "1" "2" or "3". And I know what I want to do but I need the code for it. I want it to manually say this data was put in through this subform, therefore it is type 1. And a different sub form is type 2 and the other one is 3.

 

[pdevito3]

The user wouldn't need to do anything different. I'm assuming that on each subform there is some kind of a "submit" or "ok" button to put that data into the worksheet... When they press that button there can be a hard coded variable (ex. type) that carries that number over. For example, the user fills out userform 1 and presses submit. within userform 1 code, there is a type variable equal to "type" (1 for this form). When that button is pressed, the code would enter a 1 in each row that the data is entered into in your type column. Same for 2 and 3 with nothing different for the e

 

[kmerc]

Yeah that's what I want to do but I don't know what the vba code is. Can you help me out with that? Thanks

 

[pdevito3]

I'll need more details... I'll need to know your table setup. Your current code on data submission would also be very helpful.

 

[kmerc]

The form "Frm Lessons Learned Branch Input" is where I'm entering the data from. There are three subforms on different tabs of the form. The one that I want to be type 1 is "Subfrm WWI" Type 2 is "Subfrm Pain Points" and Type 3 is Subfrm SI would like them entered into a table called "Action Items" in the "Type" Column. Data that the user will be entering will be put in the "Action Item" column of the "Action Items" table. I would like the 1, 2, 3 in the type field. I don't have any commands because I want them to submit everything together. I was planning on putting the code in "On Update" event so it still updates but does so immediately when the record is put in. There is a little bit of macro work on the command buttons for the form but not much. The subforms only have one field each (Action Item obviously) so those don't have coding but are the ones that I would like to. I can enter the "On Update" for each of the three subforms. Thanks.

 

[pdevito3]

Wait so are your forms actually worksheets? Regardless, I am realizing a hole in this process and I am having trouble visualizing your final table... how is data from three sheets will all go into the same table as different rows? a data table should normally have one unique field (primary key in a database) that can relate data between various tables. For example, table 1 has field 1 and 2, table 2 has field 3, and table 3 has field 4 and 5. With that, the data would then go to the final table like the below table. I don't get how you could had a type column to say what page each was added from. You're going to need to paint a clearer picture. If you can upload a skeleton copy of the workbook on dropbox, I could get the picture much more easily.

Primary Key	Field 1	Field 2	Field 3	Field 4	Field 5
Same data on all three sheets to link the data	Data from sheet 1	Data from sheet 1	Data from sheet 2	Data from sheet 3	Data from sheet 3

<tbody>
</tbody>

 

[kmerc]

I'm sorry but I don't have a drop box. The primary key is an ID field so that they can all be differentiated. The next field over is type, then the action item, assigned to, due date, date completed, status and a field that links this table to another table. These are all sub values. So when a record is made it goes into a DIFFERENT table. that table contains the name of the event, where it happened, when it happened, etc. But one single event (a tornado for example) Could have multiple action items which is why this is a subform. I need the "type" to say 1 2 or 3 so that later on it can be differentiated between what kind of action item it was. The important part to know is that I need a code that will allow it so that when I type something into one subform it goes into the table "Action Items" AND the value 1 2 or 3 goes into the "type" column. All three subforms lead to the same table. They all put it into the "Action Item" field. But Depending on which subform I'm submitting it from I ALSO want it to add a 1 2 or 3 to the "type" field. I think it will end up being three different codes for all three sub tables on the "after update" event. The code should say "when updated, select table "Action Items" field "type" and put in the number 1". That code will go into the first subform. The next subform will have "when updated, select table "Action Items", field "type" and put in number 2". The third subform will have "when updated, select table "Action Items" field "type" put in the number 3". I need the type to differentiate between which subform the data came from since all three lead to the same field in the same table. There are no worksheets, this is all in access. If you could even just help me write a code for how to select another field in a different table then I could probably do the rest. Thanks. If you could