Products/Services Table

[dvvog]

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?

 

[Steve]

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
(e-mail address removed)

 

[Duane Hookom]

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.

 

[Dennis]

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