Form Information is Self-Duplicating

[Big D]

Greetings:

Has anyone experienced forms duplicating resulting in multiple records? Attached is a zipped copy of my database. The first button shown on the switchboard is titled "Enter Project Details." scrolling through the records on at time, you'll be able to see the records that were duplicated. What is odd is that not all records duplicate themselves. The duplicates can also be seen in the third button titled "Project List" (is a report) that also shows the duplicates.

If anyone has a solution to stop this duplication problem would be greatly appreciated.

Cheers,
Big D

 

[theDBguy]

Hi Big D. The reason you're seeing duplicates for some records is because you have multiple Tasks associated with those records. The duplicates are showing because you're using a query joining the two tables as the Record Source for your form. If you simply base the form to the Projects table, then you won't see the duplicates. If you want to see the Task Costs, consider using a subform to show it.

 

[isladogs]

Change the record source to unique values. The SQL will start with SELECT DISTINCT.

Also your Tasks table should have ID set as a primary key field.
If any other tables lack a primary key, fix them also

 

[Big D]

Hi Isladogs & theDBguy,

Thanks very much for following up with me so quickly. Isladog's proposed solution looks like it might be the easier one to implement. So I'll try that first and if that results in no joy, I'll try theDBguy's proposed approach.

Isladog, I do have a question. I was able to change the record source easy enough to unique values and the SQL now shows: SELECT DISTINCTROW Projects.*, Tasks.Cost FROM Projects LEFT JOIN Tasks ON Projects.ID = Tasks.Project; However, I'm somewhat stuck on the part where you say to set the Tasks Table's ID column to a primary key field. Is that an easy fix? Can you explain how it's done?

Thanks again folks!
Big D

 

[Big D]

Isladogs,

I just figured out how to set the ID to primary key in the task table. Any idea how I can get rid of the duplicate repords?

 

[theDBguy]

Isladogs,

I just figured out how to set the ID to primary key in the task table. Any idea how I can get rid of the duplicate repords?

Hi Big D. I told you how. When you get a chance, give it a try and let us know if it doesn't work. If you have any questions, let us know too.

 

[theDBguy]

Hi Big D. I told you how. When you get a chance, give it a try and let us know if it doesn't work. If you have any questions, let us know too.

Here, let me know what I broke. Cheers!

 

[isladogs]

I said to use Unique Values (SELECT DISTINCT) which will work.
However, you used Unique Records (SELECT DISTINCTROW) which is not the same thing and not appropriate in this case.
See attached for a modified version

 

[Pat Hartman]

Colin, I really think that getting rid of the child table resolves the issue as theDBGuy suggested. Your example still duplicates records based on the number of tasks per project.

 

[isladogs]

I have no objection to the use of a subform for this.

However I checked before I posted the first time and again just now.
I see no duplicates using SELECT DISTINCT.
44 records, all with unique Project ID.
Is there something I'm missing here?

EDIT
Just downloaded DBGs example. The Project List form seems identical to the original from the OP with 63 records including duplicates

 

[Pat Hartman]

Colin,
When I open that form, it shows 80 records but there are only 57 projects so we would never expect to see more than 57 records returned. The first three records are for the same project.

The query does not use Distinct and if it did, the form would not be updateable. Even using DISTINCT (assuming the form didn't need to be updateable), you would still get duplication if the costs were different on the tasks records.

 

[isladogs]

OK - it appears we're referring to different forms!:banghead:

In my v2, I was referring to the form ProjectList (third item on the switchboard)
The original version had 63 records. Mine has 44

I didn't do anything to the first item ProjectDetails.
However I've now also changed that to DISTINCT in v3
The original version had 80 records as you stated.
Mine has 57 with no duplicate ID values

Of course, the forms are now read only due to using DISTINCT. I should have mentioned that important point

DBGs version also has 57 records in the ProjectDetails form using the single table (and of course these are editable)
... but he didn't alter the ProjectList form
Hopefully now clear!

 

[theDBguy]

DBGs version also has 57 records in the ProjectDetails form using the single table (and of course these are edirtable)
... but he didn't alter the ProjectList form
Hopefully now clear!

Sorry, yes, I stopped after the first issue hoping the OP can do the rest. Cheers!

 

[Pat Hartman]

many inexperienced people make the mistake of including child tables in their main form query. I believe that is what happened here.

 

[Big D]

Good Morning Isladogs / theDBguy / Pat Hartman,

I'd like to thank you all for all the help and support you have provided me to help me get through this problem. I can't thank you folks enough for all that you do!

Based on the latest information it sounds like I should be putting Revision 3 of the database through some testing to see if the fixes stuck. I do have a question, however. What are the ramifications, if any, of the forms in Revision 3 now being read only due to using DISTINCT. I don't understand what that actually means. Can you explain this to me? Thank you again for all your help!!

Cheers,
Big D

 

[theDBguy]

Good Morning Isladogs / theDBguy / Pat Hartman,

I'd like to thank you all for all the help and support you have provided me to help me get through this problem. I can't thank you folks enough for all that you do!

Based on the latest information it sounds like I should be putting Revision 3 of the database through some testing to see if the fixes stuck. I do have a question, however. What are the ramifications, if any, of the forms in Revision 3 now being read only due to using DISTINCT. I don't understand what that actually means. Can you explain this to me? Thank you again for all your help!!

Cheers,
Big D

Hi Big D. If you want the forms to be editable, then maybe you should test my version of rev1 but also fix the second form that I didn't fix.

 

[isladogs]

BigD
Just so we're all singing the same song...
If you want your forms to be editable, my solution is no use to you.
To be honest, I think I may have just confused you in my exchange with Pat …
Instead use DBG's method for both forms

However, if you did want a form to be read only (not editable), you could use my approach or just lock the form using DBG's better method

 

[Big D]

Hi Isladogs,
Yeah, I guess I'm somewhat confused because I just opened Revision 3 and if I place the cursor in a field and left click the mouse, a menu opens allowing me to edit the form, please see attached screenshot. Thanks very much for your help.

Cheers,
Bruce

 

[isladogs]

Dropdowns do appear and will allow you to filter the form.
However, its not editable.
Have you tried actually changing the values in any of the form controls?

 

[Big D]

Hi Isladogs:

Now I understand what you mean by form not being editable. I just tried to change info in a manually entered field and I can't. Unfortunately, Revision 3 won't work for me as the form fields need to be editable.

Cheers,
Big D