Solved Form not reflecting (nor recognising) all the records in the table (1 Viewer)

[Peter Peterson]

Good morning,
I have searched the Forum for an answer including:
data-entry-form-w-subform-creates-extra-records.313520/ which I don't think is related to my problem.
I am using Office 365 Pro on latest version of Windows 10.
This simple Access database is to help me manage my clients (outgrown the suitability of MS Outlook Contacts.
I have two tables, "Clients" and "Meetings" with a relationship one (client) to many (meetings).
I have set "Enforce Referential Integrity", not "Cascade Update Related Fields", nor "Cascade Delete Related Records"
Join Type is set to "Include ALL records from 'Clients' and only those records from 'Meetings' where the join fields are equal"
The Client table includes a List of where the Client is in my process - noting if I have a 'Lead', I have no Meetings in the "Meetings" table.
Not sure if this were my issue is but my Forms are showing the records in my tables correctly.
I have a "Clients" form and a "Meetings" subForm which is on the fourth tab of the "Clients" form.
I have four (4) Client records in my Clients table
one of which has two meetings recorded;
two of which have one meeting recorded each; and
one of which has no meetings recorded.
My Clients Form record navigator shows there are "4 of 4" records, however, it is counting (and showing) the Client with two meetings twice and not counting (or showing) the Client with no meetings. When using the 'next' record selector, the Form displays my second Client details twice, yet on the meeting sub-form the first meeting date is displayed twice, noting the sub-form record selector notes there are two meeting records (correctly). Clicking on the 'next' record on the sub-form works.
I have attached form images if this helps.
I look forward to learning the errors of my ways...

 

[pbaldy]

Can you confirm the record source(s)? I would expect the main form to be bound only to the clients table, and a subform for meetings bound to the meetings table. Master/child links would keep them in sync with each other.

 

[Peter Peterson]

Thank you for replying pbaldy,
I am a novice at this and not sure where or how to find the info you are requesting.
The tables were created using Access, they are not from another database, if that is your question.
See attached, if this helps.
I vaguely understand your Master / Child concept, I can't remember how I created the forms, but thought I used the wizard?
I am unsure how to check if I have the forms set up as Master / Child, sorry.
Regards
Peter

 

[pbaldy]

What I'm after to start is the record source property of the Clients form. It should just be the clients table, not a query that pulls in the meetings table.

 

[Peter Peterson]

Good evening pbaldy,
Thanks for the guidance. Below is the SELECT statement in the Record Source of the Clients.

SELECT Clients.*, Meetings.MeetingDate, Meetings.MeetingNotes, Meetings.MeetingEmail FROM Clients INNER JOIN Meetings ON Clients.ClientID = Meetings.ClientID;

I have no idea how this was created???
Are you saying I should replace the above with "SELECT * FROM Clients", or just "Clients"
The subForm "Meetings" has a Record Source "Meetings" which I assume is my Meetings table.
Thanks again for the assistance.
I'll pick this up tomorrow after a nights sleep.
Regards
Peter

 

[Pat Hartman]

Paul's assessment was correct. You need to change the RecordSource for the main form to be a query that selects ONLY from Clients and the subform's RecordSource should be a query that selects ONLY from Meetings.

Opening the query you have currently as the main form RecordSource should show you the problem. It is using an inner join which is why it is showing only clients with meetings and since it is including the meetings table, you will see a row for every meeting, hence the "duplication" of client.

 

[pbaldy]

Are you saying I should replace the above with "SELECT * FROM Clients", or just "Clients"

Yes, and the subform control on the main form is where you should find the master/child links.

 

[Pat Hartman]

Actually, Access only "finds" the Master/Child link if you have created a relationship using the Relationships window. Otherwise, you need to specify it yourself.

 

[pbaldy]

Actually, I was just telling Peter where he would find the properties.

 

[Peter Peterson]

Good morning Pat and pbaldy,
I have replaced the Client Form Record Resource with "Clients" and my forms seem to be working as expected.
Thank you both.
Regards
Peter

 

[pbaldy]

Happy to help!