Form- Bringing in multiple data from one combo box

Creating my first database, and I have spent hours going through "help" to
find the answer. Hope someone can help me.

I have created a data table with multiple columns for descriptions,
wholesale and retail pricing. Each row of data represents one product with a
unique stock number.

I have created a form with a combo box that allows you to either type in the
stock number, or select it from the list. So far so good.

What I want to be able to do is when you type in or select the stock number,
it will also automatically fill in the remaining data from the data table row
into the form at the degnated spaces. How can I do this?

THANKS!
Sterling

On Wed, 27 Dec 2006 18:26:01 -0800, Sterling013
wrote:

Creating my first database, and I have spent hours going through "help" to
find the answer. Hope someone can help me.

I have created a data table with multiple columns for descriptions,
wholesale and retail pricing. Each row of data represents one product with a
unique stock number.

I have created a form with a combo box that allows you to either type in the
stock number, or select it from the list. So far so good.

What I want to be able to do is when you type in or select the stock number,
it will also automatically fill in the remaining data from the data table row
into the form at the degnated spaces. How can I do this?

Don't.

If you're trying to copy the description, etc. from the stock table
into some other table, you're missing the basic point of how
relational databases work. You should store this information once and
once only, and use Queries to look it up as needed! For example if you
want to print a Report, you'ld base the report on a query joining your
data table to the other table (sales??).

TO *display* the data from the data table on your form, include all
the fields that you want to see in the Combo Box's RowSource query. On
the form you can put textboxes with control source properties


=comboboxname.Column(n)

where n is the zero-based index of the field that you want to see -
e.g. if the Price is in the fourth column of the query use (3).

If I'm misunderstanding what you're trying to do please post back!

John W. Vinson[MVP]

John: Thank you for your response!

Since I am trying to teach myself Access, I think some of the terms you used
may not have the same definition that I have in my mind.

What I am trying to do is create a database to assist my home based
business. Currently, I use Excel templates to order products and create
invoices for customers. I manually enter the products stock number,
description, pricing, etc., which can take some time. Thus, I thought I
would create a database where I could just type in the stock number and the
remaining information would be filled in automatically. Each order may only
use 50+/- products out of the 1000’s that are available. Seems simple enough?

I tried building a form, hence my first posting. After reviewing your
reply, I tried to build a report without any better results. Obviously, I am
lacking knowledge of this program and it is not as easy to self-teach as
Word, Excel, PowerPoint, or Publisher.

Should I be designing a form or report? Could you be more explicit on how
to get all the information from the data table row into the form or report?
If it is too complicated, could you recommend a website or book that will
help me with the step-by-step process and give me a base understanding of
Access?

Thank you for your time!

Sterling


"John Vinson" wrote:

On Wed, 27 Dec 2006 18:26:01 -0800, Sterling013
wrote:

Creating my first database, and I have spent hours going through "help" to
find the answer. Hope someone can help me.

I have created a data table with multiple columns for descriptions,
wholesale and retail pricing. Each row of data represents one product with a
unique stock number.

I have created a form with a combo box that allows you to either type in the
stock number, or select it from the list. So far so good.

What I want to be able to do is when you type in or select the stock number,
it will also automatically fill in the remaining data from the data table row
into the form at the degnated spaces. How can I do this?

Don't.

If you're trying to copy the description, etc. from the stock table
into some other table, you're missing the basic point of how
relational databases work. You should store this information once and
once only, and use Queries to look it up as needed! For example if you
want to print a Report, you'ld base the report on a query joining your
data table to the other table (sales??).

TO *display* the data from the data table on your form, include all
the fields that you want to see in the Combo Box's RowSource query. On
the form you can put textboxes with control source properties


=comboboxname.Column(n)

where n is the zero-based index of the field that you want to see -
e.g. if the Price is in the fourth column of the query use (3).

If I'm misunderstanding what you're trying to do please post back!

John W. Vinson[MVP]

On Thu, 28 Dec 2006 08:09:00 -0800, Sterling013
wrote:

John: Thank you for your response!

Since I am trying to teach myself Access, I think some of the terms you used
may not have the same definition that I have in my mind.

What I am trying to do is create a database to assist my home based
business. Currently, I use Excel templates to order products and create
invoices for customers. I manually enter the products stock number,
description, pricing, etc., which can take some time. Thus, I thought I
would create a database where I could just type in the stock number and the
remaining information would be filled in automatically. Each order may only
use 50+/- products out of the 1000’s that are available. Seems simple enough?

A printed Invoice should be an Access "Report" - and it should contain
data from a query consisting multiple joined tables.

It is neither necessary nor appropriate to put all the data together
in one table to do this.

I tried building a form, hence my first posting. After reviewing your
reply, I tried to build a report without any better results. Obviously, I am
lacking knowledge of this program and it is not as easy to self-teach as
Word, Excel, PowerPoint, or Publisher.

It certainly isn't. Access has a rather steep learning curve!

Should I be designing a form or report? Could you be more explicit on how
to get all the information from the data table row into the form or report?
If it is too complicated, could you recommend a website or book that will
help me with the step-by-step process and give me a base understanding of
Access?

Check out the suggestions at these sites, starting with the "Database
Design 101" links on Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conrad...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]

Thank you John for your help. Looks like I need to spend some time educating
myself on Access instead of just diving into it. It's just a bigger
challenge!

Sterling

"John Vinson" wrote:

On Thu, 28 Dec 2006 08:09:00 -0800, Sterling013
wrote:

John: Thank you for your response!

Since I am trying to teach myself Access, I think some of the terms you used
may not have the same definition that I have in my mind.

What I am trying to do is create a database to assist my home based
business. Currently, I use Excel templates to order products and create
invoices for customers. I manually enter the products stock number,
description, pricing, etc., which can take some time. Thus, I thought I
would create a database where I could just type in the stock number and the
remaining information would be filled in automatically. Each order may only
use 50+/- products out of the 1000’s that are available. Seems simple enough?

A printed Invoice should be an Access "Report" - and it should contain
data from a query consisting multiple joined tables.

It is neither necessary nor appropriate to put all the data together
in one table to do this.

I tried building a form, hence my first posting. After reviewing your
reply, I tried to build a report without any better results. Obviously, I am
lacking knowledge of this program and it is not as easy to self-teach as
Word, Excel, PowerPoint, or Publisher.

It certainly isn't. Access has a rather steep learning curve!

Should I be designing a form or report? Could you be more explicit on how
to get all the information from the data table row into the form or report?
If it is too complicated, could you recommend a website or book that will
help me with the step-by-step process and give me a base understanding of
Access?

Check out the suggestions at these sites, starting with the "Database
Design 101" links on Jeff's site:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conrad...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]

On Thu, 28 Dec 2006 21:28:00 -0800, Sterling013
wrote:

Thank you John for your help. Looks like I need to spend some time educating
myself on Access instead of just diving into it. It's just a bigger
challenge!

Good luck, and don't hesitate to post here (or in
m.p.a.tablesdbdesign, m.p.a.gettingstarted or the other groups) if you
get stuck!

John W. Vinson[MVP]