• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

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

Peter Peterson

New member
Local time
Tomorrow, 00:09
Joined
Sep 14, 2020
Messages
15
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...
 

Attachments

  • Client Form.png
    Client Form.png
    175.5 KB · Views: 10
  • Client Form - Sub-Form.png
    Client Form - Sub-Form.png
    161.3 KB · Views: 10

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:09
Joined
Aug 30, 2003
Messages
34,610
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

New member
Local time
Tomorrow, 00:09
Joined
Sep 14, 2020
Messages
15
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
 

Attachments

  • DB Relationship.png
    DB Relationship.png
    222.6 KB · Views: 9

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:09
Joined
Aug 30, 2003
Messages
34,610
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

New member
Local time
Tomorrow, 00:09
Joined
Sep 14, 2020
Messages
15
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

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2002
Messages
29,746
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

Wino Moderator
Staff member
Local time
Today, 06:09
Joined
Aug 30, 2003
Messages
34,610
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

Super Moderator
Staff member
Local time
Today, 09:09
Joined
Feb 19, 2002
Messages
29,746
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

Wino Moderator
Staff member
Local time
Today, 06:09
Joined
Aug 30, 2003
Messages
34,610
Actually, I was just telling Peter where he would find the properties.
 

Peter Peterson

New member
Local time
Tomorrow, 00:09
Joined
Sep 14, 2020
Messages
15
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
 

Users who are viewing this thread

Top Bottom