Lookup Field on a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table "Student" containing student info like ID, names and
addresses. I also have a table "Student Record" which keeps track of grades
and other info.

I created a form for people to input the info into the table "Student
Record". On the form I have a field for Student ID and Student Name. I have
set up the Student ID field as a combo box so that people can choose the ID.
What I like to do is I want the Student Name field automatically populates
after people select a Student ID from the combo box or after people type in
the Student ID from the combo box.

I also would like to have people to select or type in multiple times in the
combo box and the Student Name field would still automatically populate. And
the Student Name that just automatically populates will be populate in the
Student Record table.

I have tried "DLookup" in the Control Source of the Student Name field. It
does populate automatically when I select the ID from the combo box for first
time. When I type in or select more than once, it would not populate it.
Also it would not populate the name in the Student Record table.

Thanks.
 
First, wouldn't it be easier to choose a name rather than the ID in the combo
box? Normally, you don't really care about the numeric code--it is simply
there as an identifier.

Secondly, be careful to distinguish between *fields*, which exist in a
table, and have a data type such as "Text", "Integer", "Date/Time", etc. and
*controls*, which exist on a form or in a report, have no data type, and can
be *bound* to a field in the form or report's RecordSource, or be *unbound*.

You need a main form based on Student, and a continuous subform based on
Student Record. Student Record must contain the StudentID field--a foreign
key corresponding to Student's primary key. Drag the subform from the
database window onto the main form in form design view, and link the main and
subforms by the StudentID by setting the subform's MasterLinkFields and
MasterChildFields properties to the name of the student ID field in the main
form and subform's RecordSource, respectively.

Once the link is established, all Student Record records will display
automatically after navigating to the proper student record in the main form.
You can facilitate this navigation with an *unbound* (i.e., no
ControlSource) combo box similar to your own. If you choose to select a name
rather than an ID, include both fields in the RowSource of the combo box, set
the Bound Column to 1, and the ColumnWidths property to 0";x", where x is
large enough to display the longest name.

Then you can use the combo box selection to navigate to the desired record.
Since the BoundColumn is set to 1, its *value* is the ID, even though it
*displays* the Name, since Access displays the first non-zero-width column.
In the AfterUpdate event of the unbound combo box:

DoCmd.OpenForm "YourFormName", , , "[StudentID]=" & Me!YourComboBox

Hope that helps.
Sprinks
 
Sprinks,

I did what you said yesterday. However, I got two issues. First, I got an
error message about duplicates after I selected the student name on the Main
form from the combo box and tabed to the next field on the form.

Second, the student name was not populated into the table "Student Record".

Thanks.

Sprinks said:
First, wouldn't it be easier to choose a name rather than the ID in the combo
box? Normally, you don't really care about the numeric code--it is simply
there as an identifier.

Secondly, be careful to distinguish between *fields*, which exist in a
table, and have a data type such as "Text", "Integer", "Date/Time", etc. and
*controls*, which exist on a form or in a report, have no data type, and can
be *bound* to a field in the form or report's RecordSource, or be *unbound*.

You need a main form based on Student, and a continuous subform based on
Student Record. Student Record must contain the StudentID field--a foreign
key corresponding to Student's primary key. Drag the subform from the
database window onto the main form in form design view, and link the main and
subforms by the StudentID by setting the subform's MasterLinkFields and
MasterChildFields properties to the name of the student ID field in the main
form and subform's RecordSource, respectively.

Once the link is established, all Student Record records will display
automatically after navigating to the proper student record in the main form.
You can facilitate this navigation with an *unbound* (i.e., no
ControlSource) combo box similar to your own. If you choose to select a name
rather than an ID, include both fields in the RowSource of the combo box, set
the Bound Column to 1, and the ColumnWidths property to 0";x", where x is
large enough to display the longest name.

