Auto Complete

[Tazzy via AccessMonster.com]

Hi all,

I'm having a problem with a small database that I'm trying to set up for my
wife's training business, it involves details of students, dates of courses
and the cost for each course. I've set up tables as follows;

Tbl_Student - Student ID (Primary Key), First Name, Last Name etc.
Tbl_Course - Course ID (PK), Course Title, Start Date etc
Tbl_Cost - Course Title (PK) and Cost
Tbl_Join - Two fields, primary keys from Students and Courses.

I think this is correct as one student can have many courses, and one course
can have many students.

I want to create a form to enter all courses on, and also have a subform to
show details of any students that attend. (No problem so far) The subform
must allow additions/deletions, but I want to be able to have a combi box
that will show a list of student ID's, then when I click the ID, it will
enter the ID, First Name and Last Name automatically. Is this possible just
by using a query and creating the subform from that query?

Hope I've explained this accurately for you.

Thanks,

Tazzy

 

[Arvin Meyer [MVP]]

Yes, quite possible. First of all, you really only want to display the First
and Last Name of the student, since having and storing the ID is all that's
sufficient of proper data storage under database normalization rules.

The easiest way to do that is to do a lookup from a 3 column combo box, with
the second 2 column widths being set to zero. Then just use the column
property of a combo box:

Sub MyCombo_AfterUpdate()
Me.txtFirstName = Me.cboStudentID.Column(1)
Me.txtLastName = Me.cboStudentID.Column(2)
End Sub

You cannot do that in a subform, but if you based the subform on a query
that includes the Student Table, you can select a StudentID from a combo,
and the First, Last, and any other detail will fill in automatically. This
is called an autolookup.

 

[Tazzy via AccessMonster.com]

Thank you for your reply - will give this a go tomorrow and let you know.

Tazzy

 

[Tazzy via AccessMonster.com]

Well I certainly got a bit lost on the first part of your answer,(haven't
much experience of using code) so I thought that I would try the second part.

I created a query from my Student table using only ID, First Name and Last
Name, created a subform from this and entered onto the Course Form. I then
changed the field for ID to a combo, and in the expression builder I used
only that field. Saved the form, went back to the normal view, and yes, I do
get a drop down list of all student IDs. However, when I choose one of these,
it does not enter anything into the first name and last name fields. Where am
I going wrong please.

Thanks for your patience

Tazyy

 

[Tazzy via AccessMonster.com]

An update to my progress.

I decided to create a subform based on the join table, hid the course ID
field and changed the Student ID field to a 3 column combo box that included
the first and last name fields. The resulting combo box does have a drop down
list of those three fields, clicking on one however will only result in the
Student ID being shown.

I feel that I have made a little progress, but is there a way of adding first
and last name fields to this subform that will automatically display the
details once I have chosen an ID.

It has also thrown up another problem - I can have the same student ID more
than once for each course. How can I prevent this? It is set as a primary key
in its table, and although it needs to be available for more than one course,
it should only ever be allowed once in a course.

Tazzy

 

[Arvin Meyer [MVP]]

I've prepared a small database which will demonstrate how to do both of the
things you need. I'll compact and zip it and send it to you. Do not post
your email address here. Instead, go to my webpage at datastrat.com and send
your email to my email there.