auto populate fields in subform

Question

Have tables, 1.Student Info and 2. COLLEGECOURSEINFO 

Created form Student Info input form  with a subform  COURSEINFO 

some of the fields on the sub form are the CourseNumber, CourseTitle, CourseCredits, Perequisites and Discipline. When i get to CourseNumber, I look this up from a combo box  created from a  table that contains these 5 fields.  After i select the CourseNumber  i would like the other 4 boxes to auto fill with the information already provided in table. 

Currently to help me with inputting the data i created a pop up form that contains the CourseNumber, CourseTitle, CourseCredits, Perequisites and Discipline. I put a link to this form about the subform so When i am entering info in the Student Info Input Form i open the pop up form, see the information i need and enter that into COURSEINFO subform

i can't figure out how to get it to auto fill the other fields once CourseNumber is selected. 

thank you

Answer 1

In the college information subform I would expect there would be a combo box to select the primary key from the courseinfo table. The combo box might have a Row Source like:

SELECT [Course Number], [Course title], Prerequisites, [Course Credits], [Discipline] FROM CourseInfo ORDER BY [Course Number];

You can make 1 or more columns visible using the column widths property. The bound column would be the primary key from the courseinfo table.

To display the other information from CourseInfo in the subform, add text boxes with control sources like:

=[Your Combo box Name].Column(x)

"x" is the number of the column. Column(0) is actually the first column.

Answer 2

I don't usually have more than one table storing the same info.

I have 2 tables; studentinfo and collegeinfo

They have a one to many relationship

I created an input form from the studentinfo table as the main form and the collegeinfo table is the subform.

I  tried entering sample information, adding student demographic information on the main form and in the subform I added college information such as; current semester, is this their last semester, course title, course number, prerequisites (just yes or no), course credits, discipline (such as visual arts, geography, medicine and health services etc.), auditing or taking course for credit (drop down list with audit or credit being the selections), number of students enrolled in course, final grade (if applicable), note field, and a few other needed fields.

When I got to the fields for the course information (which will remain constant) I found myself having to look up in different places the course title, course number, prerequisites (just yes or no), course credits, discipline over and over again.

so I created a table to store this information and created a pop up form from it. On the form I added a button to open the pop up form that displays all the bolded information so the person entering the information can open the form and use this information to populate the fields in the subform.

I tried putting course info in a third table, courseinfo table.  I can get it to work fine if it had just one field ,like looking up the course title, but I need the other 4 fields as well.

Since I couldn’t figure it out I just created this pop up form to make entering the data easier on the person who has to do it.

Hope I explained it well enough for you to understand what I am trying to do.  just trying to help someone out who currently keeps some of this information in an excel spread sheet and types some of the same student information over and over for each semester.

Answer 3

This is a normalization issue.  Apart from the foreign key, the only non-key columns in the subform's table should be those which are functionally determined solely by the table's primary key.  Otherwise the table would not be normalized to Third Normal Form (3NF), and consequently open to the risk of update anomalies.  In most cases the values of no other columns from the referenced  table, apart from the key, should be repeated in the referencing table.  The values of the columns in the referenced  table can be shown in the subform, however, either by referencing the combo box's Column property as Duane describes, or simply by basing the subform on a query which includes the referenced  table, joined on CourseNumber.  You can then bind text box controls in the subform to the relevant columns in the referenced  table.  The Locked property of these controls should be set to True (Yes), and their Enabled property should be set to False (No) to make them read-only.

In those exceptional cases where it is necessary to repeat a non-key column, e.g. a UnitPrice column from a referenced Products table in an OrderDetails table, then the price looked up from the Products table should be assigned to the UnitPrice column in the OrderDetails table in the product combo box's AfterUpdate event procedure.

You'll find examples of both scenarios in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the section on 'Inserting data via a form/subforms' incudes a simple Orders form.  In this, when a customer is selected in the bound combo box, the customer's address is shown in the unbound controls below the combo box, by referencing the combo box's Column property.  The combo box's RowSource property is:

SELECT Contacts.ContactID, Contacts.Address, Cities.City,

Regions.Region, Countries.Country,

[LastName] & ", " & [FirstName] AS Customer

FROM (Countries INNER JOIN Regions

ON Countries.CountryID = Regions.CountryID)

INNER JOIN (Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID)

ON Regions.RegionID = Cities.RegionID

ORDER BY Contacts.LastName, Contacts.FirstName;

It's ColumnWidths property is 0cm;0cm;0cm;0cm;0cm;8cm to hide all bar the last column, the contact's name.

In the OrderDetails subform on the other hand the current unit price of the selected item is assigned to the UnitPrice column with the following code in the item combo box's AfterUpdate event procedure:

    ' assign current unit price from Items table

    ' to UnitPrice column in OrderDetails table

    Me.UnitPrice = Me.ItemID.Column(1)

This means that while the price of an item will change over time in the referenced  Items table, the price current at the time the order was made will remain static in the OrderDetails table.  In each table the UnitPrice column is functionally determined solely by the table's primary key, so both tables are normalized to 3NF, and there is no redundancy.

Answer 4

You probably don't want to store the same information in multiple tables. I CourseNumber is the primary key from your course table, this is the only value that should be stored in the related table. You can display the other fields by using text boxes with control sources like:

=[YourComboBoxName].Column(x)

x is the column number from the combo box row source. These are zero based so the 2nd column is actually 1.