Pulling information from one form to fill field on another

[tjones]

I am attempting to pull information from one form (course taken) to another form (Paper) and fill the information from the course form to the exact same field on the paper form.

Not sure which form / field to enter the code on. What I attempted was going to the course form on grade field:

Private Sub Grade_AfterUpdate()
'to auto fill the practicum form with course info
If Me.Capstone = 3 Then
[Forms]![Course Paper]![Grade] = Me.Grade
End If
End Sub

and on the Paper form on Current:

Private Sub Form_Current()
'to auto fill the info from the course form
If [Forms]![Course Taken].Capstone = 3 Then
Me.Grade = [Forms]![Course Taken]![Grade]
End If
End Sub

 

[Pat Hartman]

Create a query that joins the two tables. Use that query as the RecordSource for your form. You can then pull columns from both tables and bind them to controls on the form. Access will autofull the "lookup" values when you populate the foreign key field which is usually done with a combo.

Data is not stored in forms. It is stored in tables.

Occassionally, when one form opens another you would want to pass data between them but this doesn't sound like that case to me.

 

[tjones]

Way above my level of knowledge.
1. How do you create a query to join two tables ( tho I do know how to change the record source once it is made)
2. How do you bind them to controls on the form

 

[Pat Hartman]

• Open the query builder.
• Add the tables and/or queries you want to select from.
• Draw join lines to connect the PKs to the FKs.
• Select the columns you need from each table/query.
• Name the query and save it.
• Change the RecordSource of the form to the new query.
• All the columns in the query will be available for selection as ControlSources for the controls so you can bind the controls to the RecordSource.

 

[tjones]

OK Got it working sort of.

1 Create Query in Design View and open table you want to pull the information from
2. Choose Append! in design view and select the table you want the information
inserted in (this should be named the same as the form you want to view it in)
3. Below in the query setup Select the fields you want to copy (append from) in the top row
**the Append to fields should auto fill if they are the same name otherwise select the correct field to fill to
4. fill the criteria that makes it fill only when the correct trigger is chosen .

Now the problem is that i f you are not in the correct line of the split form on the practicum form (etc) it shows the three fields of the line you are in and not the one you want.

 

[tjones]

Now the "Join" part comes into effect. How do you join so that it ONLY shows in the secondary form when when (for practicum) capstone = 3?

 

[Pat Hartman]

2. Choose Append! in design view and select the table you want the information
inserted in (this should be named the same as the form you want to view it in)

No - you need a select query. An append query adds rows to a table. You want to select rows to view on a form.

Now the "Join" part comes into effect. How do you join so that it ONLY shows in the secondary form when when (for practicum) capstone = 3?

This is selection criteria. Go back to the query and in the column for capstone, add the 3 to the Criteria line.

 

[tjones]

More relevant information: The course form is a split form that contains courses available. if the course is a "capstone" you select the it hence Practicum = 3. the Practicum form opens off the course form via cmdbutton I want the Term,Year,Grade fields to fill from the information on the course form/table but there are also more fields that need to be filled on this form that is unique to the Practicum course.

I really appreciate all the help, I know that I am really new to Access and Queries are hard for me to understand so lets take this one step at a time.

• Open the query builder.
  Ok this I do know how to do
• Add the tables and/or queries you want to select from.

Do I need to open both tables? Courses and Practicum?

• Draw join lines to connect the PKs to the FKs.

OK I think you are saying: In Courses (main form) select

"Term,Year,Grade" fields and draw lines to the Practicum (secondary

table) to the same fields?

• Select the columns you need from each table/query.

This would be down below so Term,Year,Grade (but from which table?)

• Name the query and save it.

easy enough

• Change the RecordSource of the form to the new query.

So I thought i knew how to do this but don't and that would be the record

source for the secondary (Practicum) form?

• All the columns in the query will be available for selection as ControlSources for the controls so you can bind the controls to the RecordSource.

Is that the control source on the form property sheet? and then as for the bind controls to the recordsourse I am entirelly lost

 

[Pat Hartman]

We need to back up a bit. The tables need some work. I can't figure out what Course Practicum is supposed to be. A practicum is a type of course so it would be an attribute but never part of a table name. I thought the table might be the definition of a course but it doesn't contain any attributes that support that hypothesis. I also can't figure out what Course Taken is supposed to be although its name sheds more light on that one. However, it is missing an important attribute and that would be studentID.

In any event Term, Year, and Grade do not belong in both tables. A typical schema would be:
tblCourse
CourseID
CourseTitle
CourseDescription
DepartmentID
etc.
tblCourseSchedule
ScheduleID
CourseID
InstructorID
SchoolYear
Semester
MeetingLocation
etc.
....
tblStudents
StudentID
FirstName
LastName
etc.
....
tblStudentCourses
StudentID
ScheduleID
FinalGrade
etc.
...

 

[tjones]

OK the table for Course Taken contains the course info
CourseTaken
Student ID (which ties all tables) DeptID, CourseID, Professor, Term, Year, Grade, etc. all relevant info

the Practicum table
contains the info unique to a Practicum, such as proposal date, defense date, title, etc. In the interest of not having to look at different forms I would like to fill the TYG fields on the practicum form so all information is there.

I have it in both places as the course taken record needs this information to be complete but would like it in the practicum so all infor related is there.