Form no longer updates table; what'd I do?

[SevenSignz]

Evening all!



I've been working on an "Enter Scores" type of form for a database that's used in a competition. What should happen, is I pick a team from a combobox, enter their score in a text box, then upon the form's close, a separate table should have a record added with the team's number and score. However, somehow after changing a few things, this doesn't happen anymore.

I should mention that this is a maintenance problem, as this database was complete and used in previous competitions, but the user needed it to be slimmed down. I'm an Access newbie, but everything has been going fine until this. There were never any macros or events for "onClose" and the only things I changed was where the data for the combo box came from (to my knowledge at least). Oh, and I'm using MS Access 2007.

So my question boils down to, how can I tell what happens when the form closes? If this isn't enough info, please let me know what you need. I can even upload the database if you'd like. Thanks!

EDIT:The problem unfortunately evolved. Now it does update the table, but the identifying field, Team #, defaults to 0 no matter which record I pick from the combo box.

EDIT 2: Problem solved! The Record Source of my form had to be changed to the table it was updating. Thanks for the help!

[June7]

Provide file for analysis. Attach to post.

[SevenSignz]

Oh, hello June! I didn't realize these forums were connected.

Here's the database before I touched it, and after I made changes. Please disregard any specific info discrepancies; I had to censor some personal info.

https://skydrive.live.com/redir.aspx...IP83IRMIVD3MFk

[June7]

They aren't connected, I just visit both. It's a courtesy to note that question is in other forums and provide link so people don't spend time on an issue if solution has been provided somewhere.

Which form is the issue? Anything special I should know about how to interact with it - should it be opened from main menu?

[SevenSignz]

It's the "Enter Brief Scores" form. Nothing special, just right click->Design View from the navigation pane. What should happen is you enter pick a team, enter a score, then on form close it should populate the Briefscores table with respective values. (Briefscore ID is an AutoNumber generated on population).

+Also, the Briefscores table has to have a unique Team # field. That's why the defaulting to 0 is a problem because it adds one record then can't overwrite it so it errs. To test, you have to delete all records from the table for the best results.

[June7]

I thought you said the Before project worked? It won't play nice with me. I try to open 'Enter Brief Scores' from the Brief Menu and the code errors. I think it has something to do with Open event macro which wants to go to new record but some reason it can't.

I don't use macros, only VBA. I remove the macro call from Frm - Enter Brief Scores and changed VBA Click event for Command44 to: DoCmd.OpenForm stDocName, , , , acFormAdd

Now form opens to new record. But won't save entries. Says creates duplicate record. What is supposed to be the relationship between the two tables in this form's RecordSource query? The Tbl - Briefgraders to link has RdrTmID set as Autonumber data type but the Indexed property is Yes (Duplicates OK). I thought this was impossible for an Autonumber field, yet there it is! It is not set as Primary key but that is usually the reason for a Autonumber. The RdrTmID field in Tbl - Brief Scores is a number type and it is set as Indexed Yes (No duplicates). I think these settings are backwards.

[SevenSignz]

It must be the macro error, because it opens easily for me. The reason it won't save any entries (possibly at least) is because the table Briefscores already has data for that team number. You have to delete all the records from Briefscores to enter any data from the form. Also, when this DB was built, the user of it would get 5 brief scores per team from 2-3 different brief graders which would then be averaged and points taken depending on violations. This time around, we're just getting the final score so all of those calculating queries are essentially useless. The Briefgraders to Link table's only purpose as of now is to connect the actual scores to a team number which I'm trying to eliminate by cutting out the middle man and just using the Competitors table. That's why the form itself changed so drastically.

The Briefscores table holds the team numbers and their respective scores in order for reports to be generated. It used to be used to calculate final brief score, but that use has been eliminated. The Briefgraders to link table houses the information pertaining to the brief graders (name, what they graded, how many they grade, etc.), which the user doesn't need. Everything in that table serves no purpose so I've been steadily editing other queries and reports so they no longer depend on it. That led me to the Enter Brief Score form. If I can get that working properly without needing the Briefgraders to link table, then the other tasks will be waaaaayyy easier to alter. If by RecordSource query you meant the one used in the combobox, then I just made that so I could have the team number and school name display in one column. The second column is just the Team # (hidden by making it's width 0"), used as the bound column.

I hope this answers your questions. The whole database is being scrubbed by other members of my team, but I'm responsible for the brief scores so that's all I'm concerned about at the moment. This DB is making me dislike its creator more and more...

[SevenSignz]

It's fixed! Turns out I had to switch the Record Source of Enter Brief Score to be the Briefscores table rather than the Valid or Invalid Query. Thank you so much for you help June, I appreciate your timely responses and late night availability!!

[June7]

Glad you found solution. I going down that road, thinking the RecordSource was wrong. And no, I was not talking about the combobox RowSource. Still doesn't clarify the circumstances of the RdrTmID fields.