A 2010 Table no design view

[Dick7Access]

I just tried to take a look at one of MS’s templates to get an idea on how some relationships were created, and I cannot go to design view on any of the tables. Anybody know if there is something that has to be done to switch views

 

[Beetle]

You're probably looking at a web template. There is no design view for web tables, you simply add fields to the main view. Do the table icons have small globes on them? If so those are web tables.

 

[Dick7Access]

Thanks! Do they really have beer in CO

 

[Beetle]

Thanks! Do they really have beer in CO

I'm drinking one right now.

 

[Dick7Access]

Hey Beetle, if your still there, a quick quetion.
I ask this question about 5 years ago, got the answer but forgot.
I am studing tonight on nirmlization as you suggest. I am building a combo box and it always records the Id insted of the text. I have been googling it for about 2 hours

 

[Dick7Access]

I don't drink beer anymore but when I did my favoriate was Lowenbraw draft in a frosted mug

 

[Dick7Access]

I think if I remember correctly it has to do with the formatting of the row soure

 

[Beetle]

I am building a combo box and it always records the Id insted of the text

That's how combo boxes are supposed to work. A combo box has both a Control Source and a Row Source. The Control Source determines which field in the forms underlying record set (table or query) the value is stored in. The Row Source determines where the values that are displayed in the combo box come from, which would be a different table or query than the one the form is based on. Typically you want to store a key value, like CustomerID, but you want the combo box to display a value like CustomerName.

 

[Dick7Access]

That is the probem. My combo box is taking the Id and name from the customer table and pasting only the id in the name field of the order table. As I said before it was a long time ago and someone on the forum helped me and now I can't findmy notes on it, and can't remember it either. It seems to I had to add something to the sql

 

[Dick7Access]

I was going tio ask what you are doing up so late, but then I rememberd that you are a few hours earlier than me. I wish these post forms had as spell checker

 

[Dick7Access]

This is the tutorial I have been studing, and as far ad I can see I have every think right, but still paste Id and not text

http://www.techonthenet.com/access/comboboxes/bind_index.php

 

[boblarson]

but still paste Id and not text

You are not supposed to store the text. As Beetle said, you store the number, NOT THE TEXT.

 

[Beetle]

The number (CustomerID or whatever you have it named) is the only value you need to store in the Orders table. That's how relational databases work. A Customer can have more than one Order, so you have a Foreign Key field in the Orders table to store the CustomerID value (the Primary Key) from the Customers table. Any other information that you may ever need to know about a Customer as it relates to an Order can be retrieved from the Customers table whenever you need it based solely on that CustomerID value.

 

[Dick7Access]

Beetle, Bob,
I understand the relationship between the ID and other fields. That is what I am studying now to better understand it. Everything else in the table is associated to the ID. This is not a db I am making for anyone. I have made many simple db for people in the past but never using any formulation of my tables, which of course is not sound programing. . Maybe it is the form that I am not understanding and not structuring it correctly. To do this study I made a tbl customers with the usual fields, CuId auto number, and three names as data, the names are in a field called, (NameLast). A tbl orders with a OrId auto number and a Cuid longinteger, a field called CusLastName. I then went into database tools > relationships and dragged tblCustomer Cuid (auto number) to tblOrder to cuid (longinteger).
Now to test it I made a form with only a combo box that calls the list of three names of customers. (nothing else is on the form) When I open the combo box the three names show up ok. I will pick a name and then go to my tblOrders, and in the CusLastName field the cuid shows up. Obviously if a data person would look at the orders they would not know what customer had that order from the Cuid.

 

[Beetle]

The CusLastName field should not exist in the Orders table, only the CuID field. The combo box on your form needs to return two columns from the Customers table, CuID and CusLastName, so the Row Source of the combo box should look like;

Select CuID, CusLastName From tblCustomers Order By CusLastName;

The Control Source of the combo box should be the CuID field from the Orders table, then there are a few more properties of the combo box that need to be set appropriately for this to work right;

Bound Column: This should be set to 1 (the first column, which is CuID)

Column Count: This needs to be 2 in this case, because you are returning two columns.

Column Widths: This should be 0", 1" (or whatever width works best for the second column) so that the CuID column is hidden and the CusLastName column is displayed.

Now when you select a value in the combo box it should store the CuID value from the Customers table in the CuID field of the Orders table

 

[Dick7Access]

Beetle, Bob, or Anyone,
I think my problem is not understanding the proper us of a combo box, not the relationship between Id’s. If you will look at the new tables I have re-structured and the relationship I set up that seems to be normalized. Where would I use a combo box in this snippet?

 

[Beetle]

I can look at it on Monday. I don't have A2010 where I am now.

 

[Dick7Access]

Beetle,
Here is a 2003 version if you are intreasted. If not it can wait till Monday. I am sure you have life besides Access. Right now my wife is still in a rehab hospital so I am using that free time to study.

 

[Dick7Access]

Beetle, Bob, and Anybody,
I finally figured it out. No need for you to trouble yourself anymore. Boy I am getting desnse. I guess I am not drinking enough beer!!
Thanks for your help.

 

[Dick7Access]

Beetle, Bob and Anybody,
I thought I posted yesterday that no one needed to reply as I had figured out the combo box and the relationship between the PK and the FK, but today I do not see the post. Anyways, yesterday I did have the combo working quite well for the order form combo box selecting the desired customer, however today as I tried to duplicate a combo box to select a product it isn’t happening. I spent over 4 hours between last night and today on goggling it and trying different combinations of combo boxes. All I can get it to do is saved the Id in the orders table. If anybody is interested and looking at it and telling me what I am doing wrong I would appreciate it.