Solved Easiest way to search for unique items (1 Viewer)

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
Hi @MajP .
I need to know if a vinyl record is already in the database, I have some duplicates and they do not need to be in the database twice.
But I have not found a way to create a key that works for this so I thought of using your FAYT for it.
I changed the field for the name of the record name in the form (frm_record2) to a combo box and entered code according to what I thought was right, but I get error messages.
Maybe this is not the right way to solve my problem?

Leif
 

Attachments

  • Skivsamlingen - copy.zip
    901.7 KB · Views: 86

Minty

AWF VIP
Local time
Today, 06:40
Joined
Jul 26, 2013
Messages
10,355
There is a find duplicate query wizard - have you considered using that?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
If you do this query. It will show you all records entered more than once
SQL:
SELECT
 tbl_record.record_name,
 Count(tbl_record.record_ID) AS CountOfrecord_ID
FROM
 tbl_record
GROUP BY
 tbl_record.record_name
HAVING
 (((Count(tbl_record.record_ID))>1));
Lets call that qryDupes

That will show you
qryDupes qryDupes

record_nameCountOfrecord_ID
Bolero
2​
Boogie Nights
2​
Born To Be Alive
3​
Dance
2​
Fire
2​
I Love To Love
2​
Let The Sunshine In
2​
Let's Spend The Night Together
2​
Paloma Blanca
2​
Theme From Close Encounters
3​
Ti Amo
2​
Which may or not be dupes based on who the artist is. You should probably have a unique index so that an artist and record is unique. But I assume the same song sung by a different person is not a duplicate.
This is normally checked in the before update of the record name field. Do a dcount on that name and see if it already exists. Then you can pop up a message if the count is > 0. The Message should say. "The album Boogies Nights is already in the database assigned to Heatwave you are trying to assign to Cousin Rachel. Do you want to continue?"
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
You can use this query
Code:
SELECT tbl_record.record_name, tbl_record.record_artist_ID, tbl_artist.artist_name
FROM tbl_artist INNER JOIN tbl_record ON tbl_artist.artist_ID = tbl_record.record_artist_ID
WHERE (((tbl_record.record_name) In (Select Record_name from qryDupes)))
ORDER BY tbl_record.record_name, tbl_artist.artist_name;

To then do cleanup
Query2 Query2

record_namerecord_artist_IDArtist / Grupp
Bolero
886​
Thijs Van Leer
Bolero
1047​
Tomita
Boogie Nights
944​
Cousin Rachel
Boogie Nights
488​
Heatwave
Born To Be Alive
655​
Patrick Hernandez
Born To Be Alive
655​
Patrick Hernandez
Born To Be Alive
997​
Rick Fernando
Dance
838​
Nightforce
Dance
841​
Paradise Express
Fire
311​
Lizzy Mercier Descloux
Fire
6​
Ohio Players
I Love To Love
486​
Tina Charles
I Love To Love
486​
Tina Charles
Let The Sunshine In
542​
Taxi
Let The Sunshine In
542​
Taxi
Let's Spend The Night Together
784​
Edmundo Falé
Let's Spend The Night Together
856​
Roger Chapman
Paloma Blanca
15​
George Baker Selection
Paloma Blanca
15​
George Baker Selection
Theme From Close Encounters
962​
Gene Page
Theme From Close Encounters
104​
Meco
Theme From Close Encounters
963​
The Visitors
Ti Amo
964​
Howard Carpendale
Ti Amo
66​
Umberto Tozzi
So you do have some legit duplicates where artist and record are the same. If you make a composite index on recordname and artistid you can keep this from happening without any code.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
I relooked and even these are not duplicates, because they are different media. Some are 12 inch, LP, or 45. So what is a duplicate? Artist, Record Name, and Media. When I look at the input it would be really hard to not notice that especially if sorting by record name. I can see adding a duplicate song.
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
Thank you @MajP for the input in this subjekt.
You've found my real problem.
In the table, it is no problem to find duplicates, but I want it to appear in the form if I try to enter a name of a record that has been used before, for the same artist and with the same media.
I managed to create a composite primary key with these three fields in the table tbl_records, but when I was going to create the link to the middle table tbl_music_record it could not find a foreign key.
So I had to find another way to find out if I was trying to insert a duplicate.
I do not know why I could not connect the tables in the same way as they are now even though the primary key looks different.
The contents of the table are the same.

Leif
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
Should I try to make a composite index för these three fields?
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
I've tried that and it works.
This stops me from enter a duplicate.
It would be nice i I could get a messages that showed me the post that is already in the database.
Is that to much to ask for? :unsure: ;)

Leif
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
I do not like composite keys, I find them hard to handle. A unique composite index would be fine.
Or you can alert them in the before update
Code:
Public Function RecordExists(RecordName As String, RecordMedia As String, RecordArtistID As Long) As Boolean
  Dim Criteria As String
  RecordName = Replace(RecordName, "'", "''")
  Criteria = "Record_Name = '" & RecordName & "' AND Record_Media = '" & RecordMedia & "' AND Record_Artist_ID = " & RecordArtistID
  RecordExists = DCount("*", "tbl_Record", Criteria) > 0
End Function

Public Sub Test()
  If RecordExists("Let's Spend The Night Together", "45", 784) Then
    MsgBox "Your message here"
  End If
