New Records do not Appear on Edit Record Form

[jmitchelldueck]

Hi,

I have a table in my db called ContractsT, which has a query linked to it called ContractNameSearchQ to search for contracts by a person's name. I have based a continuous form on the query to edit the contracts (EditContractsF).

My issue is that upon entering a new record in to the table, it does not always appear on the query (and thus the form). It seems to rely on entries in two specific fields to consider it for the query.

Here is the SQL code for the query:

SELECT PhysicianT.fullName, PhysicianT.physID, ContractsT.contractID, ContractsT.positions, ContractsT.contractStart, ContractsT.contractEnd, REFTypesT.contractType, ContractsT.notes, ContractsT.renewalCompleted, ContractsT.type, ContractsT.extraPos, ContractsT.posEnd1, ContractsT.posEnd2, ContractsT.posEnd3FROM REFTypesT INNER JOIN (PhysicianT INNER JOIN ContractsT ON PhysicianT.physID = ContractsT.fullName) ON REFTypesT.contractTypeID = ContractsT.type
WHERE (((PhysicianT.physID) Like [forms]![EditContractsF]![nameSelector] & "*"))
ORDER BY PhysicianT.fullName;

Thanks

[pbaldy]

Your joins require entries in the related tables; try editing the joins and choosing the appropriate option.

[June7]

Normally a form can do data entry/edit for only 1 table. Which table is this form intended for data entry/edit? Why are you including additional tables?

As Paul notes. INNER JOIN requires related records in both sides for data to show.

[jmitchelldueck]

Will probably revisit this issue but I found a bypass, I'm going to make the two fields that need to have an entry required in the initial entry form so they always have a value.

I have two tables on the form because the one field, type, is a combo box that references another table for its values.

[June7]

Just because a combobox draws items from a table is not enough reason to include that table in RecordSource. However, if you do include then do not use INNER JOIN. Textboxes bound to the lookup table fields must not be editable.