one-to-one connections

[MoggieCat]

I have created a linked form from a table, but the number of records in the
form is less than the number of records in the table. They are linked
one-to-one. How can I see all records in the form view? All sub-tables
(in the tabs) in the tabs have the correct number of records.

 

[Allen Browne]

If you are using Access 2007, open the form in design view, and set its
FilterOnLoad property to No. (This property does not exist in previous
versions.)

Double-check the RecordSource property of the form. It might be a query or
SQL statement with a WHERE clause.

If neither of those things solve it, and this is a local JET table (not a
table linked to another database), you could try a compact/repair in case
there's a bad index. More on that possibility:
http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries

 

[MoggieCat]

Thank you but the information unfortunately did not solve the problem.
Resetting FilterOnLoad did not help; the recordSource starts with "select".
When I tried to copy and paste, it did not work as the program told me that
one of the fields included related records. As all records are related and
have the same ID key, this did not help. Any other suggestions, other than
starting again from scratch? I have managed to incorporate tabs, so do not
really want to erase all the work.

 

[Allen Browne]

Okay, the RecordSource is a SELECT query statement.

Click the Build button (...) beside the RecordSource property.
Access will open the query builder, and show you design view.

If this query uses more than one table, the default JOIN will be giving you
only records that exist in the other table also. That's why there's fewer
than in the one table alone.

You could solve the problem several ways:
a) by using one table only in the SELECT statement,
b) by using an outer join, or
c) by ensuring the 2nd table has records for all the ones in the first
table.

(a) is the simplest solution. For (b), see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

(c) is the least desirable choice (as the problem may reoccur.)