Products/Services Table
in [Tables]
|
Prev: Splitting database - terrible performance
Next: How can I use relationships to auto-create records in my subform?
From: dvvog on 8 Apr 2010 12:59 I am using Access 2007. I have a database for my Products/Services industry. I have a table containing my products/services, as well as a Work Order Form that is linked to the Products/services table from which I can choose via drop down the products that I am selling to that customer. The Product/services table contains 3 fields; Product, Cost & Price. The Work Order Form also contains those three fields, and when I choose a product from the drop down, the corresponding Price field fills in as well. Now, sometimes (after bargaining of course!) I want to change the price for that specific work order without it affecting the main Products/services table, can anyone help?
From: Steve on 8 Apr 2010 13:45 You should have a separate table for Work Orders. Your work order form should be based on this table. This table design makes the price in the work order table independent of the price in the products/services table. You can leave the price auto fill in the work order form and if you need to change it just move your cursor to the price field and edit it. The end resuly will be no change to the product price in the product/services table and a different product price in the work order table than is in the product/services table. Steve santus(a)penn.com "dvvog" <dvvog(a)discussions.microsoft.com> wrote in message news:F8BB2244-1995-4E47-89D3-E54A2AD58413(a)microsoft.com... >I am using Access 2007. I have a database for my Products/Services >industry. > I have a table containing my products/services, as well as a Work Order > Form > that is linked to the Products/services table from which I can choose via > drop down the products that I am selling to that customer. The > Product/services table contains 3 fields; Product, Cost & Price. The Work > Order Form also contains those three fields, and when I choose a product > from > the drop down, the corresponding Price field fills in as well. Now, > sometimes > (after bargaining of course!) I want to change the price for that specific > work order without it affecting the main Products/services table, can > anyone > help?
From: Duane Hookom on 8 Apr 2010 13:53 You would need to have the appropriate fields in the "child" table to store the values. Typically the combo box for the products/services would contain columns for the price. The after update event of the combo box would have code to set the value of the price field like: Me.txtPrice = Me.cboProdService.Column(1) Columns are numbered from 0. -- Duane Hookom Microsoft Access MVP "dvvog" wrote: > I am using Access 2007. I have a database for my Products/Services industry. > I have a table containing my products/services, as well as a Work Order Form > that is linked to the Products/services table from which I can choose via > drop down the products that I am selling to that customer. The > Product/services table contains 3 fields; Product, Cost & Price. The Work > Order Form also contains those three fields, and when I choose a product from > the drop down, the corresponding Price field fills in as well. Now, sometimes > (after bargaining of course!) I want to change the price for that specific > work order without it affecting the main Products/services table, can anyone > help?
From: Dennis on 8 Apr 2010 23:25
Dvvog, I just did this very same thing. Both Duane, and Steve are correct. I'm just wanted to add a little more detail. Good luck. I have a standard rate for my time, but it is negotiable especially if I realize I can bring something to the table that no else can. I have two tables; tblInvoice and tblInvoiceTrans tables. The tblInvoice table has the parent information for the invoice and the tblInvoiceTrans table has one record for each transaction on the invoice. I also have an employee table called tblEmployee. It contains all of my employee's name and hourly rate. On my Time subform, I have a combo box that provides a drop down list of employee's. When the user chooses an employee, the software copies the employee rate to text box control called txtRate. This text box (txtRate) is bound to a the Rate field on the invoice detail table. Since txtRate is a separate control bound to a field on the tblInvoiceTrans table, I can change it to anything I want on that particular invoice transaction without altering the base rate on the tblEmployee table. Here is the SQL row source for the cboEmployeeId control: SQL Beginning ----------------------------------------------- SELECT qrytblEmployeesD.EmployeeID, qrytblEmployeesD.FirstName & " " & [LastName] AS EmpNm, qrytblEmployeesD.CoName, qrytblEmployeesD.BillingRate FROM qrytblEmployeesD ORDER BY qrytblEmployeesD.CoName DESC , qrytblEmployeesD.FirstName & " " & [LastName]; SQL End ----------------------------------------------------------------- Here is the AfterUpdate event for the cboEmployeeId control: Note, the index in the column count starts at 0 (not 1). So EmployeeId is column 0, FirstName & Last Name is column(1), CoName is column(2), and BillingRate is column(3). Code Begin ----------------------------------------------------------------- Private Sub cboEmployeeId_AfterUpdate() On Error GoTo Err_cboEmployeeId_AfterUpdate Me.txtRate = Me.cboEmployeeId.Column(3) Exit_cboEmployeeId_AfterUpdate: Exit Sub Err_cboEmployeeId_AfterUpdate: Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description, cpstrFormName & " - cboEmployeeId_AfterUpdate") Resume Exit_cboEmployeeId_AfterUpdate End Sub Code End ------------------------------------------------------------------ Me.txtRate is my control on the Invoice Detail form that is bound to the Price field on the tblInvoiceDet table. Good luck. Dennis |