Student Progress Database Application

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?

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.

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?

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 an Unbound Textbox on the Main form and gave it a label of Month name. Then I went ahead and changed the query to reflect the CenterCode from the combo box like you said. Since I only see one query in my database, so I changed the query to the Center Code, if this is the query you are talking about.

"One last thing is to add a column to query:

Mth: Forms!mainformname!controlname

where controlname is the name of the unbound control you just added for the month.

Now when you run the query, it should show records for the Center currently being displayed on the main form with a field for the month."

I didn't understand this part above quite well. Are you talking about the same query that I added to reflect the Center Code wiz. Forms!MainFormname!Controlname which is the combo box? And where do you want me to add the column for the month ? Please clarify these in greater detail.

Thanks.