Getting duplicate records in split form datasheet when using combo box with multiple selections

[nwatterson]

I hope someone can give me guidance as to how to correct the issue below. I created a split form and I have 2 combo boxes that allow multiple selections. The one combo box for LOB (line of business) works perfect and does not create duplicate records in the datasheet view of the split form. The 2nd combo box with multiple selections creates duplicate records in the datasheet depending on how many selections are made. I have checked this in the underlying table and there are no duplicate records, it is only in the split form datasheet. I have checked settings and configuration between the 2 combo boxes that are reacting differently and they appear to be identical and therefore have absolutely no idea why I am having this problem.



Any guidance would be greatly appreciated.

Thanks, Neil

[June7]

Need to review data structure, form design, and code. If you want to provide db for analysis, follow instructions at bottom of my post.

[nwatterson]

Thanks June, I know you will have a lot of recommendations as I am not even a good novice but besides this one issue I have the database does work. If you open FRM All projects, you will see in the datasheet rows 4,5 and 6 are duplicate project #'s. You will see 3 check boxes selected in the field CBT Applications. This appears to be what is causing the duplication only in the split form datasheet. the Tbl projects only has a single record as expected. Also note, the field LOB is setup the same way but multiple selections does not creat duplicate split form datasheet records.

Thanks again for your guidance, I am completely stumped and I know it will end up being something stupid on my part.

Neil

[June7]

The multiple records are because of the multi-value fields included in the All Projects query. I NEVER use multi-value fields.

The form RecordSource is a query with only one table - Tbl Projects. There is no filter or sort criteria in the query - could just reference the table. Or use an SQL statement as done in the other forms. But again, they all pull from only one table and none have filtering so could just reference tables.

So I changed the RecordSource to Tbl Projects and the duplication goes away. This SQL also works: SELECT * FROM [Tbl Projects] ORDER BY [Project #];


Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be tblProjects or tbl_Projects and ProjectNum or Project_Num.

[nwatterson]

Thanks June, I'll take a look and mark as closed once complete.

[nwatterson]

June, what field did you change the record source to Tbl projects?

[June7]

I didn't change field, I only changed the form RecordSource property.

[nwatterson]

Awesome, thanks June. I knew it would be something real simple.