MS-Access: What could cause one form with a join query to load right and another not?
- by Daniel Straight
Form1
Form1 is bound to Table1. Table1 has an ID field.
Form2
Form2 is bound to Table2 joined to Table1 on Table2.Table1_ID=Table1.ID
Here is the SQL (generated by Access):
SELECT
Table2.*,
Table1.[FirstFieldINeed],
Table1.[SecondFieldINeed],
Table1.[ThirdFieldINeed]
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.[Table1_ID];
Form2 is opened with this code in Form1:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Form2", , , , acFormAdd, , Me.[ID]
DoCmd.Close acForm, "Form1", acSaveYes
And when loaded runs:
Me.[Table1_ID] = Me.OpenArgs
When Form2 is loaded, fields bound to columns from Table1 show up correctly.
Form3
Form3 is bound to Table3 joined to Table2 on Table3.Table2_ID=Table2.ID
Here is the SQL (generated by Access):
SELECT
Table3.*,
Table2.[FirstFieldINeed],
Table2.[SecondFieldINeed]
FROM Table2 INNER JOIN Table3 ON Table2.ID = Table3.[Table2_ID];
Form3 is opened with this code in Form2:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Form3", , , , acFormAdd, , Me.[ID]
DoCmd.Close acForm, "Form2", acSaveYes
And when loaded runs:
Me.[Table2_ID] = Me.OpenArgs
When Form3 is loaded, fields bound to columns from Table2 do not show up correctly.
WHY?
UPDATES
I tried making the join query into a separate query and using that as my record source, but it made no difference at all.
If I go to the query for Form3 and view it in datasheet view, I can see that the information that should be pulled into the form is there. It just isn't showing up on the form.
