Combo Boxes

  • Thread starter Thread starter Aleda
  • Start date Start date
A

Aleda

Is there a way to change a combo box that has 3 columns of information (last
name, first name and address) so that all of the information appears on the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name and
address. However, I did not realise that on the record, only the last name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.
 
Aleda

A combobox can display only one field after selection. That said, if you
wish to see more columns of information, you have a couple options:

1) you could use a query to retrieve the columns of information and in
the query concatenate the fields together into one, for display purposes.
2) you could add unbound textboxes to your form to hold the contents of
the third and fourth columns (the combobox would hold the second, but would
store the ID - the first).

NOTE: the form is for display purposes. You aren't and really don't want
to actually redundantly store all that information in the table underlying
the form.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Is there a way to change a combo box that has 3 columns of information (last
name, first name and address) so that all of the information appears on the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name and
address. However, I did not realise that on the record, only the last name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.
Click to expand...

First off... don't name a field Name. Name is a reserved word (a Form has a
Name property, a textbox has a Name property...).

Secondly, don't store data redundantly. If you're trying to copy the full name
and address from the table of Students into a payments table - DON'T! The
student's name should exist only in the Students table; that table should have
a unique StudentID, and only that field should be put into the table of
payments.

You can *display* the full name in the combo box by basing the combo on a
query such as

SELECT StudentID, [LastName] & ", " & [Firstname] AS Fullname, [Address] FROM
Students ORDER BY LastName, FirstName;

You can also put textboxes on the form with control sources such as

=comboboxname.Column(n)

where n is the zero based index of the field in the combo - e.g. if the
address is in the third column use (2).
 
Hi Jeff:

Thanks so much for the help. I will see if I can make it work.

All the best,
Aleda
 
Hi John:

Thanks so much for you sound advice. I will see which method I can make
happen.

All the best,
Aleda



John W. Vinson said:
Is there a way to change a combo box that has 3 columns of information (last
name, first name and address) so that all of the information appears on the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name and
address. However, I did not realise that on the record, only the last name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.
Click to expand...

First off... don't name a field Name. Name is a reserved word (a Form has a
Name property, a textbox has a Name property...).

Secondly, don't store data redundantly. If you're trying to copy the full name
and address from the table of Students into a payments table - DON'T! The
student's name should exist only in the Students table; that table should have
a unique StudentID, and only that field should be put into the table of
payments.

You can *display* the full name in the combo box by basing the combo on a
query such as

SELECT StudentID, [LastName] & ", " & [Firstname] AS Fullname, [Address] FROM
Students ORDER BY LastName, FirstName;

You can also put textboxes on the form with control sources such as

=comboboxname.Column(n)

where n is the zero based index of the field in the combo - e.g. if the
address is in the third column use (2).
Click to expand...
 
Aleda

You are welcome.

Consider posting back the solution you decided on... other folks may be
looking for a similar solution in the future.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Hi Jeff:

I cannot get the query to work. I really want the full name to print out for
reporting purposes. Could you explain the steps?

Thanks so much,
Aleda
 
You *might* have missed the point here. Both Jeff and John are giving you
the SAME advice from different perspectives; it's not a choice of methods in
the most fundamental sense.

Your student table should be designed like this. If it is not, then your
first step will be to correct the table design, and then come back to work
on the combo box for selecting students and for displaying their names in
report

tblStudent
=======
StudentID --the primary key, a unique identifier for each student, commonly
created using the Autonumber in Access
StudentFirstName
StudentLastName
StudentDOB (if appropriate)
StudentGender (if appropriate in your workflow)
etc. as appropriate to your workflow

tblPayment
========
PaymentID --the primary key, a unique identifier for each payment, commonly
created using the Autonumber in Access
StudentID --the foreign key, points back to the student table and uniquely
links each student to one or more payment records in the payment table
PaymentAmount
PaymentReason
PaymentDate
etc. as appropriate to your workflow

When used as the rowsource for a combo box, the proper approach is to bind
the primary key--StudentID--to the combo box. That value is the one you
store in the payments table. That value, the StudentID, is the ONLY value
you store in the payments table, as shown above. It is stored in the
payments table as a foreign key.