Then you can use the combo box selection to navigate to the desired record.
Since the BoundColumn is set to 1, its *value* is the ID, even though it
*displays* the Name, since Access displays the first non-zero-width column.
In the AfterUpdate event of the unbound combo box:

DoCmd.OpenForm "YourFormName", , , "[StudentID]=" & Me!YourComboBox

Hope that helps.
Sprinks


AccessHelp said:
I have a table "Student" containing student info like ID, names and
addresses. I also have a table "Student Record" which keeps track of grades
and other info.

I created a form for people to input the info into the table "Student
Record". On the form I have a field for Student ID and Student Name. I have
set up the Student ID field as a combo box so that people can choose the ID.
What I like to do is I want the Student Name field automatically populates
after people select a Student ID from the combo box or after people type in
the Student ID from the combo box.

I also would like to have people to select or type in multiple times in the
combo box and the Student Name field would still automatically populate. And
the Student Name that just automatically populates will be populate in the
Student Record table.

I have tried "DLookup" in the Control Source of the Student Name field. It
does populate automatically when I select the ID from the combo box for first
time. When I type in or select more than once, it would not populate it.
Also it would not populate the name in the Student Record table.

Thanks.
Click to expand...
Click to expand...
 
The combo box should be unbound, i.e., the Control Source should be left
blank. Its purpose is simply as a navigation convenience. You should have
another textbox on the form bound to the student for entering new students.

It didn't add the StudentName to the child records in your case because
Access blocked adding the main form record because it already existed. Once
you unbind the combo box, you can add new "Student Records" records in the
subform and they will receive the linked field automatically.

Sprinks


AccessHelp said:
Sprinks,

I did what you said yesterday. However, I got two issues. First, I got an
error message about duplicates after I selected the student name on the Main
form from the combo box and tabed to the next field on the form.

Second, the student name was not populated into the table "Student Record".

Thanks.

Sprinks said:
First, wouldn't it be easier to choose a name rather than the ID in the combo
box? Normally, you don't really care about the numeric code--it is simply
there as an identifier.

Secondly, be careful to distinguish between *fields*, which exist in a
table, and have a data type such as "Text", "Integer", "Date/Time", etc. and
*controls*, which exist on a form or in a report, have no data type, and can
be *bound* to a field in the form or report's RecordSource, or be *unbound*.

You need a main form based on Student, and a continuous subform based on
Student Record. Student Record must contain the StudentID field--a foreign
key corresponding to Student's primary key. Drag the subform from the
database window onto the main form in form design view, and link the main and
subforms by the StudentID by setting the subform's MasterLinkFields and
MasterChildFields properties to the name of the student ID field in the main
form and subform's RecordSource, respectively.

Once the link is established, all Student Record records will display
automatically after navigating to the proper student record in the main form.
You can facilitate this navigation with an *unbound* (i.e., no
ControlSource) combo box similar to your own. If you choose to select a name
rather than an ID, include both fields in the RowSource of the combo box, set
the Bound Column to 1, and the ColumnWidths property to 0";x", where x is
large enough to display the longest name.

Then you can use the combo box selection to navigate to the desired record.
Since the BoundColumn is set to 1, its *value* is the ID, even though it
*displays* the Name, since Access displays the first non-zero-width column.
In the AfterUpdate event of the unbound combo box:

DoCmd.OpenForm "YourFormName", , , "[StudentID]=" & Me!YourComboBox

Hope that helps.
Sprinks


AccessHelp said:
I have a table "Student" containing student info like ID, names and
addresses. I also have a table "Student Record" which keeps track of grades
and other info.

I created a form for people to input the info into the table "Student
Record". On the form I have a field for Student ID and Student Name. I have
set up the Student ID field as a combo box so that people can choose the ID.
What I like to do is I want the Student Name field automatically populates
after people select a Student ID from the combo box or after people type in
the Student ID from the combo box.

I also would like to have people to select or type in multiple times in the
combo box and the Student Name field would still automatically populate. And
the Student Name that just automatically populates will be populate in the
Student Record table.

