Duplicate data results

[cdscivic]

I have a form linked to SQL Server table, it has a search box that applies a filter when you click search or hit enter. The data is correct on the server table. Simple in theory , but when the results are returned they aren't correct! Sql has the below but the search returns 3 lines but all are comments "testing". Driving me nuts, what am I doing wrong? Note it's a simple split form.



Id | name | Comments
1234 | Smith | testing
1234 | Smith | testing2
1234 | Smith | testing3

[orange]

Show the sql of the form's recordsource and any filter you have.

[cdscivic]

The record source is just the table name and the only filter that is applied is what's typed in the text box, it searches f name, l name and ID

[cdscivic]

Lol Ike I just chose it from a drop down

[ItsMe]

I have no idea what this thread is about. Perhaps "split form" has something to do with the symptom.

[cdscivic]

I have no idea what this thread is about. Perhaps "split form" has something to do with the symptom.

What specifically is confusing?

[ItsMe]

You are seemingly getting a Cartesian effect from a table after applying a keyword to a form's Filter property. That does not add up. So, there is not a single thing that makes perfect sense. The one exception may be the Split Form thing. When you put everything together, it makes less sense.

[cdscivic]

I see. Here is the filter that is applied via "Apply Filter" macro on click event:
[ID] Like "*" & [Forms]![frmLookup]![txtSearch] & "*" OR [Last Name] Like "*" & [Forms]![frmLookup]![txtSearch] & "*" OR [First Name] Like "*" & [Forms]![frmLookup]![txtSearch] & "*"

After that filter it applies a sort order by 4 fields using the same macro and "Set Order By"

[ItsMe]

Apparently there are three records with "testing". Maybe you can display the Primary Key during runtime and determine which records are actually being displayed.

[cdscivic]

Interesting update, the linked Sql Server table has the duplicate records in it, however the actual SQL table does not have dup records. What?!

[ItsMe]

Hmmmm. Maybe you can collect the connection property information from design view of your table and also the table name and rebuild it. Delete the table and use something like the following to add it back. If it persists, you may need to export dependent Objects for safe keeping, delete the dependent objects, then do a compact and repair. In other words, do a C and R after deleting the objects.
https://www.accessforums.net/showthr...522#post298522

[cdscivic]

I used a File DSN to connect using trusted windows authentication ont he table. Interestingly, I have compacted and repaired this DB today. I did not notice this problem prior to that, not saying it did not exist though.

[ItsMe]

The compact and repair may have introduced an issue. So, rebuilding the table may be as easy as using the Wizard for you. However, there may be issues with a dependent query object or something. So deleting the culprit object and purging the system tables via a C and R may be the only solution. Of course, this is only a guess. I seem to remember a similar issue being reported. However, I am not remembering the cause.

[cdscivic]

sooooo, semi rookie mistake. I fancy myself an average user of access and average on sql server. When I created the SQL table, I did not introduce or create auto numbered PK. Earlier today when I linked to the SQL table via ODBC in MS Access, I was asked to choose a unique identifier on the last step, so I chose the ID/"case id" mentioned above in my post. I see now it was a bit misleading in my post, I was trying to not divulge company info and in the process failed in my attempt to seek help...sigh.

That is where the issue was, I went back and dropped and recreated the sql table and added an auto numbering PK....then deleted the linked table, readded the new and improved version with PK and viola! It did not ask me for a unique ID upon running the wizard! After testing, it appears that the search is working as intended now. I guess the case ID being the ID messed with the results, ill try and wrap my brain around the logic of that specifically later on.

I wanted to make sure to post this in case someone else has a bonehead moment like me. Moral is, add a PK when linking to a table in sql.

Cheers!

[ItsMe]

Ok, thanks for posting and glad you are moving forward. I am unfamiliar with the Wizard and I am not sure I understand the adding PK thing. But, I cannot imagine adding one will hurt. I am just curious why Access has that option.