Linking information

[HoustonOil]

I am having a hard time linking some certain information. I think I have my relationships formatted correctly but not all the information is connecting.

For example: I have a form where I am creating a sales quote. In the form I have a combo box where I select my product ID. When I select the product ID, the product name automatically populates but the price does not.

I have attached a screenshot of my relationships. Can someone please look it over and see if anything stands out to you as being incorrectly formatted? Or offer any advise in getting my price to connect to the product ID I enter?

 

[boblarson]

Something doesn't look right as it looks like you have ONLY one-to-one relationships. That doesn't make sense. Especially Customers to Orders, OrderDetails to Orders, and OrderDetails to Products.

You must have the foreign keys set to no duplicates and they shouldn't be.

 

[HoustonOil]

I went in and double checked and all my relationships are labeled one to many, but I did not have enforce referential integrity checked which I do now. So the 1 and sideways 8 is showing now. Is that what you meant by something looks wrong?

My products table has the price in the price column, why is that price not carrying over in to my order details table if the two have a relationship?

Something doesn't look right as it looks like you have ONLY one-to-one relationships. That doesn't make sense. Especially Customers to Orders, OrderDetails to Orders, and OrderDetails to Products.

You must have the foreign keys set to no duplicates and they shouldn't be.

 

[boblarson]

My products table has the price in the price column, why is that price not carrying over in to my order details table if the two have a relationship?

Forgive me if I sound condescending as it isn't meant to be that way. So, you are entering data only through forms, correct? If so, and I hope so, the problem would likely be that you haven't included the appropriate fields into the form, or forms.

Without much more information about what you are trying to do, and how you are trying to do it, I can't help much more. If you could post a copy of the database, it would be extremely helpful as I am more visual and can spot problems much more quickly that way if I can open it up and play with it to see what is set wrong. That's just my preferred way of doing things.

 

[The_Doc_Man]

why is that price not carrying over in to my order details table if the two have a relationship?

The relationship is not enough. I cannot download what you sent in because of my site's rules, so here is what I think you need to hear. If you knew this, sorry. I'm at a government site that limits downloads so I must go by your words alone.

When you use a combo box to pull down info, pull down EVERYTHING you wanted to pull down from that table. Then, on the form having that box, make the relevant control names on the form match the field names pulled down from the combo box. If you do that, at least in theory you will populate the matching control values. I.e. if you select based on product number but have the combo box include a (hidden) price field, and if you have a control named [Price], it will be populated.

Having a relationship between two tables and also having 2 bucks will get you a small cup of designer coffee - if you have enough loose change to cover the tax. A relationship is mostly passive, just a guideline for Access. What that relationship does is give Access extra information on how to proceed when you perform ordinary data changes or design functions.

For instance, if you try to build a query that joins two tables, some of the linkage is automatic if a relationship exists between the tables. You won't have to write LEFT INNER JOIN in the SQL because Access will know enough to do it right.

If you have a parent-child table and try to build matching parent-child forms, the sub-form wizard won't have to ask you the names of the linking fields. If the relationship will tell Access what fields are related.

If you try to delete a parent record in a way that would leave orphans, a properly defined cascade relationship would allow you to either clean up everything automatically - or be notified that you are about to create orphans if you click OK on the delete confirmation.

But having a relationship doesn't cause automatic data movement between point A and point B. You still have to arrange for that to work correctly.

 

[boblarson]

Thanks for the post Doc Man as I sometimes have trouble putting into words that which needs to be written.

 

[HoustonOil]

You can sound like anything you want, or call me anything you want (including stupid) if you get this figured out for me

I am trying to upload a copy of the database but it is saying upload failed.
The file is 3.88MB, is that too big to upload?

Forgive me if I sound condescending as it isn't meant to be that way. So, you are entering data only through forms, correct? If so, and I hope so, the problem would likely be that you haven't included the appropriate fields into the form, or forms.

Without much more information about what you are trying to do, and how you are trying to do it, I can't help much more. If you could post a copy of the database, it would be extremely helpful as I am more visual and can spot problems much more quickly that way if I can open it up and play with it to see what is set wrong. That's just my preferred way of doing things.

 

[boblarson]

393Kb is the max here. You can email it to me if you want. I'll PM you with my email address.

 

[HoustonOil]

That would be great

393Kb is the max here. You can email it to me if you want. I'll PM you with my email address.

 

[boblarson]

Is there a reason why your OrderID is text and not an autonumber?

 

[boblarson]

Okay, I have it working. Here’s a list of what was wrong and what got changed:

1. Your ID fields should be autonumber and not text.
2. I fixed your fields and changed the relationships
3. Unless you absolutely need the product ID, it is now hidden but when you select the combo box it populates the price text box because the price is now included in the query for the product combo.
4. Also I would just hide any autonumbers on the forms (if you have a purchase order number or something like that, you should add a field to manually enter it. The associated records will be tied to the order via the autonumber and so you won’t have maintenance problems.

I sent you the revised database to look at to see what I did.

 

[HoustonOil]

Thanks Doc, nicely put together.

The relationship is not enough. I cannot download what you sent in because of my site's rules, so here is what I think you need to hear. If you knew this, sorry. I'm at a government site that limits downloads so I must go by your words alone.

When you use a combo box to pull down info, pull down EVERYTHING you wanted to pull down from that table. Then, on the form having that box, make the relevant control names on the form match the field names pulled down from the combo box. If you do that, at least in theory you will populate the matching control values. I.e. if you select based on product number but have the combo box include a (hidden) price field, and if you have a control named [Price], it will be populated.

Having a relationship between two tables and also having 2 bucks will get you a small cup of designer coffee - if you have enough loose change to cover the tax. A relationship is mostly passive, just a guideline for Access. What that relationship does is give Access extra information on how to proceed when you perform ordinary data changes or design functions.

For instance, if you try to build a query that joins two tables, some of the linkage is automatic if a relationship exists between the tables. You won't have to write LEFT INNER JOIN in the SQL because Access will know enough to do it right.

If you have a parent-child table and try to build matching parent-child forms, the sub-form wizard won't have to ask you the names of the linking fields. If the relationship will tell Access what fields are related.

If you try to delete a parent record in a way that would leave orphans, a properly defined cascade relationship would allow you to either clean up everything automatically - or be notified that you are about to create orphans if you click OK on the delete confirmation.

But having a relationship doesn't cause automatic data movement between point A and point B. You still have to arrange for that to work correctly.

 

[HoustonOil]

Bob

Does my ID field have to be number? My customer ID field needs to be letters and numbers ( TRI645 ). Can I use something like this? Will it cause problems?

Okay, I have it working. Here’s a list of what was wrong and what got changed:

1. Your ID fields should be autonumber and not text.
2. I fixed your fields and changed the relationships
3. Unless you absolutely need the product ID, it is now hidden but when you select the combo box it populates the price text box because the price is now included in the query for the product combo.
4. Also I would just hide any autonumbers on the forms (if you have a purchase order number or something like that, you should add a field to manually enter it. The associated records will be tied to the order via the autonumber and so you won’t have maintenance problems.

I sent you the revised database to look at to see what I did.

 

[boblarson]

You can ADD an additonal field to store that information as such but use the Autonumber ID's to link the data, just like I have it now.

 

[HoustonOil]

Ok, thanks

You can ADD an additonal field to store that information as such but use the Autonumber ID's to link the data, just like I have it now.