HTH

George


Hi John:

Thanks so much for you sound advice. I will see which method I can make
happen.

All the best,
Aleda



John W. Vinson said:
Is there a way to change a combo box that has 3 columns of information
(last
name, first name and address) so that all of the information appears on
the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name
and
address. However, I did not realise that on the record, only the last
name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.
Click to expand...

First off... don't name a field Name. Name is a reserved word (a Form has
a
Name property, a textbox has a Name property...).

Secondly, don't store data redundantly. If you're trying to copy the full
name
and address from the table of Students into a payments table - DON'T! The
student's name should exist only in the Students table; that table should
have
a unique StudentID, and only that field should be put into the table of
payments.

You can *display* the full name in the combo box by basing the combo on a
query such as

SELECT StudentID, [LastName] & ", " & [Firstname] AS Fullname, [Address]
FROM
Students ORDER BY LastName, FirstName;

You can also put textboxes on the form with control sources such as

=comboboxname.Column(n)

where n is the zero based index of the field in the combo - e.g. if the
address is in the third column use (2).
Click to expand...
Click to expand...
 
Hi George:

Thanks for the clarification. Not sure I understand what you meant by
foreign key. My table is set up for the Students table just as you mentioned.
But for my Payments table, it comes from a form. Since we have many students
with the same last name, I used the combo box to view the last name, first
name and address so I could select the right student and that placed it in a
field called ID1, and displays only the last name. When I click on the field,
it displays the students information. So what I wanted was a way to have a
report that shows the full name of the student.

But I guess, I have to start over and create a new payment table and form?
This is my first attempt to set up a database. We were tracking records in
Excel and it was too difficult to manage that way.

Any help would be greatly appreciated. Thanks so much.
George Hepworth said:
You *might* have missed the point here. Both Jeff and John are giving you
the SAME advice from different perspectives; it's not a choice of methods in
the most fundamental sense.

Your student table should be designed like this. If it is not, then your
first step will be to correct the table design, and then come back to work
on the combo box for selecting students and for displaying their names in
report

tblStudent
=======
StudentID --the primary key, a unique identifier for each student, commonly
created using the Autonumber in Access
StudentFirstName
StudentLastName
StudentDOB (if appropriate)
StudentGender (if appropriate in your workflow)
etc. as appropriate to your workflow

tblPayment
========
PaymentID --the primary key, a unique identifier for each payment, commonly
created using the Autonumber in Access
StudentID --the foreign key, points back to the student table and uniquely
links each student to one or more payment records in the payment table
PaymentAmount
PaymentReason
PaymentDate
etc. as appropriate to your workflow

When used as the rowsource for a combo box, the proper approach is to bind
the primary key--StudentID--to the combo box. That value is the one you
store in the payments table. That value, the StudentID, is the ONLY value
you store in the payments table, as shown above. It is stored in the
payments table as a foreign key.

HTH

George


Hi John:

Thanks so much for you sound advice. I will see which method I can make
happen.

All the best,
Aleda



John W. Vinson said:
On Mon, 8 Mar 2010 15:52:01 -0800, Aleda
<[email protected]>
wrote:

Is there a way to change a combo box that has 3 columns of information
(last
name, first name and address) so that all of the information appears on
the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name
and
address. However, I did not realise that on the record, only the last
name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.

First off... don't name a field Name. Name is a reserved word (a Form has
a
Name property, a textbox has a Name property...).

Secondly, don't store data redundantly. If you're trying to copy the full
name
and address from the table of Students into a payments table - DON'T! The
student's name should exist only in the Students table; that table should
have
a unique StudentID, and only that field should be put into the table of
payments.

You can *display* the full name in the combo box by basing the combo on a
query such as

SELECT StudentID, [LastName] & ", " & [Firstname] AS Fullname, [Address]
FROM
Students ORDER BY LastName, FirstName;

You can also put textboxes on the form with control sources such as

=comboboxname.Column(n)

where n is the zero based index of the field in the combo - e.g. if the
address is in the third column use (2).
Click to expand...
Click to expand...
Click to expand...
 
