Can't change join type in subform record source

[EmilyT]

I'm having a problem getting a change to the join type in a subform record source to stick. I'm trying to change the join type from an inner join to a left join (I think, I'm new to this terminology). I can make the change when the subform is open in design view and save it, but when I open the main form it reverts to an inner join.



The subform is based on a table and a query. I want to use a left join so that all records from the table are shown and only those records that match from the query are shown. The query is never as up to date as the table, so when the join is reset to an inner join, some of the table entries disappear from the subform. I'm joining two fields, which are called SubjectID and VisitID and I've been trying to change both to a left join.

The main form is based on a differet table. The main form records are linked to the subform by the SubjectID field.

I must be creating some kind of problem somewhere since Access won't keep this change, but I don't understand what I have to do to fix it.

[June7]

Queries are dynamic and automatically update. The join type, as you have noted, can prevent related data from displaying. Why is the subform RecordSource a query joined to table?

Need to post queries and/or the db for analysis. Follow instructions at bottom of my post. Identify forms involved.

[pbaldy]

There was a bug in 2007 when it first came out that could cause changes to control/record source SQL to be dropped. If you haven't updated Access with the current service packs you might start with that. The workaround as I recall was to make sure you hit "Enter" in the properties window after changing the property to drop focus down to the next property.

[EmilyT]

I couldn't post the database unless it was completely empty of data. Would that still be helpful?

The design of this is probably very suspect - it has come about due to several user requests that I am trying to comply with though I think they are unnecessary. They want to see data from different tables in the same form. Some of the data is imported from another source (output from testing equipment) with 4 records per subject per visit. Other visit data is entered in a table (via the form/subform) with one record per subject per visit. The equipment output is imported in batches and so it is not as up to date as the visit data in the table. I used the query to rename the fields from the imported data so that they can be displayed on the subform for that visit. It works, but for those visits that have data entered in the visit table but do not have the equipment data imported yet, the join does not allow them to be displayed. (Sorry, that's probably really confusing...)

[June7]

Not really, need something to test with, even if it dummy info.

Is this form/subform used for data entry/edit?

[EmilyT]

Yes, the form/subform are for data entry.

[June7]

A form can add new record to only one table. Main form can add record to parent table then subform can add record to related child table.

I am thinking query is too complex.

We really need a better understanding of table relationships and the queries used as RecordSource. Fastest and easiest is usually direct examination of db.