Hi Barry
sounds like the Combo is the way to go. Well, here's how...
You need 3 tables for this part -- Events, Students, and the linking Enrolments table. Each has its own ID. Enrolments also has StudentID and EventID fields. You can put whetever other fields you need in the tables, but keep them on the topic of the table.
Now for the forms.
First, build an Events form based on the Events table. Leave the bottom half of the form blank for inserting a subform. You'll need the EventID field on eh form or you won't be able to use it for data entry. [frmEvents]
Now build an Enrolments subform with at least the EventID, EnrolmentID and StudentID fields. This should be set to display as a Datasheet. [subEnrolments]
Open frmEvents in Design view, Restore so you can see the database container, and hit F11 to bring the container to the front. Drag the subEnrolments icon onto frmEvents and let go. A subform is created. Right-click the subform to see that the ChildField and MasterField properties both have EventID in them. This synchronises the mian form with the subform. Save and close.
Open subEnrolments in Design view. Change StudentID to a Combo (Right-click the field, Change to | ComboBox). Now go to the properties of the Combo. Click the RowSource property. Click the ... at the end of the line to go to SQL design view. If it's blank, add Students to the grid. Now add these fields:
StudentID
StudentName (you may need to make an expression to join Surname, Firstname)
At least one other descriptive field (Company, for example)
Now go to SQL view in the query. You'll see this --
SELECT something
FROM somewhere;
Change to
SELECT DISTINCT something
FROM somewhere;
This limits the list to one occurrence of each entry.
Back to Design view in the query. Set the sorting to Ascending by the StudentName field.
Close and save.
In the Properties, make sure that the Column Count matches the number of fields you had in the query (3, in the example above).
Column widths should be 0;3;4 (this hides the first key field, and lets you see the others in the drop-down. You'll need a width value for each column in the combo).
List Width should be the sum of the Column widths.
Bound Column should be 1.
Save
Close the subform, open frmEvents and take a look. In the StudentID field, you can select any existing student / registrant from the list. You will need to add students via the Students form before you can enrol them.
Denis
