Combo boxes Linking to Other Data

[crystal0streaks]

I'm still new to access and I've tried many things to automatically input data into specific columns and it hasn't worked. In my main table, I have combo boxes for [MODEL#] and I would like to have the combo box link to the [PRICE] column so it will insert the appropriate price. I have the Models and Prices in a query as well.

example...
MODEL# ---->PRICE
X-1000B --->$183
X-1000C --->$250
X-1000CH -->$301
X-1000CE -->$349
X-1000SB -->$149

If there is anyway I can link the two (models with prices) together, please let me know. Thank you!


-crys

 

[PHV]

In the AfterUpdate event procedure of the combo you may use something like this (VBA):
Me![name of price control] = Me![name of combo].Column(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I think part of your frustration is coming from the terminology you are using. You keep referring to ComboBoxes (I've seen 2 or 3 postings about this topic so far this morning)....however, what you are REALLY talking about is a LOOKUP FIELD.

Lookup TABLE - A table that contains a Primary Key and maybe a description. I use these for the Verdicts that are allowed for Jury Trials. There's a VerdictCode and VerdictDescription (this allows me to store GU, but report Guilty).

Lookup FIELD - an evil display tool used in Access that puts a drop down list in a TABLE. Should not be used - see the Access 10 commandments!

ComboBox - a control placed on a FORM that allows the user to select preordained information (like you would find in a LOOKUP TABLE) - what was the verdict? Guilty, Not Guilty (but only store the PK - GU or NG).

What exactly are you trying to accomplish? You want to update a field in a table depending on what the user selects. You want to update Price based on what Model is selected? What are the tables that you have that contain the Model and Price information?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[crystal0streaks]

You want to update Price based on what Model is selected?"

Yes

"What are the tables that you have that contain the Model and Price information?"

Ok, this gets tricky, there are three types of customers I'm dealing with, SystemsInt, Dealers, and Distributors, and all of them can order the same Model, but it is at different prices. So I have a query that lists the Model#, SystemsInt.Price, Dealer.Price, and Distributor.Price. Due to having the three different types of customers, I have three different (main) tables. If it is possible (I've been reading about having combo boxes [yes, I've been using combo boxes] determine the information on different combo boxes) having the first column the type of customer that will determine the price of the models. Then, choose what model they are ordering from the combo box I have set up, and hopefully, have the next column [PRICE] be automatically filled in determining the type of customer and the model selected. If the linking the customer types to the prices cannot be done, that's fine, the main thing is having the price be filled in automatically. Again, if it is possible.
Hopefully this is understandable.

-crys

 

[lespaul]

First before we get to your actual question....have you read the 'Fundamentals' document linked below? Unless there is completely different information stored about the different customer types, you should probably only have a single customer table with a field that determines if the customer is SystemsInt, Dealer or Distributor. If you are at a point in this project where you can change your table structure, I would recommend it. You will find that things are just going to get trickier if you don't normalized (see document linked below). If you also normalize the tables that store the pricing information you would find that what you are trying to accomplish is very easy to do.

I'm still not clear on what you want to do with the price information once you get it? You say:

have the next column [PRICE] be automatically filled

where's "the next column"?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[crystal0streaks]

I'm reading the document, but I'd still like to keep a dialogue with you, so to answer your statement...

Unless there is completely different information stored about the different customer types, you should probably only have a single customer table with a field that determines if the customer is SystemsInt, Dealer or Distributor.

Yes, I have a Table labeled "Customers" that has the customers name, address, phone, fax, and what type of customer they are.

As to Normalizing the tables, I'm reading the document, and some of the figures that I believe are suppose to show images are not showing up, so it's a bit difficult for me to understand as I am a visual learner.

Thank you for your interest and help so far.

-crys

 

[crystal0streaks]

where's "the next column"?"

In the same Table that we select the Model#, the very next column (to the right of the Model#).

"I'm still not clear on what you want to do with the price information once you get it?"

When I select the Model#, the price for the Model# will be filled in the Price column (the the right of the Model#).

-crys

 

[lespaul]

It sounds like you want to UPDATE a RECORD in the TABLE. On some form the user has selected a CUSTOMER and a MODEL. What table do you want to update?

Why don't you tell me about your tables......

A normalized structure (based on the little information I have) would be:

Customer
CustomerID (PK)
Name
Address
phoneNumber
CustomerType (SystemsInt, Dealer or Distributor)

Model
ModelID (PK)
Description

ModelPrices
ModelPriceID (PK)
ModelID
CustomerType
Price

Orders
OrderID
CustomerID
OrderDate

OrderLines
OrderID
LineNumber
ModelIDOrdered
Quantity
ModelPrice

Is this similar to what you have? If not, what DO you have?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[crystal0streaks]

The examples of the Tables are similar, but I know for a fact my order tables are NonNormalized, but I did that specifically for my employer who is access-illiterate (worse than I am) so all he has to do is type in the information and it will link to the Reports that he needs.

The Order Tables I have go something like this...(be prepaired)

Current_Date
Company
Invoice#
PO#
Terms
Due Date
Rep
Ship Date
Ship Via
Account#
Special Instructions
UPS Tracking #
Shipping
Quantity1*
Description1*
Description1b*
Unit Price1*
Hyperlink
Past Due
Paid

*=Repeate to 7

I know this table is *really* scewed up according to the Normalizing Rule, but it works for him.

-crys

 

[lespaul]

[aside]what do you do if someone orders more than 7 items?[/aside]

So what you are trying to do is when the user selects a model in the ORDER you need to lookup the price and when the order is finished, you need to update the record associated with the order to reflect the price. How do you know if you need to update Price1 or Price7?

 

[crystal0streaks]

If the person orders more than 7 different items, a new order form (Invoice [Report]) will be made for him/her. There are only 7 slots on the Invoice (Report) so a new one will have to be made anyway. The 7 will not change, so we do not have to worry about that.

So what you are trying to do is when the user selects a model in the ORDER you need to lookup the price and when the order is finished, you need to update the record associated with the order to reflect the price

That sounds correct, currently what I've done is have a combo box for both the Model and the Price. Only with the price, the combo box has two columns so the prices line up with the model. This works for the time being, but it would be more efficient if the price would be automatically updated (using your terms) to the correct model/price connection.

-crys