Update Multiple Tables with one Form

I am trying to create a small database to help me keep track of my soccer team's stats. I am working on the form to input games stats. I have a Main form with 2 subforms. The main form I can select a previous game or enter a new game. The first subform brings up the team roster in a datasheet format. The second subform is supposed to bring up the game stats for the player chosen in the roster subform. It works okay if there is data in the gamestat table, but gives me an error of “The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-edit the record.”, when I go to enter stats for the first player. – any tips for using a single form to update multiple tables?

You know, I recently made a form to feed 3 tables just as you're trying, and it looks like not much different. Maybe this set up will work for you.
The main form is for the transaction table (date, day, store number, score, etc), and the subform has the list of employees (or players) with a date related to the main form's table, but the list is queried into a drop down list. There are some other input boxes for fields for this table, but the important thing is to get the list of people. When the employee is choosen, it queries the subform within this subform which displays the employee's (player's) details for that particular shop (game) fed by the third table, at which time you can add a new game's stats if needed.
Hope that helps. Obviously, make sure all the tables are related, but mostly, I think if you change the first form to form view and insert the second subform into it, maybe have that one datasheet view, it'll do exactly what you want.
Good Luck!

I can't seem to get the listbox to be populated with just the team's roster, it is listing all the players from all the teams.

can you create a query for each (if needed) team's players? For example, LastName, FirstName, PlayerTeam and the criteria under the team would be "DesiredTeam". If you're writing the select statement, I think it would be something like;


SELECT FirstName, LastName, PlayerTeam
FROM tblPlayers
WHERE PlayerTeam = "desiredTeam";

My SQL is a bit rusty but you should be able to do this is Access, in query design veiw. Once you've got the results that you want from the query (the correct roster) save it as something appropriate, and when going through the list box (or drop down list) use that query to obtain the info to populate. If you've got this far, and the next subform is related, it should hook right up.

Thanks, I don't know what I did different today than yesterday, but I have the listbox populating correctly today.

How do you pass information from the listbox so I can link the second subform? I'm having trouble pulling information out of the listbox. Does this have to be done with code?

Nah, I did it completely with the access wizard. In my case, the list is populated with first names and last names, but is also the employee ID which is not visible (this option was default when going through the wizard, wanting to hide the PK). The subform within, was of information about work stuff that was related to each employee via the EmployeeID. So upon adding a new record, the list box doesn't showe a name, then pick a name, and as soon as one is picked, the related info of the new subform displays (in datasheet view) all previously input data. In my case, details about said previous "transactions". Click the "new record" button on that subform, and I can input more data, feeding the 3rd table. Hope that kinda makes sense.
I attached some "print screens" in a word doc showing the form, relationships, and db window so you maybe able to put it together. The main form supplies the tblStoreShops, the nest subform supplies the tblEmpShops, and the second subform supplies the tblEmpShopDetail.

I'll be checking back

OK, I couldn't attach the file (too big) so go to http://tjfdesigns.com/PrintScreenDB.doc