Student Progress Database Application

The query I'm referring to is the SELECT query that returns one record for each student for each course within a selected Center. Since you have tested that query and it worked we are now modifying the query to make it more dynamic so it will reflect the data you want to append to the table.

First, I would not use the Search combo as the criteria. The search combo may not reflect the currently loaded Center. You should use the CenterCode control in the Detail band area.

Second, to add a column go to the first blank column and enter the alias and the expression in the top row. The alias is Mth: the expression is the reference to the unbound control you added.

Okay, now I added a exp:Mth:Forms!mainformname!controlname in the top row and then ran it. As soon as I ran it, it displayed the name of the month in all the rows reflecting what I put in the text box on the main form. But when I tried to add marks to StudentID's and then save the changes, the tblStudentProgress is not reflecting the name of the month in the Month field. What's wrong?

Don't get ahead of yourself. This query is not for entering the data, its for setting up the rows where the data will be entered.

So when you run the query, it has 4 records per student, one for each course. It displays only those students in the Center currently displayed on the form and it shows the Month you entered on the main form for all records, correct? If so, then we proceed to the final step. 

Open that query in Design view. On the Design ribbon will be an option to make it an Append Query. Select that Option and it will prompt you to select a table to Append to. You will be Appending to tblStudentProgress. A row will be added to the grid that says Append To. Some of the columns may be filled in with the corresponding fields in tblStudentProgress. You need to make sure that the StudentID, CenterCode, CourseID and Month columns have their corresponding fields set. I'm not sure if you need anything else but those four. If you are unsure, list the fields in tblStudentprogress again for me and I'll advise.

The last thing you need to do is add a command button to the main form. The Caption for that button, will be Add Progress Records. Use the Code Builder to add the following code to the On Click event of the button:

DoCmd.OpenQuery "queryname"

Me.subformname.Requery

queryname is the name of the Append query we have saved and subformname is the name of the second subform where you will be entering the scores.

After clicking the button, the second subform should be populated with a record for each student, for each course, for the Month entered. You can then go down the subform, entering the scores.

Hi,

I did everything exactly as instructed by you but there is an error message which says, "An action query cannot be used as a row source."

I think this will not allow us to use the Append query for the command button. Any ideas?

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.