A 2010 Table no design view

[Dick7Access]

My last two posts in this thread are not showing. Somebody please clue me in. Did I not post correctly, or did the powers to be decide that what I posted is not appropriate for some reason?

 

[Beetle]

My last two posts in this thread are not showing. Somebody please clue me in. Did I not post correctly, or did the powers to be decide that what I posted is not appropriate for some reason?

Must be something on your end because I can see both your posts. I'll take a look at your example db in a bit.

 

[Beetle]

You combo boxes are working as they should. They are storing the Customer and/or Product ID values but displaying the name. What is it that you perceive as the problem?

I do have a another tip for you with regards to combo boxes. Although you can have multiple visible columns in a combo box, it will only display the first visible column in its un-expanded state. However, you can concatenate more than one field into a single column in the combo box Row Source. A typical scenario for this would be a Customer name, where it is usually helpful to be able to see the whole name in the displayed column of the combo box. So in the Row Source you use a field like;

FullName: [NameLast] & ", " [NameFirst]

BTW - I know this is just a sample db you are using for learning purposes, so this is just food for thought, but what do you do if you want to be able to sell more than one product on a single order?

 

[Dick7Access]

Beetle,
Thanks again for answering. I knew I would eventually have to get into more complex orders but I am not multitasking. In fact it should be against the law for a guy like me to be allowed to even buy Access. <G> Once I have the concept of table normalization, and combo box fully understood I will move on to something else. I can build db, and for the most part make it do what I want, but I do it by throwing every think into a few tables and using a lot of queries and sub forms. I have been wanting for a long time to make my db’s more professional. Your nudging got me started. I have revise that practice db so many time I forget what one I sent out last so here is the latest version.
On the order form (frmOrders) I have the combo box (Last Name) working fine that adds the desired name to the order form, and the combo (Item) will add the correct Id to the productId (prodId) field, but when you go to the orders table (tblOrders) the correct Id shows, but the itemOrdered field is not populated, which, of course, would not mean much to the end user, printing an order to shipping etc. If you will look at the Database tools > relationships you will see I also do not have a join as I have tried every type and nothing seems to work.

 

[boblarson]

Dick:

I've taken a look at your database and have some comments.

1. Starting with the tables (relationships window)
a. The Msys tables should NOT be in the relationships window.
b. The Orders table should also have an ORDER DETAILS table. Otherwise you can only order ONE item ever on an order, or you will be duplicating information.
c. ID fields should be Long Integer (or Autonumber, which are Long Integers) and not TEXT.
d. Why do you have a states table but also have the state entered in the Customers table?
e. The OrderID (OrID) should NOT be in the products table.
f. Customer ID (CuID) should NOT be in the States Table.


2. Next, the customers form it is Entering, not Intering.

3. On the Orders form:
The Customers table should NOT be in the form's record source.


Well, that should keep you busy for a while.

 

[Dick7Access]

Bob Thanks, Duh!

 

[Dick7Access]

Bob,
The Msys tables should NOT be in the relationships window.
I deleted them. I did not put them there and though that it was something the system needed. Do you have any idea where they came from?

I know about having only number Long Integer, but I could not find a table with a text for a key. Which table did you see that in?

Why do you have a states table but also have the state entered in the Customers table?
Oh! I thought I need that for a look up with a combo box instead of having to type in each state on a new customer.

e. The OrderID (OrID) should NOT be in the products table.
f. Customer ID (CuID) should NOT be in the States Table.
I will work on that.




On Inereing/Entering, my spell checker is still in the rehab hospital!

 

[Beetle]

Looks like Bob already answered some of your questions, but I do have a comment based on something you said;

On the order form (frmOrders) I have the combo box (Last Name) working fine that adds the desired name to the order form, and the combo (Item) will add the correct Id to the productId (prodId) field, but when you go to the orders table (tblOrders) the correct Id shows, but the itemOrdered field is not populated, which, of course, would not mean much to the end user, printing an order to shipping etc.

It sounds like you are expecting that you and/or your users should be able to view the data (and see all the related values) directly in the tables. This is not the case. First, you should NOT allow the users direct access to the tables. Even as the developer you would not enter or view data directly in the tables except perhaps during the design process. Second, in a relational database the child table stores only the PK value of the related table in its FK field. You should not, for example, expect to look at an Orders table and see a Customer Name or an Item Name. You would only see the ID values. Data display and entry is done at the query, form and report level where you either join tables in a query to display the related values or you use combo boxes or the like on forms and reports.

 

