obtain data from another table

[ebbo]

Hello, Im not sure if this should go in forms or queries. but i guess its a bit of both. I have two tables:

TABLE: COMPANIES
ID
Account_no
Company
1Address Type
1Address1
1Address2
1Address3
1Town
1County
1Postcode
Company_Notes

TABLE: CANCELLATIONS
Cancellation_ID
DX_NO
Company_Name
Cancellation_Date
Product
Diarised
Date_Diarised
Cancellation_Complete
Date_Complete

COMPANIES.Account_no = CANCELLATIONS.DX_NO

I have created a cancellations form (tabular). In this form i have a few fields. The two main fields of concern are. DX_NO and Company name. What I want to do is when I enter a DX_NO I would like the Company_name field to update automatically based on the DX_NO. But i am not sure how to do this. I need to somehow tell the Company_name field to:

Select company from Companies WHERE companies.account_no = cancellations.dx_no

 

[Galaxiom]

You should not have the company name field in the Cancellations table. To do so is a breach of normalisation.

In the record source query for your cancellations form, join the two tables and include Companies.Company as a bound control on the form. Set this control to Locked so it cannot be changed.

I would also drop the Cancellation_Complete field which I presume is a Y/N format. The presence of a date in Date_Complete field would indicate the same information. Similarly the Diarised field I expect.

You should probably also have a Product table which stores the name and ID of the product. Use the product ID in you Cancellations table.

It does seem rather odd that you are dealing with cancellations in this way at all. Should they not be integrated into the database that manages the original bookings?

 

[ebbo]

You should not have the company name field in the Cancellations table. To do so is a breach of normalisation.

Understood

In the record source query for your cancellations form, join the two tables and include Companies.Company as a bound control on the form. Set this control to Locked so it cannot be changed.

Can you explain this to me. So far I have opened the form in design view. Went to view > properties and in record source I see cancellations.
Edit: I think I have this working, I made a query with all the fields i wanted, then dragged the fields from the query onto the form. Now when I enter a code and switch to the company name field it gets updated! cheers

I would also drop the Cancellation_Complete field which I presume is a Y/N format. The presence of a date in Date_Complete field would indicate the same information. Similarly the Diarised field I expect.

Good Idea

You should probably also have a Product table which stores the name and ID of the product. Use the product ID in you Cancellations table.

This I have actually managed to do

It does seem rather odd that you are dealing with cancellations in this way at all. Should they not be integrated into the database that manages the original bookings?

I should probably say that the original database is not designed by me, I have been asked to integrate a cancellations form into the database by my boss. I have actually no idea of how to use access so I am experimenting at the moment once I learnt a bit I might expand a few areas. So right now I need all the help I can get!!

Hope you can hlp me with point 2!