MS-Access: What could cause one form with a join query to load right and another not?

Question

Late breaking news!

If I manually create the new record with SQL and then open the form in edit mode using the code below, it works. I would still like to know what the problem was before, but at least I can get on with my application now.

---

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.

---

Another round of updates

Let me provide an analogous situation so what I'm trying to do is clear.

Table1 is now Houses. Table2 is now Drivers. Table3 is now Cars.

Pretend I am writing an insurance application. The relation of these tables will exactly match what I'm trying to do.

Each house can have one or more driver, and each driver has exactly one car. Right now, I'm just trying to get it working with one house, one driver, and one car. The user should first see a form called HouseInformation which will ask them to fill in information about their house. Then they will see DriverInformation which will ask them to fill in information about themselves. Then they will see CarInformation which will ask them to fill in information about their car. After they are done, there will be one record in each table. The record in Drivers will contain HouseId, matching the corresponding house, and the record in Cars will contain DriverId, matching the corresponding driver. I don't especially care how the forms are linked, just that they are and that it works reliably.

For what it's worth, I've also tried numerous other methods of loading the form and setting it's foreign key id. None of them have caused the form to pull in the data from the previous form's table, even though this exact pattern does work between the first two forms.

---

More updates

Subforms are not acceptable to the client, who specifically requested separate forms.

I find this extraordinarily confusing, but after Form3 loads, if I close it, go into design view, open the query for Form3 and switch to datasheet view, the correct data is there, including the fields I want from Form2. It just won't show up on the form. I requeried. It makes no difference. It won't show up.

Answer 1

This is usually done with subforms. There are two approaches to this:

1. nested subforms, which limits some of your display options because you have two levels.

2. cascading subforms.

Households would be your parent form.

Your Cars table would have have a HouseholdID foreign key.

Your Drivers table would have a CarID foreign key.

For both solutions, you'd have a Cars subform with the relevant fields describing the car (make, license plate, color, model year, etc.), linked on HouseholdID.

You'd also have a drivers subform that for the first solution you would embed in the Cars subform, linked on CarID. This forces the cars form to be a single form or, more usefully, a datasheet form. You can make the drivers form either continuous or datasheet, because it's at the bottom of the nested forms.

The result is a subform that displays as a datasheet and has a subdatasheet. When you hit the asterisk button on the Car datasheet, you get a new record and type in the car data. The HouseholdID is automatically entered (this is how subforms work). Likewise, for the drivers, you hit the asterisk in the drivers subform and enter the driver.

If you don't want to be limited to datasheets, you can put both subforms on the top-level Households form and link the drivers form on the CarID from the subform thus:

LinkChildField: CarID LinkParentField: Cars.Form!CarID

This does cause a few UI issues, as you can't enter a driver until a car has been entered, and you can end up with issues. That is not the case for solution 1, which because it uses datasheets doesn't cause any UI issues.

One thing that's a problem with this solution is that if you want to attach a single driver to more than one car in a household, you have to enter the driver twice. The solution to that is to create a join table to connect multiple cars to multiple drivers. That table would have a CarID and DriverID in it, and your low-level subform in the setup described above would tblCarDriver as its recordsource, and a combo box as the only control on it that drew its data from the Drivers table. The combo box would be bound to the DriverID of the join table.

Now, that allows you to model two drivers in one household driving two cars without having to entere each driver twice, but in that setup, there is no place to enter a new driver. So, the usual method is to set a NotInList event for the combo box and when you type "Fenton, David W." into the combo box and it's not there, it asks you if you want to enter this as a new driver, and then the code in the NotInList event takes care of it for you (either taking what you type and inserting the record, or opening a popup form prepopulated with what you typed that allows you to put in other information, like phone number and email and so forth).

Here's a screenshot of my ugly mockup of the first solution with many-to-many join between cars and drivers:

Answer 2

Might be a bit of a shot in the dark but I notice your queries are all inner joins, are you sure you have data on both sides of the relationships in the tables? Does an outer join make a difference?

