Prev: text conversion to number on select query
Next: Rounding Problems with Euros
From: Jeff Boyce on 9 Mar 2010 11:07
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.

"Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
news:DB60595F-7AD1-4BCB-9F5F-DCC8A1902CA7(a)microsoft.com...
> Hi Jeff:
>
> Thanks so much for the help. I will see if I can make it work.
>
> All the best,
> Aleda
>
> "Jeff Boyce" wrote:
>
>> 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.
>>
>> "Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
>> news:750C37B2-6F9E-4840-A2A3-13FED57273E8(a)microsoft.com...
>> > 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.
>>
>>
>> .
>>


From: Aleda on 9 Mar 2010 12:53
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

"Jeff Boyce" wrote:

> 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.
>
> "Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
> news:750C37B2-6F9E-4840-A2A3-13FED57273E8(a)microsoft.com...
> > 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.
>
>
> .
>
From: George Hepworth on 9 Mar 2010 13:19
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

"Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
news:D8B56110-E5A4-46C7-BAF6-4823F5D9335D(a)microsoft.com...s.


> 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" wrote:
>
>> On Mon, 8 Mar 2010 15:52:01 -0800, Aleda
>> <Aleda(a)discussions.microsoft.com>
>> 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).
>> --
>>
>> John W. Vinson [MVP]
>> .
>>
From: Aleda on 9 Mar 2010 13:54
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" wrote:

> 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
>
> "Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
> news:D8B56110-E5A4-46C7-BAF6-4823F5D9335D(a)microsoft.com...s.
>
>
> > 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" wrote:
> >
> >> On Mon, 8 Mar 2010 15:52:01 -0800, Aleda
> >> <Aleda(a)discussions.microsoft.com>
> >> 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).
> >> --
> >>
> >> John W. Vinson [MVP]
> >> .
> >>
From: George Hepworth on 9 Mar 2010 14:57
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" <Aleda(a)discussions.microsoft.com> wrote in message
news:3083C1B6-AC07-4633-936A-74DB2BEE5A45(a)microsoft.com...
> 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" wrote:
>
>> 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
>>
>> "Aleda" <Aleda(a)discussions.microsoft.com> wrote in message
>> news:D8B56110-E5A4-46C7-BAF6-4823F5D9335D(a)microsoft.com...s.
>>
>>
>> > 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" wrote:
>> >
>> >> On Mon, 8 Mar 2010 15:52:01 -0800, Aleda
>> >> <Aleda(a)discussions.microsoft.com>
>> >> 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).
>> >> --
>> >>
>> >> John W. Vinson [MVP]
>> >> .
>> >>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: text conversion to number on select query
Next: Rounding Problems with Euros