Populating a Field with info from another table

[Joyce Fife]

I keep getting a syntax error when I try to create the procedure to insert the information selected from the list box into the main database. The data in the list box comes from a separate table with only 2 field. The user can check the selections in the form, but the selections aren't saving in the main table. The Form allows user entry to populate the "Colleges" table. The "Majors" table has a list of majors that appears on the form. Users can check the ones that apply. I want the items checked to be inserted in the (majors_field) in the colleges table. How do I accomplish this?

[NeoPa]

The form wizard should help with this Joyce. It seems you're approaching it in a common, but flawed way.

If the form is to update the [Colleges] table then create a form that maintains (Add/Edit/Delete/Etc) that table. This form would need a ComboBox control from which you could select an ID of one of the items in the list. You shouldn't be thinking of copying two fields across as that goes againtst the concept of Normalisation which is fundamental to database design (See Database Normalisation and Table structures).

[Joyce Fife]

Thank you for your reply. Perhaps I wasn't clear.

The form I am using is created to add/edit/delete, the "Colleges" table. I'm simply using the list from the "Majors" table as a ComboBox, and attempting to allow users to make a selection that gets saved in "colleges". Right now users can make the selection but it is not being stored in either table. The ID selection is also not being saved in the "Colleges" table so the [major] field in that table remains blank when I go to view it.

I have already added a field in this form that allows users to "add" a degree major to the "Majors" table, and also updates the [major] field in the "Colleges" table.

[NeoPa]

If the ComboBox control (not field) returns a [Majors] ID then simply bind this to the [Major] (ID) field of the form (whose Record Source is [Colleges]). You should probably understand that forms have controls and fields, but these items are not synonymous. A control (TextBox; ComboBox; Label; etc) shows data on the form, while a Field holds the data as part of the Record Source recordset.

Joyce Fife:
I have already added a field (control) on this form that allows users to "add" a degree major to the "Majors" table, and also updates the [major] field in the "Colleges" table.

It's probably not a great idea to manage a separate table on the same form, but what you say implies you already have a solution. If you can already maintain the [Major] field of [Colleges] why are you asking how to add new ones?

[Joyce Fife]

OK, thanks again. I will look into the "binding" step. The intent is that the [Colleges] table is updated with the selected major from the [Majors] table, but that's not happening when I attempt the updating using the form. It does update when I use the Datasheet View and make the selection.

I'm sure it's something simple that I've done wrong or overlooked, but without experience it's been difficult knowing where to check for my mistake.

[NeoPa]

Forms are bound to record sources by specifying them in the form's Record Source property. Controls are bound to fields by specifying the field in the control's ControlSource property.

To understand the process better the ComboBox is simply setting the value of the bound field to the value selected from the ComboBox. When this is on a New record then that value will be saved into the record as [Colleges].[Major]. Does that make better sense?