[Dick7Access]

Beetle Thank you so many times. You hit the nail on the head as the saying goes. That is exactly what I am trying to do. So the question is, if they are not to look at the table, and you are absolutely right as I had a client go into a form in design view one time and correct all my misspellings in the data fields and then calls me and said nothing works <G> What I should have said is where is the end user going to look up all the orders, which of course will be on a form, or best a dataheet view, but how am going to populate the form with all the orders if the product item doesn’t show on either the Entering form, or the separate “All Ordersform” that I would make at a later date, or even in the table. I would not even be able to pull the item with a query. Now on the Order entering form how is the end user going to know if they have entered the right product if only the product ID is showing. Now correct me if I am wrong, but it seems that you and Bob are saying that to enter a product from a combo box I will have to have use a orders detail table and a sub form of the detals on the order form. Is that correct?
It also seems to me that I am going to run into the same probem when I try to add States to the Customer form/table with a comb box. If I am adding a new customer it would seem better to use a combo box to add the state, either from a state only table or God forbid adding them to a list in a combo box property sheet.
Ps. I am not sure that I am glad that you got me started (just kidding) As one of my bosses you to say when he wanted to add something new to my duties, now Dick first remember all change is hard. Actually this is helping to get my mind of off now having my wife around especially as it might be permanent if she doesn’t heal better.

 

[Dick7Access]

. Another question Beetle, did you find the Foreign key that Bob said I had as a text instead of a Long integer?
Ps. Did you actually purpose a bill in the Senate to keep people like me from buying access. If so wiltl it only apply to CO or to FL also?


Beetle or anyone. I poste this yesterday and someone suggested trying another browser, but I have one browser, IE, but it don’t think the problem is with the browser. It is strange! I If link from my favorites and open up Access Forum and go to the thread that I am corresponding with Beetle and Bob and my last post to Beetle is not showing. If I go to my email notification and click on that link and go to the same tread it shows up

 

[Beetle]

...but how am going to populate the form with all the orders if the product item doesn’t show on either the Entering form...

If the Entering form you're talking about here is your existing frmOrders, the ProductItem does display there already - in your combo box.

...or the separate “All Ordersform” that I would make at a later date...

Again, you would use combo boxes to display the ProductItem, based on the ProductID, or you would create a query that joins the necessary tables and returns all the fields you need and then use that query as the record source of your form. Which method you choose depends on what the purpose of the form is.

...or even in the table

It wouldn't, and shouldn't, show in the table.

Now correct me if I am wrong, but it seems that you and Bob are saying that to enter a product from a combo box I will have to have use a orders detail table and a sub form of the detals on the order form. Is that correct?

What Bob was pointing out, and what I hinted at in one of my previous posts, is that if you want to be able to have more than one product on a given order (which, in most real world situations, you would), then you need another table to store the multiple products for each order. A table like this would typically be named OrderDetails or something similar.

If I am adding a new customer it would seem better to use a combo box to add the state, either from a state only table

That's fine, but since your States table has an Autonumber Primary Key (StateID) then you would only store the StateID in the Customers table as a Foreign Key, not the State Name.

Actually this is helping to get my mind of off now having my wife around especially as it might be permanent if she doesn’t heal better.

My condolences to you and your wife. Here's hoping she will improve and get to come home soon.

Did you actually purpose a bill in the Senate to keep people like me from buying access

LOL. Just remember that some of us were once where you are now, struggling to figure all this out. Just part of the ongoing quest to learn new things.

 

[Dick7Access]

Merci beaucoup

 

[pr.eduardo]

Hi guys!

I'm new on this. Look I search and communicate via chat with microsoft and nobody tell me what I found here. The things is that I'm using a Template of Contacts and I can't manage the Design View neither the Remove Layout option because I was doing some changes and I can't move the labels a free will. Now reading this post I found that is because was a Web Template. Ok, it's there any option to obtain that function on my Access? I really like that template of contacts is really simple and fill the work that I wanna do.

Sorry for my English and thanks for the Help!