If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design
Hi!
I'm trying to make a Database for registration of my clients, and I'm stucked in one code. (I'm a "rockie" on the Access, unfortunately not like Hamilton in F1) The struture is as follows: Database has 4 Tables: tblClient (Date;ClientName;Category;Product;Supplier;UnitPri ce). tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID;SupplierID;UnitP rice) 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. Can anybody help me please! Regards |
#2
|
|||
|
|||
Database Design
To start, your tables need modified:
TblSupplier SupplierID SupplierName Phone Fax TblClient ClientID ClientName Other client contact fields SupplierID TblCategory CategoryID CategoryName Description TblProduct ProductID ProductName CategoryID TblSupplierProductPrice SupplierProductPriceID SupplierID ProductID UnitPrice TblOrder OrderID OrderDate ClientID SupplierName TblOrderDetail OrderDetailID OrderID CategoryID ProductID Quantity UnitPrice Two things to note --- 1. Supplier is recorded in TblOrder rather than SupplierID. A client may change suppliers at one time or another. Recording supplier allows the supplier to be automatically filled in at all times with the client's supplier when ClientID is recorded. 2. UnitPrice is recorded in TblOrderDetail rather than SupplierProductPriceID. Supplier prices may change over time. Recording UnitPrice allows the UnitPrice to be automatically filled in at all times with any product price when the product and supplier are recorded. You need to abandon your FrmClient and create a new form/subform. Name the main form FrmOrder and it needs to be based on TblOrder. The main form needs a combobox named ClientID for entering ClientID. The rowsource for the query needs to be a query that includes TblClient and TblSupplier. The fields in the query need to be ClientID, ClientName, SupplierID and SupplierName. Set the Bound Column proprty to 1, Column Count to 3 and Column Width to 0;2;0. The main form needs a hiddewn textbox named SupplierID for the SupplierID field and a textbox named SupplierName for the SupplierName field. When a client is selected in the combobox, the client's current SupplierName will be automatically entered in the SupplierName textbox and the SupplierID will be automatically entered in the SupplierID hidden textbox. Name the subform SFrmOrderDetail and it needs to be based on TblOrderDetail. The subform needs a combobox named CategoryID for selecting CategoryID. The rowsource for the combobox can be TblCategory. Set the Bound Column property to 1, Column Count to 2 and Column Width to 0;1.5. The subform needs a combobox named ProductID for selecting ProductID. The rowsource for the combobox needs to be a query that includes TblProduct and TblSupplierProductPrice. The fields in the query need to be ProductID,ProductName and CategoryID from TblProduct and SupplierID and UnitPrice from TblSupplierProductPrice. Set the criteria for CategoryID to: Forms!FrmOrder!SFrmOrderDetail.Form!CategoryID. Set the criteria for SupplierID to: Forms!FrmOrder!SupplierID. Set the Bound Column property to 1, Column Count to 5 and Column Width to 0;1.5;0;0;1 Put the following code in the AfterUpdate event of the combobox: Me!UnitPrice = Me!ProductID.Column(4) Add a textbox to the subform for the UnitPrice field. When a product is selected in the combobox, the product's current UnitPrice will be automatically entered in the UnitPrice textbox. The subform will be a continuous form. The ProductID combobox will be dependent on the CategoryID combobox. I haven't included the details on how to set this up. You can look the previous postings in the newsgroup for setting this up - it is often addressed. PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications "silvex" wrote in message ... Hi! I'm trying to make a Database for registration of my clients, and I'm stucked in one code. (I'm a "rockie" on the Access, unfortunately not like Hamilton in F1) The struture is as follows: Database has 4 Tables: tblClient (Date;ClientName;Category;Product;Supplier;UnitPri ce). tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID;SupplierID;UnitP rice) 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. Can anybody help me please! Regards |
#3
|
|||
|
|||
Database Design
Steve
I tried all the steps you send to me, but they didn´t work. I think is something to do with the relationships of the tables, or with the properties of the comboBoxes, or perhaps I’m just to “green” or ignorant on this, to understand your help. Please forgive my bad English and my worst knowledge of database design and if you have some patience to read this again: What I want something like this: 1) The Form will look like this : Frm Client Date: ___(Field Date)_______ Name: ___(Field ClientName)_____________________ Age:__(fldAge) … other fields in tblClients …. Category: __cboCategory____ Product: __cboProduct___ (selected from the AfterUpDate cboCategory) Supplier: ____cboSupplier__(select from tblSupplier)__ UnitPrice: __txtBox (with the Price for that product that the supplier is paying) 2) In Date field I’ll type the date of my Job 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 the Client (Ex: Consultation;Surgery; Exams;…) 6) In the cboProduct appears the products that are related to the Category I selected (Ex: Consultation=1)Gyne 2)Obst 3)Urgency 4)… Surgery= 1)Laparotomy 2)Salpingectomy 3)Hernia repair 4)…. and so on. As I told you I already know how to do this. 7) Then when I select the supplier ( which is the insurance company that will pay for the Job - Ex: AXA Insurance; MedicalInsurance;Doctorhelp;….) the UnitPrice txtBox will automatically fills with the price that the insurance company pays for that product, regardless of the client I insert. Thanks for your Hellp Silvex "Steve" escreveu: To start, your tables need modified: TblSupplier SupplierID SupplierName Phone Fax TblClient ClientID ClientName Other client contact fields SupplierID TblCategory CategoryID CategoryName Description TblProduct ProductID ProductName CategoryID TblSupplierProductPrice SupplierProductPriceID SupplierID ProductID UnitPrice TblOrder OrderID OrderDate ClientID SupplierName TblOrderDetail OrderDetailID OrderID CategoryID ProductID Quantity UnitPrice Two things to note --- 1. Supplier is recorded in TblOrder rather than SupplierID. A client may change suppliers at one time or another. Recording supplier allows the supplier to be automatically filled in at all times with the client's supplier when ClientID is recorded. 2. UnitPrice is recorded in TblOrderDetail rather than SupplierProductPriceID. Supplier prices may change over time. Recording UnitPrice allows the UnitPrice to be automatically filled in at all times with any product price when the product and supplier are recorded. You need to abandon your FrmClient and create a new form/subform. Name the main form FrmOrder and it needs to be based on TblOrder. The main form needs a combobox named ClientID for entering ClientID. The rowsource for the query needs to be a query that includes TblClient and TblSupplier. The fields in the query need to be ClientID, ClientName, SupplierID and SupplierName. Set the Bound Column proprty to 1, Column Count to 3 and Column Width to 0;2;0. The main form needs a hiddewn textbox named SupplierID for the SupplierID field and a textbox named SupplierName for the SupplierName field. When a client is selected in the combobox, the client's current SupplierName will be automatically entered in the SupplierName textbox and the SupplierID will be automatically entered in the SupplierID hidden textbox. Name the subform SFrmOrderDetail and it needs to be based on TblOrderDetail. The subform needs a combobox named CategoryID for selecting CategoryID. The rowsource for the combobox can be TblCategory. Set the Bound Column property to 1, Column Count to 2 and Column Width to 0;1.5. The subform needs a combobox named ProductID for selecting ProductID. The rowsource for the combobox needs to be a query that includes TblProduct and TblSupplierProductPrice. The fields in the query need to be ProductID,ProductName and CategoryID from TblProduct and SupplierID and UnitPrice from TblSupplierProductPrice. Set the criteria for CategoryID to: Forms!FrmOrder!SFrmOrderDetail.Form!CategoryID. Set the criteria for SupplierID to: Forms!FrmOrder!SupplierID. Set the Bound Column property to 1, Column Count to 5 and Column Width to 0;1.5;0;0;1 Put the following code in the AfterUpdate event of the combobox: Me!UnitPrice = Me!ProductID.Column(4) Add a textbox to the subform for the UnitPrice field. When a product is selected in the combobox, the product's current UnitPrice will be automatically entered in the UnitPrice textbox. The subform will be a continuous form. The ProductID combobox will be dependent on the CategoryID combobox. I haven't included the details on how to set this up. You can look the previous postings in the newsgroup for setting this up - it is often addressed. PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications "silvex" wrote in message ... Hi! I'm trying to make a Database for registration of my clients, and I'm stucked in one code. (I'm a "rockie" on the Access, unfortunately not like Hamilton in F1) The struture is as follows: Database has 4 Tables: tblClient (Date;ClientName;Category;Product;Supplier;UnitPri ce). tblCategory (CategoryID;CategoryName;Description). tblProducts (ProductID;ProductName;CategoryID;SupplierID;UnitP rice) 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. Can anybody help me please! Regards |
Thread Tools | |
Display Modes | |
|
|