Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 17th, 2004, 05:16 PM
Authorized User
 
Join Date: Mar 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating or Editing a record from a form

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?


 
Old October 17th, 2004, 10:05 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

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
 
Old October 18th, 2004, 10:59 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 18th, 2004, 11:42 PM
Authorized User
 
Join Date: Mar 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.







Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo boxes in record editing form srcLakeJake Access 0 January 23rd, 2008 11:14 PM
Passing URL parameter to form for editing markad08 Dreamweaver (all versions) 1 May 18th, 2006 03:38 PM
Editing SINGLE record in ASP.NET Koofmich ADO.NET 5 December 30th, 2004 05:42 AM
Trouble creating a Copy Record command on a Form Ron V Access 0 May 19th, 2004 01:36 PM
Editing MySQL data from a PHP form cmiller Beginning PHP 2 September 2nd, 2003 05:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.