End Sub
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
Or you can alert them in the before update
What do you mean by this? What before update?
I don't understand the code you presented in your last message.

Leif
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
I think that I get it now.
You mean that i can put the code above int the event "Before update".

But what about the "Sub Test()" When I call the funktion RecordExist(... I only gets the result for the record "Let's spend the night...

So...... Sorry that I'm such a bad coder.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
The test was to show that the dlookup worked. Maybe something like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim rtn As Long
  If RecordExists(Me.record_name, Me.record_media, Me.record_artist_ID) Then
    MsgBox "You already have a record named " & record_name & " by this artitst in format " & Me.record_media & ". Do you want to continue?", vbInformation, " Duplicate."
    Cancel = True
  End If
End Sub

Now there is no error checking in there to ensure that they have entered all fields. So you would need to add to the code. So this is just a basic idea, but would need more code. You would need to cancel if the mandatory fields are not filled in.
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
The test was to show that the dlookup worked. Maybe something like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim rtn As Long
  If RecordExists(Me.record_name, Me.record_media, Me.record_artist_ID) Then
    MsgBox "You already have a record named " & record_name & " by this artitst in format " & Me.record_media & ". Do you want to continue?", vbInformation, " Duplicate."
    Cancel = True
  End If
End Sub

Now there is no error checking in there to ensure that they have entered all fields. So you would need to add to the code. So this is just a basic idea, but would need more code. You would need to cancel if the mandatory fields are not filled in.
Thanks (again) @MajP

I chose a slightly different path.
Instead of adding code to handle error messages here, I chose to add a check that the field is filled in in the "LostFocus" event.
Here's what happened to Record_name:
Code:
Private Sub record_name_LostFocus ()
    If IsNull (record_name) Then
        MsgBox "You must enter a disc name.", VbExclamation, "Disc name is missing!"
        Me.Combination Box25.SetFocus
        Me.record_name.SetFocus
    End If
End Sub

About the same for record_media and for Artist / group like this:
Code:
Private Sub Combination Box25_LostFocus ()
    If Me.Combination Box25.ListIndex = "-1" Then
        MsgBox "You must enter an artist / group name.", VbExclamation, "Artist / group name missing!"
        Me.record_name.SetFocus
        Me.Combination Box25.SetFocus
    End If
End Sub

I'm a little unsure if this was the right way to do it but I was unsure how to check several of the fields in the same code.
Have a nice weekend!

Leif
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:40
Joined
May 21, 2018
Messages
8,463
I'm a little unsure if this was the right way to do it but I was unsure how to check several of the fields in the same code.
Normally, you also validate required fields in the before update event of the form. This way you can cancel the update until all required fields are entered. I have been using this version lately as provided in this thread.

 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
Normally, you also validate required fields in the before update event of the form. This way you can cancel the update until all required fields are entered. I have been using this version lately as provided in this thread.

Oops, that was a lot of code.
I have been confident in the little snippets of code that you and others have written to me before.
But I will definitely test this, it's fun to code! :D

But I was hit by another error that I think has with the code you wrote to find any duplicates.
I tried to put in a single with a group called Secret Service and named Oh Susie.
I'm pretty sure the disk is not in the database before. Despite this, I only got an error message when I try to add a picture on the disc cover.
Skärmklipp.PNG

The explanation in the error message says that the record does not exist.
If, on the other hand, I look in the table for records (tbl_record), I see it.
Skärmklipp.PNG

There is only one entry in the table that has a similar appearance but the same artist and media.
Skärmklipp.PNG

I do not know what it is that makes this one interpreted as a duplicate?
I will send with my database if you want to take a closer look.

Leif
 

Attachments

  • Skivsamlingen.zip
    932.1 KB · Views: 78

Dreamweaver

Well-known member
Local time
Today, 06:40
Joined
Nov 28, 2005
Messages
2,466
I've updated the code and removed what Wasn't needed
Code:
Dim strPicture As String
    Dim ID As Long
    strPicture = PicturePicker
    If Len(strPicture) > 0 Then
        Me.artist_picture = strPicture
        RunCommand acCmdSaveRecord
    End If
I've only used the runcommand as you seemed to want to save the record after adding the picture link.

mick
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
I've updated the code and removed what Wasn't needed
Code:
Dim strPicture As String
    Dim ID As Long
    strPicture = PicturePicker
    If Len(strPicture) > 0 Then
        Me.artist_picture = strPicture
        RunCommand acCmdSaveRecord
    End If
I've only used the runcommand as you seemed to want to save the record after adding the picture link.

mick
Thanks Mick.
That worked with a slight change of code.
In the table, the field for the image is called record_picture not artist_picture.

Leif
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
BUT! I've still got the error message that "The record doesn't exist".
And now it's stopped at RunCommand acCmdSaveRecord.

So there is something other than this that's wrong. :cry:

Leif
 

lodmark

Member
Local time
Today, 07:40
Joined
Jul 24, 2020
Messages
232
BUT! I've still got the error message that "The record doesn't exist".
And now it's stopped at RunCommand acCmdSaveRecord.

So there is something other than this that's wrong. :cry:

Leif
I took away the line "RunCommand acCmdSaveRecord" and then it worked fine.
Wonder what's gonna happened next. 🤯
Leif
 

Users who are viewing this thread

Top Bottom