I have tried "DLookup" in the Control Source of the Student Name field. It
does populate automatically when I select the ID from the combo box for first
time. When I type in or select more than once, it would not populate it.
Also it would not populate the name in the Student Record table.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Again, thanks for your help. Now I am not getting any error message.
However, the student name is still not populating to the table and the bound
field on the child form. Also when I typed in this code "DoCmd.OpenForm
"YourFormName", , , "[StudentID]=" & Me!YourComboBox" next to After Update in
the Unbound combo box and after selecting the student name from this combo
box, I was prompted for the student ID.

Thanks.

Sprinks said:
The combo box should be unbound, i.e., the Control Source should be left
blank. Its purpose is simply as a navigation convenience. You should have
another textbox on the form bound to the student for entering new students.

It didn't add the StudentName to the child records in your case because
Access blocked adding the main form record because it already existed. Once
you unbind the combo box, you can add new "Student Records" records in the
subform and they will receive the linked field automatically.

Sprinks


AccessHelp said:
Sprinks,

I did what you said yesterday. However, I got two issues. First, I got an
error message about duplicates after I selected the student name on the Main
form from the combo box and tabed to the next field on the form.

Second, the student name was not populated into the table "Student Record".

Thanks.

Sprinks said:
First, wouldn't it be easier to choose a name rather than the ID in the combo
box? Normally, you don't really care about the numeric code--it is simply
there as an identifier.

Secondly, be careful to distinguish between *fields*, which exist in a
table, and have a data type such as "Text", "Integer", "Date/Time", etc. and
*controls*, which exist on a form or in a report, have no data type, and can
be *bound* to a field in the form or report's RecordSource, or be *unbound*.

You need a main form based on Student, and a continuous subform based on
Student Record. Student Record must contain the StudentID field--a foreign
key corresponding to Student's primary key. Drag the subform from the
database window onto the main form in form design view, and link the main and
subforms by the StudentID by setting the subform's MasterLinkFields and
MasterChildFields properties to the name of the student ID field in the main
form and subform's RecordSource, respectively.

Once the link is established, all Student Record records will display
automatically after navigating to the proper student record in the main form.
You can facilitate this navigation with an *unbound* (i.e., no
ControlSource) combo box similar to your own. If you choose to select a name
rather than an ID, include both fields in the RowSource of the combo box, set
the Bound Column to 1, and the ColumnWidths property to 0";x", where x is
large enough to display the longest name.

Then you can use the combo box selection to navigate to the desired record.
Since the BoundColumn is set to 1, its *value* is the ID, even though it
*displays* the Name, since Access displays the first non-zero-width column.
In the AfterUpdate event of the unbound combo box:

DoCmd.OpenForm "YourFormName", , , "[StudentID]=" & Me!YourComboBox

Hope that helps.
Sprinks


:

I have a table "Student" containing student info like ID, names and
addresses. I also have a table "Student Record" which keeps track of grades
and other info.

I created a form for people to input the info into the table "Student
Record". On the form I have a field for Student ID and Student Name. I have
set up the Student ID field as a combo box so that people can choose the ID.
What I like to do is I want the Student Name field automatically populates
after people select a Student ID from the combo box or after people type in
the Student ID from the combo box.

I also would like to have people to select or type in multiple times in the
combo box and the Student Name field would still automatically populate. And
the Student Name that just automatically populates will be populate in the
Student Record table.

I have tried "DLookup" in the Control Source of the Student Name field. It
does populate automatically when I select the ID from the combo box for first
time. When I type in or select more than once, it would not populate it.
Also it would not populate the name in the Student Record table.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Please post the LinkMasterFields and LinkChildFields properties of the
subform. They should be set to the names of the common field in the main
form's RecordSource and the subform's RecordSource, respectively.

As to the code, you will need to change YourFormName to the name of your
form, StudentID to the name of the primary key field you are trying to match,
and YourComboBox to the name of the combo box.

