Solved

Update a subform after append

Posted on 2006-06-14
6
422 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:SSGWillson
6 Comments
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16904489
This should do it:

[YourSubformName].Requery()

M@
0
 
LVL 10

Expert Comment

by:LenaWood
ID: 16904508
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:

Me!SubFormName.Form.Requery

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).

Lena
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16904581
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.

Nic;o)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:SSGWillson
ID: 16913978
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 17035272
SSGWillson,

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.Requery
And
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.

tblAWARDSELECT
StudentID, (Primary Key)
FirstName
LastName
...ect

tblAWARDS
AwardID (Primary Key)
AwardName
...ect

tbljtnStudentAwards
StudentID (Primary Key, Indexed Yes, Duplicates OK)
AwardID (Primary Key, Indexed Yes, Duplicates OK)
AwardDate
...ect

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17053208
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.
http://www.experts-exchange.com/Community_Support/
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now