Using a query to supply an Access form

Question

I have a Microsoft Access form that is being supplied (somehow) by a query. The query contains three tables linked together via their respective primary and foreign keys, and the form displays data quite happily.

The strange thing (as far as I'm concerned) is the ability of the form to then allow data entry using the query.

However, if the user creates a new record, the whole thing seems to have problems due to a required field in the [table2] table.

SELECT [table1].*
    ,[table2].JobNo
    ,[table2].PlannedDateOC
    ,[table3].DateJobStarted
    ,[table1].PlanNo
FROM (
    [table1] LEFT JOIN [table2] ON [table1].PlanNo = [table2].PlanNo
    )
LEFT JOIN [table3] ON [table2].JobNo = [table3].JobNo
ORDER BY [table2].PlannedDateOC
    ,[table1].PlanNo;

According to the users, this form worked perfectly prior to conversion from Access 97/2003 format to Access 2010 (2007 file format).

Could anyone clarify whether this (the fact that it should work) is legitimate, and if this process would work in either version of Access, if so? The concept of being able to use a query for data entry is quite alien to me.

Let me know if you need further clarification.

NOTE: One thing of note, here, is that I did move some of the fields into the form header so that they were always visible as the rest of the form scrolls. I don't know if this will have any side effects on the performance of the form.

Answer 1

Above query will allow you to insert data into [table1] when all not null fields have their values and make sure only table1.fields are getting dirty when new record is inserted.