In order for this to work, the *value* of the lookup combo box (determined
by the Bound Column) must match the type of data in the primary key field.

If, for example, the Row Source of your combo box was:

SELECT StudentName FROM Students ORDER BY StudentName

then the *value* of the combo box after selection would be something like
"John Smith". If, however, the RowSource is something like:

SELECT StudentID, StudentName FROM Students ORDER BY StudentName

with the Bound Column set to 1, then the value of the box will be the
StudentID corresponding to the selection.

Hope that helps.
Sprinks

AccessHelp said:
Again, thanks for your help. Now I am not getting any error message.
However, the student name is still not populating to the table and the bound
field on the child form. Also when I typed in this code "DoCmd.OpenForm
"YourFormName", , , "[StudentID]=" & Me!YourComboBox" next to After Update in
the Unbound combo box and after selecting the student name from this combo
box, I was prompted for the student ID.

Thanks.

Sprinks said:
The combo box should be unbound, i.e., the Control Source should be left
blank. Its purpose is simply as a navigation convenience. You should have
another textbox on the form bound to the student for entering new students.

It didn't add the StudentName to the child records in your case because
Access blocked adding the main form record because it already existed. Once
you unbind the combo box, you can add new "Student Records" records in the
subform and they will receive the linked field automatically.

Sprinks


AccessHelp said:
Sprinks,

I did what you said yesterday. However, I got two issues. First, I got an
error message about duplicates after I selected the student name on the Main
form from the combo box and tabed to the next field on the form.

Second, the student name was not populated into the table "Student Record".

Thanks.

:

First, wouldn't it be easier to choose a name rather than the ID in the combo
box? Normally, you don't really care about the numeric code--it is simply
there as an identifier.

Secondly, be careful to distinguish between *fields*, which exist in a
table, and have a data type such as "Text", "Integer", "Date/Time", etc. and
*controls*, which exist on a form or in a report, have no data type, and can
be *bound* to a field in the form or report's RecordSource, or be *unbound*.

You need a main form based on Student, and a continuous subform based on
Student Record. Student Record must contain the StudentID field--a foreign
key corresponding to Student's primary key. Drag the subform from the
database window onto the main form in form design view, and link the main and
subforms by the StudentID by setting the subform's MasterLinkFields and
MasterChildFields properties to the name of the student ID field in the main
form and subform's RecordSource, respectively.

Once the link is established, all Student Record records will display
automatically after navigating to the proper student record in the main form.
You can facilitate this navigation with an *unbound* (i.e., no
ControlSource) combo box similar to your own. If you choose to select a name
rather than an ID, include both fields in the RowSource of the combo box, set
the Bound Column to 1, and the ColumnWidths property to 0";x", where x is
large enough to display the longest name.

Then you can use the combo box selection to navigate to the desired record.
Since the BoundColumn is set to 1, its *value* is the ID, even though it
*displays* the Name, since Access displays the first non-zero-width column.
In the AfterUpdate event of the unbound combo box:

DoCmd.OpenForm "YourFormName", , , "[StudentID]=" & Me!YourComboBox

Hope that helps.
Sprinks


:

I have a table "Student" containing student info like ID, names and
addresses. I also have a table "Student Record" which keeps track of grades
and other info.

I created a form for people to input the info into the table "Student
Record". On the form I have a field for Student ID and Student Name. I have
set up the Student ID field as a combo box so that people can choose the ID.
What I like to do is I want the Student Name field automatically populates
after people select a Student ID from the combo box or after people type in
the Student ID from the combo box.

I also would like to have people to select or type in multiple times in the
combo box and the Student Name field would still automatically populate. And
the Student Name that just automatically populates will be populate in the
Student Record table.

I have tried "DLookup" in the Control Source of the Student Name field. It
does populate automatically when I select the ID from the combo box for first
time. When I type in or select more than once, it would not populate it.
Also it would not populate the name in the Student Record table.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top