Sub Form Query

[cmb]

Hi there!

Please would someone help me populate a text field in a sub form from data in the parent form. The parent form is frmOrdersWithSites and is made from qryOrdersWithSites which uses two tables, tblOrders and tblSites. tblOrders holds the data for upcoming installations of new equipment and tblSites holds the data for the site name and address etc. The sub form is frmSubAppoints and is bound, it's used to add appointments associated with the installation orders. My query matches the site to the order and my sub form adds new the new appointments to tblAppoints and everything works perfectly. However, I need to add the site name to the appointments from tblSites.SiteName. I have added a text field to the sub form but have not managed to figure out how to populate it.

Would someone please be so kind and explain how I can do this.

Many thanks in advance!

CB

[June7]

Why do you need to repeat the site name in the subform? Isn't it displayed on the main form? Are you trying to save site name into tblAppoints? Why? This sounds like duplication of data. The appointment is associated with order? The order is associated with site?

[cmb]

Thanks for the reply. I intend to hide the text field in the sub form because I just need the site name in tblAppoints. This will be used to populate a monthly calendar with all appointments. Thanks for your help!

[June7]

Did you try retrieving the site name for each appointment in a query that joins the 3 tables? That's the way relational database is supposed to work.

[cmb]

This code works but it will only let me view and edit existing records. I can't create any new records/appointments. Is that because I the query is across 3 tables and is there a way around this? Thanks again for any help!

SELECT tblAppoints.AppointTypeID, tblAppoints.VenderID, tblAppoints.AppointStartDate, tblAppoints.AppointDuration, tblAppoints.AppointEndDate, tblAppoints.AppointStatus, tblSites.SiteName, tblOrders.OrderID
FROM (tblSites INNER JOIN tblOrders ON tblSites.SiteID = tblOrders.SiteID) INNER JOIN tblAppoints ON tblOrders.OrderID = tblAppoints.OrderID;

[June7]

INNER JOIN requires existing related records in all tables. Use LEFT or RIGHT JOIN (not sure which for this situation).

A form should enter/edit data for only 1 table. Including the other tables is just for reference info, don't allow edit of that data.