Starting Over with my Design

[Megan]

Hi
I'm starting over with my design and have a few fairly preliminary questions
I'm looking at Northwind as my guide.
I see in several cases, they are lookups in the tables.
I am trying to get that to work in mine. If I have a primary key that's only purpose is to make a record unique, how can I get around using it. For example - I have a customerID as a primary key. I really want the customerName to be what I use in my lists, etc. I see Northwind doing a lookup gathering both but displaying only the name. I can only display the ID. I think my error is in the "bound" fields but can't get it right.

Also, if there is a data in the table and I want to put a lookup to another table, I can't right. I have a lot of information that I imported in from spreadsheets. Going forward, I want the users to be able to just use a drop down list. I know the same values will eventually get in the table but...am I doing something else wrong?

 

[MacDermott]

Hello, Megan!

Although Microsoft demonstrates the technique of using lookups in tables,
most posters in the newsgroups I frequent advise against them.
Especially for beginners, it gets very confusing when what you see in the
table is not what is actually there.

My advice is to build comboboxes on your forms, but avoid lookups directly
in your tables.

HTH
- Turtle

Hi.
I'm starting over with my design and have a few fairly preliminary questions.
I'm looking at Northwind as my guide.
I see in several cases, they are lookups in the tables.
I am trying to get that to work in mine. If I have a primary key that's

only purpose is to make a record unique, how can I get around using it. For
example - I have a customerID as a primary key. I really want the
customerName to be what I use in my lists, etc. I see Northwind doing a
lookup gathering both but displaying only the name. I can only display the
ID. I think my error is in the "bound" fields but can't get it right.

Also, if there is a data in the table and I want to put a lookup to

another table, I can't right. I have a lot of information that I imported in
from spreadsheets. Going forward, I want the users to be able to just use a
drop down list. I know the same values will eventually get in the table
but...am I doing something else wrong?

 

[Dave]

try setting the first column width to 0 then it wont be displayed on the
screen

Hope this helps
Dave

Hi.
I'm starting over with my design and have a few fairly preliminary questions.
I'm looking at Northwind as my guide.
I see in several cases, they are lookups in the tables.
I am trying to get that to work in mine. If I have a primary key that's

only purpose is to make a record unique, how can I get around using it. For
example - I have a customerID as a primary key. I really want the
customerName to be what I use in my lists, etc. I see Northwind doing a
lookup gathering both but displaying only the name. I can only display the
ID. I think my error is in the "bound" fields but can't get it right.

Also, if there is a data in the table and I want to put a lookup to

another table, I can't right. I have a lot of information that I imported in
from spreadsheets. Going forward, I want the users to be able to just use a
drop down list. I know the same values will eventually get in the table
but...am I doing something else wrong?

 

[Megan]

Hey Turtle,
Thanks.

I understand what you are saying. I guess I thought having the lookup was actually bringing that data into the current table.
I was having trouble with the forms which was one of the reasons I change directions to my original question.
Part of my confusion is that in order to create a unique index, I had to make my PK the ID but I want the value not the ID in my forms.

I feel like I'm on the cusp of knowing what I'm doing but can't just the hurdle.
When I do a query in my combo that get the ID and value. I can show only the value but then it's not reflected in the rest of the table. Only the combo box changes not the remainder of the information on the form -- for example an order form. If I change the product, I need the price and characteristics to also change.

While I have you, can I have a product list in a combo box that can create another combo box with a list of say, sizes for the selected product? Do I have to put it into a button to run a macro.

So close and yet...



----- MacDermott wrote: -----

Hello, Megan!

Although Microsoft demonstrates the technique of using lookups in tables,
most posters in the newsgroups I frequent advise against them.
Especially for beginners, it gets very confusing when what you see in the
table is not what is actually there.

My advice is to build comboboxes on your forms, but avoid lookups directly
in your tables.

HTH
- Turtle

Hi.
I'm starting over with my design and have a few fairly preliminary questions.
I'm looking at Northwind as my guide.
I see in several cases, they are lookups in the tables.
I am trying to get that to work in mine. If I have a primary key that's

only purpose is to make a record unique, how can I get around using it. For
example - I have a customerID as a primary key. I really want the
customerName to be what I use in my lists, etc. I see Northwind doing a
lookup gathering both but displaying only the name. I can only display the
ID. I think my error is in the "bound" fields but can't get it right.another table, I can't right. I have a lot of information that I imported in
from spreadsheets. Going forward, I want the users to be able to just use a
drop down list. I know the same values will eventually get in the table
but...am I doing something else wrong?

 

[Art]

Hi Megan:

Make a new combo box using the wizard. Click on the radio
button for "...find a record on my form..." This will
allow the fields on the form to change with the selection
in the combo box

Hope this helps

-----Original Message-----
Hey Turtle,
Thanks.

I understand what you are saying. I guess I thought

having the lookup was actually bringing that data into the
current table.

I was having trouble with the forms which was one of the

reasons I change directions to my original question.

Part of my confusion is that in order to create a unique

index, I had to make my PK the ID but I want the value not
the ID in my forms.

I feel like I'm on the cusp of knowing what I'm doing but can't just the hurdle.
When I do a query in my combo that get the ID and value.

I can show only the value but then it's not reflected in
the rest of the table. Only the combo box changes not the
remainder of the information on the form -- for example an
order form. If I change the product, I need the price and
characteristics to also change.

While I have you, can I have a product list in a combo

box that can create another combo box with a list of say,
sizes for the selected product? Do I have to put it into a
button to run a macro.

 

[MacDermott]

Hello, Megan!

It's amazing how many refinements can be made in an Access program, even
after it seems to be "almost done".
As you design your order form, you need to make a basic decision -
what happens if your product information changes?
Say you have an order for 3 of product XYZ.
When you make the order, product XYZ is a thingy at $5.
But some time later the price of a thingy changes to $5.50.
Do you want all your orders to be updated to reflect the new price?
Or do you want them to continue to reflect the price at the time of the
order?

Also -
as regards having one combobox control the options in another, there's
some code to do that at www.mvps.org/access.
I don't remember exactly where it is - quite possibly under Forms, but I'd
highly recommend spending some time exploring that site. You can find a lot
of good advice there.

HTH
- Turtle

Hey Turtle,
Thanks.

I understand what you are saying. I guess I thought having the lookup was

actually bringing that data into the current table.

I was having trouble with the forms which was one of the reasons I change

directions to my original question.

Part of my confusion is that in order to create a unique index, I had to

make my PK the ID but I want the value not the ID in my forms.

I feel like I'm on the cusp of knowing what I'm doing but can't just the hurdle.
When I do a query in my combo that get the ID and value. I can show only

the value but then it's not reflected in the rest of the table. Only the
combo box changes not the remainder of the information on the form -- for
example an order form. If I change the product, I need the price and
characteristics to also change.

While I have you, can I have a product list in a combo box that can create

another combo box with a list of say, sizes for the selected product? Do I
have to put it into a button to run a macro.