Combo Box concern 2

[TracySHC]

Currently, I have a Microsoft Access 2003 form built over an Access table. For one of the text fields, I need the field on the form to be a combo box. At this point, I cannot populate the combo box's options. Is this due to the text field in the table not being a lookup field? I have tried to change the text field to a lookup field, but then I lose the data in this field. Any solutions? What would be the best practice for this situation?
Thank you in advance for your thoughts and advice,
TracySHC

 

[PHV]

change the text field to a lookup field
Have a look here:

http://www.mvps.org/access/lookupfields.htm

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TracySHC]

PHV,
Thank you for your response. I have changed the text field to a lookup field, but the field and lookup are blank in the table. What am I missing?
Thank you for your help and advice,
Tracy

 

[dhookom]

The advice was to NOT use lookup fields. Use combo boxes on forms and remove the lookups from tables unless you 100% understand what they are and how they are used.

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

PHV and dhookom,
If I do not need the lookup in the table, then how do I proceed to have the combo box work? I don't have either working at this moment - they're both blank. What am I missing? What should I check?
Thank you for your help and advice,
TracySHC

 

[dhookom]

All user interaction should be through forms, never tables. Use combo boxes on your forms to allow users to update your fields from values in a related table.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

What is the combo box to represent?
How are you populating the other fields on your form?
What do you want to do with the combo box?


Randy

 

[TracySHC]

Randy700,
I'm working on a tutoring database. The fields on the form are being populated by the tutors. I'm creating updates for some fields based on the student's id number, to pull in student name, etc. The combo box is to hold the courses for the student during the current session. It pulls these courses from another table in the Access database. Right now, it's all blanks. Any suggestions for trouble-shooting? Any best practice suggestions?
Thank you for your help and advice,
TracySHC

 

[dhookom]

We can't help without understanding your table structures and relationships. Maybe you could provide just a small sampling of significant tables and fields with the relationships.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

The combo box is to hold the courses for the student during the current session. It pulls these courses from another table in the Access database. Right now, it's all blanks.
Use the combo box wizard to create this.

When I asked how the fields are populated, I meant:
Are they bound to a table / query?


Randy

 

[TracySHC]

dhookom and randy700,
I have 3 tables in this database:
EntryInfo - student id#, student name, tutoring time, tutoring day, course needed for tutoring, etc.
Students - student id#, student name
Courses - student id#, course

EntryInfo is linked by student id# to both Students and Courses

The form screen, EnterScreen, was built over the EntryInfo table. It has a student id# that, when entered, populates the student name from the Students file. It needs to have a working combo box that populates with the student's courses from the Courses file. Is this the information needed?
Thank you for your help and advice,
TracySHC

 

[dhookom]

First, I would not store the Student Name in EntryInfo. If you have the Student ID#, the name is redundant and generally considered bad practice.

Why is there a Student ID# in the Courses table? I would expect to see a Courses table with a CourseID and CourseTitle. The EntryInfo table would then store:
-StudentID
-CourseID
-TutoringDate
-TutoringTime
- other possible fields

The form for EntryInfo would have a combo box for StudentID#:
Control Source: StudentID#
Row Source: Select StudentID#, StudentName FROM Students
ORDER BY StudentName;
Bound Column: 1
Column Count: 2
Column Widths: 0,1

and a combo box for entering the CourseID
Control Source: CourseID
Row Source: Select CourseID, CourseTitle FROM Courses
ORDER BY CourseTitle;
Bound Column: 1
Column Count: 2
Column Widths: 0,1

I might be mis-understanding the use of the Courses table. What is its primary key?


Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
CourseID is the same as course number. The Courses table is a list of all of the courses taken by each student. This way, the tutor is constrained to enter only the courses taken by the entered student. (The complete course list is not available.) Therefore, what I need from the combo box is:

Control Source: CourseID
Row Source: Select CourseID FROM Courses WHERE Courses.StudentID = [Forms]![EnterScreen]![StudentID]
ORDER BY CourseTitle;
Bound Column: 1
Column Count: 2
Column Widths: 0,1

What do you suggest? I still have blanks.
Thank you for your help and advice,
TracySHC

 

[dhookom]

This seems to be the first mention of dependent combo boxes where the displayed values relies on another field in the record source.

You didn't provide any information about the primary key of the Courses table. Is the combination of StudentID and CourseID unique in the Courses table? Is there a courseID field?

Your Row Source selects only a single field yet the Column Count property is 2. Add the CourseTitle to the SELECT statement.

You also need code to requery the combo box when the studentID changes.

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
Is it significant that the combo box be dependent on another field on the form? As for code when the student id changes, what do you suggest?

Courses does have a CourseID primary key - I let Access automatically add this field. I did not want to take a chance that a student registered twice for the same course (it's possible given self-study courses.)

Is more information needed?
Thank you for your help and advice,
TracySHC

 

[dhookom]

Is it significant that the combo box be dependent on another field on the form?" I thought you only wanted to show records from the Courses table where the studentIDs match. If so, yes, it is significant. You should probably have a line of code in the On Got Focus event of the combo box like:

 Me.[Name of combo box].Requery

If you don't requery the combo box, the row source will be filtered to the studentID from the first StudentID when the form was opened.

If this form is continuous with multiple StudentIDs, the combo box will not display courses for any other student.

Duane
Hook'D on Access
MS Access MVP

 

[TracySHC]

dhookom,
That did it!
Thank you very much,
TracySHC