control value

[Guest]

Hi! I'm new on this. Can anyone help me?
I want to do a form where I put a combo Box (cboCategory) to select a
category of produts.
In the AfterUpdate event I activate and selected another comboBox
(cboProducts) that gives me only the produts from that category. So far I
already achieved.
Now I have another combox on the form with the name of diferents suppliers
from that products, that have diferent prices according with the supplier.
What I want is that when I select the Supplier Name, a field called
"ProductPrice" will automatically fulfill with the price for that
category/product that the supplier selected is asking.
Hope this is understandable?
Thanks

 

[Jeff Boyce]

In the AfterUpdate event of your cboSupplier combo box, you can "fill" a
textbox with a value from the SupplierProduct table. Make sure your
cboSupplier combo box uses a query that returns the SupplierProduct price as
one of the columns (say, the 3rd column).

Use:
Me!txtYourPriceTextBox = Me!cboSupplier.Column(2)
in the AfterUpdate event. The .Column() method is zero-based (starts
counting at 0, 1, 2...)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for your reply, Jeff.
I tried your solution, but it did not result. When I click on the
cboSupplier it returns twenty or more items of the same supplier and 10 or 15
from another and so on.
Obviously is the structure of my database that is wrong. As I said, I’m an
“amateur†in Access.
I will try to give you an idea of what I want to do with this Database. I
hope I will not bore you
to much with this, and I hope you or anybody else help me with this.
Database has 4 Tables:
tblClient (Date;ClientName;Category;Product;Supplier;UnitPrice).
tblCategory (CategoryID;CategoryName;Description).
tblProducts (ProductID;ProductName;CategoryID;SupplierID;UnitPrice) and
tblSupplier (SupplierID;CompanyName;Phone;Fax)
The products are classified in different categories
All the suppliers furnishes all the products but with different prices
Every Client is represented by a supplier (like an insurance company).
What I want is when I open the frmClient (based on tblClient) I’ll type the
date, the client name, I will select from the cboCategory the products I
want, (I already know how to do this) then I select the product in the
cboProduct, and after this when I select in the cboSupplier the CompanyName
it automatically fills the UnitPrice field with the price that the supplier I
choose is paying for that product.
Hope you’ll understand my doubts, and help me on this.
Best regards
Silvex


"Jeff Boyce" escreveu:

 

[ajetrumpet]

Silvex,
Is the supplier field in tblClient the same as the CompanyName field in tblSupplier??
If so, since your combo box values are all located in one table (tblClient), could you write a SELECT statment in the rowsource?? Rowsourcetype = Table/Query

SELECT tblClient.unitprice WHERE (tblClient.category=Me![cbocategory]
AND tblClient.product=Me![cboproduct]
AND tblClient.supplier=Me![cbosupplier];

You could write this as a query too and run it via a macro on say the "OnGotFocus" or "OnEnter" event of the "unitprice" control....

 

[Jeff Boyce]

Tell us more about how the data in your situation is related. For instance,
can a supplier offer more than one product? Offer one product at more than
one price? Can more than one supplier offer the same product?

The tables you outlined seemed to jam clients together with products. Does
that mean a client can only have one product?

Step back from what you've already done and consider what the various
"things" are you want to save information. I'll guess you have:
* Clients
* Products
* ProductCategories
* ProductSuppliers
* ClientOrders (for products)
and maybe
* ClientOrderDetails (one record for each item in an order)

Your turn -- can you describe the things and how they are related without
resorting to database terminology? Imagine you were explaining this to an
89-year-old grandmother...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Ok Jeff
Thanks again for your reply, and yes your “grandmother idea†is the best way
to explain my 19th century knowledge of Access.
What I want is something like this:

The Clients are or may be different ( I can register the same client in
different dates but I’ll do a different record – one for each date- this in
meant to, later, query the results from between dates!!)
I have a list of five or six categories each one with a list of different
products
I have a list of ten or fifteen suppliers
Each supplier furnishes all the products, but with different prices from one
supplier to another.

I want to create a Form where I insert this data:

1) The Form will look like this: Frm Client
Date: ___(Field Date)_______
Name: ___(Field ClientName)_____________________
Age :__(fldAge) ___
And perhaps other fields to identify the client ….
Category: __cboCategory____
Product: __cboProduct___ (selected from the AfterUpDate cboCategory)
Supplier: ____cboSupplier____
UnitPrice: __txtBox (with the Price for that product that the supplier is
paying)
2) In Date field I’ll type the date of the record.
3) I type the ClientName, which can be a person or a company or whatever…
4) I type all the other fields that identify that particular Client
5) In the cboCategory I select the kind of Job I did to that Client (Ex:
A-Consultation; B-Surgery; C-Exams ; D-Medications; …)
6) In the cboProduct appear the products that are related to the Category I
selected (Ex: A-Consultation = 1)Gyne 2)Obst 3)Urgency 4)… B-Surgery =
1)Laparotomy
2)Salpingectomy 3)Hernia repair 4)…. and so on. As I told you I already
know
how to do this, or at least I think I Know…!?
7) Then when I select the supplier (which is the insurance company that
will pay for the Job - Ex: AXA Insurance; MedicalInsurance; Doctorhelp;….)
the Unit Price txtBox will automatically fills with the price that the
Insurance company pays for that product, regardless of the client I inserted.

Example1:
Date: 16-07-2007
Name: Silvex
Age:----
Phone:----
Category: Surgery
Product: Hernia repair
Supplier: AXA
Unit Price: fills automatically according with the price AXA pays for Hernia
Repair

Example2:
Date: 25-06-2007
Name: Carol Stwart
Age:42
……
Category: Consultation
Product: Gyne
Supplier: Medical Insurance
Unit Price: fills automatically according with the price Medical Insurance
pays for Gyne Consultation

Example3:
Date: 5-07-2007
Name: Catherine Boulevard
……
Category: Surgery
Product: Hernia Repair
Supplier: Medical Insurrance
Unit Price: fills automatically according with the price Medical Insurance
pays for Hernia Repair

Hope this will give some clues and help you to understand my “questâ€
Thanks one more time, sorry for my bad English.
Regards
Silvex


"Jeff Boyce" escreveu: