Creating or Editing a record from a form

Brian263 · October 17th, 2004, 05:16 PM

I need to make a form that will select 3 items from 3 different tables.

I have a Job table where I will select from a pull down list the [u]job number</u>.

I have a vendor table where I will select the [u]vendor</u> from a list and I have an Insurance table where I will select the [u]insurance company </u>from.

I need to create a unique record out of this and add it to a new table called Job_detail. I was thinking of using the Job Number and Vendor Abbreviation from the Vendor table and the Insurance Abbreviation from the Insurance table to create this field.

How do I solve this?

jemacc · October 17th, 2004, 10:05 PM

please give structure of your three tables

Here is and example of what you may be looking

create a query

Code:

SELECT Customers.CustomerID, Orders.OrderID, Employees.EmployeeID, ([customers.customerID] & '' & [orderID] & '' & [employees.employeeid]) AS customerdetail
FROM Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID) ON Employees.EmployeeID=Orders.EmployeeID;

On your form base the three combo box based on your three fields

customerID - from Customer Table
OrderID - from order Table
EmployeeID - from Employee table
customerdetail= concatenation of all three fields (make sure this field exists in your table

when you create your combo box on customer make sure the source is like this

Code:

select distinct customerID from customers

and do the same for your other two fields

When you select each of the three drop downs, the customerdetail will be based on your selections.

I can work on another method but try this first

Jaime E. Maccou
Applications Analyst

mmcdonal · October 18th, 2004, 10:59 AM

Are you creating a junction table called Job_Detail?

If so, create a field in the Job_Detail table in Design View called JobNumber (for example), and then select the Lookup Wizard and look up to the Job Table for the number.

Do the same for each of the other two look ups.

Then when you create the form, the combo boxes will be there looking up to the Job, Vendor and Insurance tables.

No need to write queries or code for this.

mmcdonal

Brian263 · October 18th, 2004, 11:42 PM

Thanks for your interest in my question on the Wrox bullentin board.

Here is some more information that might help. I have 3 tables where I am gathering non-related information and putting there unique key field from the record into a new table called Job_Detail. The Job_Detail table is made up with to look something like this below.
Job_Detail Table
Job (Unique) 10000AvisFarmers
Job Number 10000
Vendor Avis
Insurance Co Farmers

The relationship is already made between the Job_detail table and the other tables and the similar fields that are in both.