• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Update a subform after append

I have a form which with command button that runs an append query and adds a record from my main table to a different table depending on what record is selected on the form (I have [forms]![frmAWARDRECIP]![NAME] in the criteria of the append query). On this form I have a subform of the "recieving" table. For some reason the record doesn't show on the subform until I close the form and come back in, is there some code I can add to the command button to have it refresh the subform? Please use exact VB. Thanks for your help.
1 Solution
This should do it:


First of all NAME is a reserved word and you should avoid using it as a field name.

If the button is on your main form use:


Change SubFormName to the name of the subform.  Make sure that you use the name given to the subform on the main form...they could be different (look at the perferences for the subform while the main form is in design view).

Sometimes the requery won't work, in such a case use:
Me.subformname.form.recordsource = Me.subformname.form.recordsource
Looks funny but will work always.

One remark however about copying the record to another table. In general I would use a (status?) field and just change that instead of moving a record. This saves the INSERT and will allow the selection of the rows by testing that (status?) field.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

SSGWillsonAuthor Commented:
Okay, I can see that that is an easy fix, but for some reason those solutions are not working. Hee is what I have so far:

Main query qryAWARDSELECT where records are students attending a course. Primary key is STUDENTID.

frmAWARDRECIP is based on this query and there is a combobox from an AWARDS tabel that stores the value selected in a field in the query [AWARD]. I made a new table (tblAWARDRECIP) based from the fields I would need to produce the rptAWARDS that didn't have a primary key so that a student could be selected more than once.

I have a command button that runs an append query for the current record and adds it to the tblAWARDRECIP. All of this works fine. I would like to add a subform to the form so that the user can see what students they have selected for what awards. Tell me the best way to go about this. Thanks again.
Jeffrey CoachmanMIS LiasonCommented:

Let's deal with the original question first:
<is there some code I can add to the command button to have it refresh the subform?>

Me.subformname.form.recordsource = Me.subformname.form.recordsource
...Are the solutions you have been given

So if you say:
<those solutions are not working>
... Can you define "Not Working"?

Now for this Second, (New) question.

From what I can tell, you now need a "Junction' Table.

Because one student can receive more than 1 award AND 1 award can belong to Many students.

StudentID, (Primary Key)

AwardID (Primary Key)

StudentID (Primary Key, Indexed Yes, Duplicates OK)
AwardID (Primary Key, Indexed Yes, Duplicates OK)

Now in the relationship window, Join tblAWARDSELECT to tbljtnStudentAwards on StudentID
Then Join tbljtnStudentAwards to tblAwards on AwardID

Now create a Form based on all three tables, so as you say, the users can:
<see what students they have selected for what awards.>

Use the form Wizard
Select  tblAWARDS  first
Select the Fields you want
Then Select the  tblAWARDSELECT  table
Now select the fields you need from this table
(No need to select fields from the junction table)

Now when you open this Form, It will contain a Main Form (tblAWARDS) and a SubForm containing the Students (tblAWARDSELECT )

When you select an Award from the Main Form a list of students who received that award will appear in the subform. (You can also reverse this to show One student and their many Awards)

Hope this helps
Jeffrey CoachmanMIS LiasonCommented:
Glad I could help

If you like you can contact the support staff and request a points split between myself and the first three repondents.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now