Making customer data populate a work order form?

  • Thread starter Thread starter HollyS
  • Start date Start date
H

HollyS

I have two tables joined in a query - a customer master and a work order table
(joined by a primary cust master id key)

I have a form based on this query that allows users to enter a new work order -
I put in a combo box that drops down (so they can type in the first couple of
letters of the customer name and it will find the customer - this is how my
friend I am working on the database for wants it).

What I just can't figure out is.....how can I fix it so that when the customer
name is found it populates the other customer fields (ie address, telephone
etc) with the customer info from the customer table?

Please help - thank you!!
 
The other customer fields only need to be viewed in a form or report. They
never need to be redundantly stored in a table. The 2 tables should not be
joined in a query, rather they should be linked as form and subform. You
actually need 3 tables, one for the customer which will supply all the data
for the customer which is to be viewed in the WorkOrder form, but linked to
the CustomerID (which is your foreign key in the WorkOrder table).

The second table is the WorkOrder table which contains all the common
information which is required for each work order detail:

CustomerID, WorkOrderDate, TechnicianID, etc.

The 3rd table is the WorkOrderDetails table which contains all the data for
each WorkOrder record.

To answer your question on how to display the customer information, use a
multi-column combobox which selects all the customer data you want from the
Customer table (the rowsource will be something like):

Select CustomerID, CustomerName, Address, City, State, Zip, Phone From
tblCustomers;

Set the column widths like: 0";1.25";0";0";0";0";0"

Now use the following expression to display the address which will be column
3:

=Forms!YourFormName!YourComboName.Column(2)

The column index array is zero (0) based so used 1 less than the column
count as the index number. Repeat the same for the other text boxes used to
display customer data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
HollyS said:
I have two tables joined in a query - a customer master and a work order table
(joined by a primary cust master id key)

I have a form based on this query that allows users to enter a new work order -
I put in a combo box that drops down (so they can type in the first couple of
letters of the customer name and it will find the customer - this is how my
friend I am working on the database for wants it).

What I just can't figure out is.....how can I fix it so that when the customer
name is found it populates the other customer fields (ie address, telephone
etc) with the customer info from the customer table?
Click to expand...

Your work order record should store ONLY the primary key field from your
Customers table. All other data concerning the customer that you want to
*display* when looking at a work order should be done with lookups, a subform,
or by using a query that combines that data with the data from the work order.
This way you store your data only once and in the proper table. In a correct
relational database design there should be very little *copying* of data from
one table to another.

Exceptions to this would be Customer data that is order-specific or time
sensitive. For example if the Customer table contained a Credit Card number or
a discount rate, these would be items that should be copied into work orders
because the work order needs to record what this data is "at the time the work
order is created". For that I would recommend adding these kinds of fields to
the ComboBox as hidden columns and then in the AfterUpdate event of the Combo
you can run code that grabs the data from the additional columns and pushes it
into other controls on the form.

ex:
Me.CustomerDiscount = Me.CustomerCombo.Column(1)
Me.CustomerCreditCardNum = Me.CustomerCombo.Column(2)
 
Hi Holly,

This should be one way. Change the names to suit

Sub Combo10_AfterUpdate()
' Find the record that matches the control's value
Me.RecordsetClone.FindFirst "[ComposerID] = " & Me![Combo10]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

HTH
 
Back
Top