Resource for using linking tables with forms?

[narayanis]

Hi all,

I have created a database for a friend and am now getting questions about building forms for it (I'm not very familiar with these). The goal is to enter Attendee information and allow the user to select a Course from each of four drop-down lists. My table structure uses an Attendees table(PK AttendeeID), a Courses table (PK CourseID) and a linking table called Attendee_Course.

I can create a form to enter the attendee information, but I can't find a good online resource to help me:
1) display the Courses.CourseDescription field in the drop-downs while setting the value to save to Courses.CourseID
2) save the Attendees.AttendeeID (I can use a hidden form field for this) and Courses.CourseID combination into the Attendee_Course table



Most of my knowledge is in SQL, but I can pick up on things pretty well if anyone can point me to an example of this kind of situation. Thanks!

[Kassie42]

Hi,
I may be able to give you a little help. I am not an expert so forgive my language. First you need to decide on how your tables relate if you are going to use all tables. For example there are one to one relationships, which mean there is one record in one table that matches one record in another table. There are also one to many relationships. This means one table has one record (lets say demographics such as name, address, etc) and the second table will have many records to go with the demographic table. After you decide on the relationships you need to set them. Then you will be able to create forms. With forms you may want one main form or a main form with a subform. The main form can be from one table and the subform from another. You can use the wizard to help you set these up. After it is set up you can go into design view to fine tune. This will allow you to put your drop down boxes etc in. Hope this helps.
Kassie

[narayanis]

the sql would be like this:

insert into attendee_course values
(attendee_course.attendeeid = (form value of attendee id), attendee_course.courseid = (courses.courseid from the drop-down list)

this table structure is pretty standard and i can create a form to enter the attendee information; my problem comes when figuring out how to get the course descriptions into the drop-down and ensuring that it inserts this into the attendee_course table rather than creating new entries in the courses table.

[jya]

If the recordsource of your form is the attendee_course table, the field data should link to the attendee_course table. Add a combobox to the form and the combobox wizard should take it from there.

When done, you can verify this by looking at the property box. The control source will display the linked field. If you look at the dropdown, the only choices will come from the fields that correpond to the form's recordsource. The row source will display an sql statement from your course table.

[narayanis]

I think I did a poor job of elaborating. Here is the desired result:

- A form is presented to the user to enter Attendee information (name, address, status, etc)

- On the form (or a subform), the user is presented with multiple combo boxes. In the boxes, Courses.coursename is displayed but Courses.code is the index value

- Upon completion of the form, the attendee information is saved to the Attendees table. Attendees.code and Courses.code are saved to the Attendee_Course table

When using the wizard I always end up with a form that writes to Attendees and a subform that writes to Courses.

[narayanis]

If anyone is curious, I found the following article on techrepublic that explains the concept of exactly what I needed:
http://articles.techrepublic.com.com...1-5285168.html