Answer 3

I was able to recreate your structure and able to get it to work properly (meaning Table2 values show on Form 3 with Table 3 data) in Access 2007.

First I created three Tables (I'll use pseudo-SQL syntax for this)

Schema

Create Table Table1 ( Table1_ID AutoNumber, Col1 Text )
Create Table Table2 ( Table2_ID AutoNumber, Table1_ID Number, Col1 Text )
Create Table Table3 ( Table3_ID AutoNumber, Table2_ID Number, Col1 Text )

I created a relation from Table1 to Table2 with Enforce RI on.
I created a relation from Table2 to Table3 with Enforce RI on.

Insert Into Table1(Col1) Values("Val1")
Insert Into Table1(Col1) Values("Val2")
Insert Into Table1(Col1) Values("Val3")

Insert Into Table2(Table1_ID, Col1) Values(1, "T2Val1")
Insert Into Table2(Table1_ID, Col1) Values(2, "T2Val2")
Insert Into Table2(Table1_ID, Col1) Values(3, "T2Val3")

Insert Into Table3(Table1_ID, Col1) Values(1, "T3Val1")
Insert Into Table3(Table1_ID, Col1) Values(2, "T3Val2")
Insert Into Table3(Table1_ID, Col1) Values(3, "T3Val3")

Form1 (used the Wizard)

Form.Control Source = Table1
TextBox control named Table1_ID with a Control Source=Table1_ID
TextBox control named Col1 with a Control Source=Col1
Command Button with Click event set to:
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "Form2", , , , acFormAdd, , Me.[Table1_ID]
    DoCmd.Close acForm, "Form1", acSaveYes

Form2

Form.Control Source = SELECT Table2.*, Table1.Col1 FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID=Table2.Table1_ID; 
Form_Load event set to
    Me.[Table1_ID] = Me.OpenArgs
TextBox control named Table2_ID with a Control Source=Table2_ID
TextBox control named Table2.Col1 with a Control Source=Table2.Col1
TextBox control named Col1 with a Control Source=Table1.Col1
Command Button with Click event set to:
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "Form3", , , , acFormAdd, , Me.[Table2_ID]
    DoCmd.Close acForm, "Form2", acSaveYes

Form3

Form.Control Source = SELECT Table3.*, Table2.Col1 FROM Table2 INNER JOIN Table3 ON Table2.Table2_ID=Table3.Table2_ID; 
Form_Load event set to
    Me.[Table2_ID] = Me.OpenArgs
TextBox control named Table3_ID with a Control Source=Table3_ID
TextBox control named Table2_ID with a Control Source=Table2_ID
TextBox control named Col1 with a Control Source=Table2.Col1

Steps to reproduce (note that the specific IDs might be different):

1. Open Form1 
    a. Table1_ID: 1
    b. Col1: Val
2. Click the Button.
3. Form2 Opens in add mode.
    a. Table2_ID shows next ID (probably 4)
    b. Table2.Col1: <blank>
    c. Table1.Col1: Val1
4. Enter "FOO" in the TextBox for Table2.Col1 and Click the button
5. Form3 Opens in add mode
    a. Table3_ID: next id (probably 4)
    b. Table2_ID: the id shown in Step 3a.
    c. Table2.Col1: FOO

The key to making this work is step 4: entering a value for Col1. If on Form2, you simply click the button to go to Form3, you are creating a blank Table2 record and associating with a new Table3 record which is why nothing will show on Form3.

If I'm missing something in these steps to reproduce, then I'd ask that you be as explicit as I have been here in order for us to help you.

Now, all that said, I would not have approached this solution in this manner. First, I would ensure that I prefixed every single control name (e.g. lbl, txt etc.). I have found that Access can sometimes get confused between referencing a Control with the same name as Field. Second, binding an editable form to a query with a join is a recipe for pain IMO. I would instead bind directly to the table in question and use a subform or just query in VBA code from the Load event for the needed data from the parent table and bind it manually.