Student Progress Database Application

Hmm, I need to see the SQL for the Append query. And I need you to check it. Enter the date then Open the query In Design Mode. Does it have any rows with Blank dates?

Yes, I did the indexing the way you just described and no duplicates were added either, yet I get these blanks in the tblStudentProgress table. Do you know why ? I deleted everything in this table before indexing.

Of course, each time you press that button another set of records will be added to the table. That's what an Append query does! Those first 4 records were probably already in the table. 

So you need to delete all the records for that Center and month. and then run the query ONCE. But the good news is the query is clearly working the way it should. 

Now we do need to add some stuff to prevent duplications if someone accidentally presses the button. So what we do is open tblStudentProgress in Design mode. Click on the Indexes icon on the ribbon. We are then going to add a multi-field unique index. So in the Index Name column (1st column) enter Unique as the name In the Field column on the same row, Select StudentID. then go to the next row, leave the index name blank and select CenterCode in the field Column. Repeat for CourseID and GradeMonth so it should look like this:

Unique   StudentID

               CenterCode

               CourseID

               GradeMonth

Now go back to the row where the index name is entered and set the index to No Duplicates. This will prevent adding another record for the same combination of StudentID, CenterCode, CourseID and GradeMonth. Save the table.

Then you can go back to your form and hit the button again. You should get a message that x number of records could not be added due to key violations. You will also see that tne number of records in the second subform did not increase.

Scott, I was able to run the query but I think it needs a little bit tweaking. When I run the query on its on, it gives me 4 records for each student. But when I use it on the form by clicking the Add Progress Records button, then open the tblStudentProgress and look at the results, it has multiple records for the same StudentID and not 4. Every time the button is pressed it adds another set of records for the CenterCode. And this is not the only problem, the first 4 rows are always empty, then the from the 5th row, the months appear in the tblStudentProgress.

That's correct, an Action query cannot be used as a RowSource. Who said anything about doing that. So you did something that was not in my instructions.

The Append query is saved as a named query. I would name it qryAppProgress. Are you sure you added a command button and not a combobox? They are not the same. The command button control is a small rectangle with xxx on it. If the Command button wizard starts when you place the Command button, then cancel it. That will leave the Command button for you to enter the properties. 

As I said the Caption property should read Add Progress Records. Then go to the Events tabl and click the ellipses next to the On Click event. You will be prompted to select Code Builder. This will open the Visual Basic Environment (VBE) where you can enter code. 

The code to enter is what I gave you. Again, RowSource doesn't come into it at all.