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

[Dreamweaver]

I'll have a look at your copy as it works fine on my system.

You don't really need to save the record anyway as access will save it when you move to a another record the only time you need save a record is if you are using that record for another form Etc.

 

[Dreamweaver]

I would also recommend opening options
Current Database>Document Window Options and change it from Tabbed to overlapping.

You wont need to have all your forms popup.

 

[lodmark]

Hi @MajP
I need a little quickfix.
I discovered that I need to have another field among those that makes a record unique. Together with the name of the album (record_name), the artist (artist_name_ID), media (record_media), the link to the album cover also needs to be unique. I have several records with the same songs but different covers.
I added that field to your code so that it was as below, but it did not work.

Public Function RecordExists(RecordName As String, RecordMedia As String, RecordArtistID As Long, RecordPicture As String) As Boolean
  Dim Criteria As String
  RecordName = Replace(RecordName, "'", "''")
  Criteria = "Record_Name = '" & RecordName & "' AND Record_Media = '" & RecordMedia & "' AND Record_Picture = '" & RecordPicture & "' AND Record_Artist_ID = " & RecordArtistID
  RecordExists = DCount("*", "tbl_Record", Criteria) > 0
End Function

And this is how the code where you call the function

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Dim rtn As Long
  If RecordExists(Me.record_name, Me.record_media, Me.record_artist_ID, Me.record_picture) Then
    MsgBox "Det finns redan en skiva med namnet " & record_name & " av denna artist / grupp och i mediaformatet " & Me.record_media & "och med samma skivomslag." & vbCrLf & "Tryck på avbryt och ändra i texten.", vbInformation + vbOKCancel, " Dubblett."
    Cancel = True
  End If
End Sub

Do you want to look at this and see if you can find what I did wrong?
Thanks!

Leif

 

[lodmark]

I think I solved it myself.
I noticed that the order of the fields in the function was different than they were when used in the sub so I changed it and then it seems to work.
I did not know that it mattered so much.
Could it be that simple?

Leif

 

[lodmark]

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.

Solved - Form Validation using .Tag with highlighted controls on close

Hello, I am trying to validate controls on y form that if their .tag is "Required" and they are null or =0 then highlight the border red and if there is data stay the same. I found a piece of code years ago and am trying to implement it but cant figure out what to change for the below revision...

www.access-programmers.co.uk

Hi @MajP
Today I tried to add the code you suggested. I didn't change it a bit and I got this error message at first and every time I switch to another record.

And it comes again when I click OK.
What am I missing here?

Leif

 

[MajP]

I noticed that the order of the fields in the function was different than they were when used in the sub so I changed it and then it seems to work.
I did not know that it mattered so much.
Could it be that simple?

Not exactly sure what you did, but lets assume you had a function or sub.

Public SomeSub1 (artistID as long, recordID as long)
' code
end sub

If you call it by

SomeSub1 me.artistID, me.recordID

you are passing the values to the correct arguments.
If you make SomeSub2 but changed the order of the arguments

Public SomeSub2 (recordID as long, artistID as long)
' code
end sub

but forget to change the order you pass in arguments
SomeSub2 me.artistID, me.recordID

Now you made a logic mistake. You are passing in an artistID to the recordID argument, and the recordID to the artistID. The datatypes match so this runs, but makes no sense.

 

[MajP]

Hi @MajP
Today I tried to add the code you suggested. I didn't change it a bit and I got this error message at first and every time I switch to another record.
View attachment 86489

And it comes again when I click OK.
What am I missing here?

Leif

I would have to see your new DB, but the error message hints that you run the event procedure but the procedure called in the event procedure is not named the same as what you are calling.

Private sub SomeEvent()
call SomeProcedure()
end sub

Private sub Some_proc()
'it was called some_proc not SomeProcedure
end sub

 

[lodmark]

I would have to see your new DB, but the error message hints that you run the event procedure but the procedure called in the event procedure is not named the same as what you are calling.

Private sub SomeEvent()
call SomeProcedure()
end sub

Private sub Some_proc()
'it was called some_proc not SomeProcedure
end sub

Thanks @MajP for your input.
I've tried to fix this line by line with help from debug and internet.
Now I'm stuck in this line of code.

The error message says:

Which I interpret as saying I have the wrong property somewhere.
Probably in Image because it was not in the original code.

Of course, I gratefully accept your help.
Still think I deserve to be recognized for my work so far.

Leif

 

[lodmark]

Not exactly sure what you did, but lets assume you had a function or sub.

Public SomeSub1 (artistID as long, recordID as long)
' code
end sub

If you call it by

SomeSub1 me.artistID, me.recordID

you are passing the values to the correct arguments.
If you make SomeSub2 but changed the order of the arguments

Public SomeSub2 (recordID as long, artistID as long)
' code
end sub

but forget to change the order you pass in arguments
SomeSub2 me.artistID, me.recordID

Now you made a logic mistake. You are passing in an artistID to the recordID argument, and the recordID to the artistID. The datatypes match so this runs, but makes no sense.

Thanks!
I fixed this and figured it out as you say above.

Leif

 

[lodmark]

Thanks @MajP for your input.
I've tried to fix this line by line with help from debug and internet.
Now I'm stuck in this line of code.
View attachment 86518
The error message says:
View attachment 86519
Which I interpret as saying I have the wrong property somewhere.
Probably in Image because it was not in the original code.

Of course, I gratefully accept your help.
Still think I deserve to be recognized for my work so far.

Leif

I've got more problems.

After I've added the picture in "criteria" in the function that you created for me I can add a new record but I can only add it with these four fields. And if I try to add something later, like a genre, it says that the record exist's.
Again I've messed up your code. Not so good. Sorry!
Leif

 

[lodmark]

Thanks!
I fixed this and figured it out as you say above.

Leif

Actually I didn't....

 

[lodmark]

I've got more problems.
View attachment 86536
View attachment 86537
After I've added the picture in "criteria" in the function that you created for me I can add a new record but I can only add it with these four fields. And if I try to add something later, like a genre, it says that the record exist's.
Again I've messed up your code. Not so good. Sorry!
Leif

I've commented out the line with "Cancel = True" in the subroutine that calls for the function.
After that it seemed to work.
The problem was that I couldn't add data to a record without getting a message that the record already exist.

Leif