Relationships (1 Viewer)

[stuart_adair]

Hello all,

Hope I can explain this well…

I’ve got 2 tables, one with customer details and one with job details. On the job form I want to be able to select an account number from a list box and then automatically display the appropriate customer details

Customer table has fields called account, name, address etc

Main_table has fields such as order details

I’ve created a relationship between the 2 account fields on both tables but am struggling from there…

Stu

 

[PNGBill]

Sounds like you should be using a Form with a SubForm.

Your Main Form is linked to One Table and the SubForm linked to the other with the Master Child fields being the Link, be it CustomerID, OderID - whatever.

Common use is to have MainForm with CustomerTable and SubForm with OrderTable but you can have it the other way around if your task is to view Orders and then see Customer Details for each order.

Your Forms can be from the Table or from a Query that only uses one table or pulls in data from more then one Table.
If the Later, you may then have either Command Buttons to jump to another Form to do Edit, Add or delete tasks or have another SubForm for this. reason being is that when you include more then one Table in your Query, you most likely are no longer able to Edit, Add or Delete Records.

If you are using Continuous Forms then this can not be the Main Form, it can be a subform but you can't have a subform from a Continuous Form, it must be a Single Form.

 

[G37Sam]

Hello all,

Hope I can explain this well…

I’ve got 2 tables, one with customer details and one with job details. On the job form I want to be able to select an account number from a list box and then automatically display the appropriate customer details

Customer table has fields called account, name, address etc

Main_table has fields such as order details

I’ve created a relationship between the 2 account fields on both tables but am struggling from there…

Stu

You're looking for the Dlookup feature.. look it up in help for details on it's syntax

 

[dkinley]

I disagree. I think the OP is suggesting that the user opens the job form and assigns customer to the job.

On the job form I want to be able to select an account number from a list box and then automatically display the appropriate customer details

As PNGBill stated, what many are used to is to go to the customer's form and add a new job from this form (while seeing all previous jobs in a subform).

I have applied the technique the OP is calling for because users claimed it saved clicks. In the first scenario 2 clicks (1 - customer form, 2 - new job form). In this scenario, it is still 2 clicks (1 - new job form, 2 - assign customer to job). The only way a user saves time is if they are bulk adding jobs.

Stu, in this instance, the simplest implementation would be to put a combo box on the jobs form. This combo box would provide the lookup to the customer table. It would store the primary key of the customer (CustomerID) in the foreign key of the jobs table.

Now, if you want to display more details about the Customer, you could have some unbound controls on the form and simply update those controls by the AfterUpdate event of the combo box (Me.txtUnboundControl1 = cboComboName.Colum(x)).

These would be static entries and unchangeable by the user but would provide them with more information about the customer other than the name (if the combo box is set up right). For example, a phone number or a city to differentiate between customers of the same name.

If the requirement was to edit customer information while they are on a jobs form, then the approach would be a bit different. However, since a change would essentially be universally applied to the entire data set (whereever a job had that customer) why not direct the user to make the changes on the customer form and save some headaches.

HTH,
-dK