All data is stored in tables. *ALL* data. No data "comes from" forms. At
least not in any but the most trivial of ways.

Forms are the interface tools through which you add new data into a table,
update existing data in tables, or delete data from tables (although we
seldom actually delete data).

Therefore, the key to understanding how your form should work is to
understand how the tables need to be designed.
The process by which we create a proper table design is called
normalization. That's the place to start.

Look up and read all of the references you can find on normalization.

It's the fundamental process underlying all good database design.

George




Aleda said:
Hi George:

Thanks for the clarification. Not sure I understand what you meant by
foreign key. My table is set up for the Students table just as you
mentioned.
But for my Payments table, it comes from a form. Since we have many
students
with the same last name, I used the combo box to view the last name, first
name and address so I could select the right student and that placed it in
a
field called ID1, and displays only the last name. When I click on the
field,
it displays the students information. So what I wanted was a way to have a
report that shows the full name of the student.

But I guess, I have to start over and create a new payment table and form?
This is my first attempt to set up a database. We were tracking records in
Excel and it was too difficult to manage that way.

Any help would be greatly appreciated. Thanks so much.
George Hepworth said:
You *might* have missed the point here. Both Jeff and John are giving
you
the SAME advice from different perspectives; it's not a choice of methods
in
the most fundamental sense.

Your student table should be designed like this. If it is not, then your
first step will be to correct the table design, and then come back to
work
on the combo box for selecting students and for displaying their names in
report

tblStudent
=======
StudentID --the primary key, a unique identifier for each student,
commonly
created using the Autonumber in Access
StudentFirstName
StudentLastName
StudentDOB (if appropriate)
StudentGender (if appropriate in your workflow)
etc. as appropriate to your workflow

tblPayment
========
PaymentID --the primary key, a unique identifier for each payment,
commonly
created using the Autonumber in Access
StudentID --the foreign key, points back to the student table and
uniquely
links each student to one or more payment records in the payment table
PaymentAmount
PaymentReason
PaymentDate
etc. as appropriate to your workflow

When used as the rowsource for a combo box, the proper approach is to
bind
the primary key--StudentID--to the combo box. That value is the one you
store in the payments table. That value, the StudentID, is the ONLY value
you store in the payments table, as shown above. It is stored in the
payments table as a foreign key.

HTH

George


Hi John:

Thanks so much for you sound advice. I will see which method I can make
happen.

All the best,
Aleda



:

On Mon, 8 Mar 2010 15:52:01 -0800, Aleda
<[email protected]>
wrote:

Is there a way to change a combo box that has 3 columns of
information
(last
name, first name and address) so that all of the information appears
on
the
record?

I created a form, that in the Name Field, when you click on the
arrow, a
combo box lets you select the name, showing the last name, first name
and
address. However, I did not realise that on the record, only the last
name
appears.

This is a student database and I am trying to track payments for
certain
items. I think I have to create another form.

Any help would be greatly appreciated.

First off... don't name a field Name. Name is a reserved word (a Form
has
a
Name property, a textbox has a Name property...).

Secondly, don't store data redundantly. If you're trying to copy the
full
name
and address from the table of Students into a payments table - DON'T!
The
student's name should exist only in the Students table; that table
should
have
a unique StudentID, and only that field should be put into the table
of
payments.

You can *display* the full name in the combo box by basing the combo
on a
query such as

SELECT StudentID, [LastName] & ", " & [Firstname] AS Fullname,
[Address]
FROM
Students ORDER BY LastName, FirstName;

You can also put textboxes on the form with control sources such as

=comboboxname.Column(n)

where n is the zero based index of the field in the combo - e.g. if
the
address is in the third column use (2).
Click to expand...
Click to expand...
Click to expand...
 
Aleda said:
Is there a way to change a combo box that has 3 columns of information
(last
name, first name and address) so that all of the information appears on
the
record?

I created a form, that in the Name Field, when you click on the arrow, a
combo box lets you select the name, showing the last name, first name and
address. However, I did not realise that on the record, only the last name
appears.

This is a student database and I am trying to track payments for certain
items. I think I have to create another form.

Any help would be greatly appreciated.
Click to expand...
 
Back
Top