If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto Complete
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200807/1 |
#2
|
|||
|
|||
Auto Complete
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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Tazzy via AccessMonster.com" u26845@uwe wrote in message news:86bf3c3bf2bc9@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200807/1 |
#3
|
|||
|
|||
Auto Complete
Thank you for your reply - will give this a go tomorrow and let you know.
Tazzy -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Auto Complete
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200807/1 |
#5
|
|||
|
|||
Auto Complete
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200807/1 |
#6
|
|||
|
|||
Auto Complete
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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Tazzy via AccessMonster.com" u26845@uwe wrote in message news:86c9ccd9e6ebb@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200807/1 |
Thread Tools | |
Display Modes | |
|
|