Duplicate records using VBA

[Khalil Handal]

Hi All,

I have this query showing duplicate records:

SELECT tblBorrowedBooks.pkBorowerId, tblBorrowedBooks.DateBorrowed, tblBorrowedBooks.fkBookId, tblBorrowedBooks.fkMemberId
FROM tblBorrowedBooks
WHERE (((tblBorrowedBooks.DateBorrowed) In (SELECT [DateBorrowed] FROM [tblBorrowedBooks] As Tmp GROUP BY [DateBorrowed],[fkBookId],[fkMemberId] HAVING Count(*)>1 And [fkBookId] = [tblBorrowedBooks].[fkBookId] And [fkMemberId] = [tblBorrowedBooks].[fkMemberId])))
ORDER BY tblBorrowedBooks.DateBorrowed, tblBorrowedBooks.fkBookId, tblBorrowedBooks.fkMemberId;


How is it possible to change it to VBA code so when the user press the command button it will first check for duplicate value before adding the values to the table. Note the form is an unbounded form with INSERT INTO statement to append data. So, a message box should appear telling the user that it is a duplicate.

Khalil

[orange]

Please tell us exactly which fields identify your "duplicates".

When designing a table, you can create a composite unique index on a number of fields, then the DBMS itself will report records in which that combination of fields would result in a duplicate.

Good luck.

[Khalil Handal]

Hi,
Thanks for the reply.

The table has 5 fields only: (pkBorrowerId, DateBorrowed, fkBookId, fkMemberId, DateReturned). The DateReturned field is a calculated field having its value from DateBorrowed + 21 days and the value is calculated from the form and the saved to the table.

Running the query for example shows the following:


1- All these fields (DateBorrowed, fkBookId, fkMemberId) identify the duplicates except the primary key (pkBorrowerId)
2- I tried the following Dcount to find the duplicates but it is not working.

If DCount("*", "tblBorrowedBooks", "DateBorrowed='" & Me.txtDateBorrowed & "' AND fkBookId = '" _ & Me.txtfkBookId & "' AND fkMemberId = '" & Me.cboMember & "'") > 0 Then
MsgBox "A book matching these details already exist!", vbExclamation

Meanwhile, I will see your option of using composite unique index on the table's fields.

Khalil

[pbaldy]

This may help with the delimiters:

http://www.theaccessweb.com/general/gen0018.htm

It would seem like your test is incomplete. If you borrowed a book yesterday, I can't borrow it today, because you'll still have it. You have to take you date returned into account.

[Khalil Handal]

pbaby, Thanks for the note and the answer. The link was of great help.

What I have in mind is using the field fbBookStatus that will be updated to (2) meaning that it is borrowed (not available.) I will work on this later.

One more issue.
If the Docmd.SetWarnings false
Does the Onerror event works???

A second thing:
What is the error code number for this message in access 2010


Khalil

[pbaldy]

I believe error handling will still work with SetWarnings off, though I'm not sure VBA considers that an error. I'd probably use a database variable and the Execute method which lets you check RecordsAffected.

[Khalil Handal]

Hi,
The error handler works.

As for the DCount, I have this which has a problem:

If DCount("*", "tblBorrowedBooks", "fkBookId = " & Me.txtfkBookId _            & " AND fkMemberId = " & Me.cboMember _
            & " AND DateBorrowed=#" & Me.txtDateBorrowed & "#" _
            & " AND DateReturned=#" & Me.txtDateReturned & "#") > 0 Then
                MsgBox "A book matching these details already exist!", vbExclamation, "Library Program"

Even when I used one field "fkBookId" instead of "*" still stoppes the compile at .txtDateBorrowed in (Me.txtDateBorrowed) the third line of the code.

any suggestions
Khalil

[pbaldy]

In my experience it could mean that any of the form references are wrong. It seems to get confused when there are multiple within a statement like that. Check all the form references and make sure they're valid control names.

[ssanfu]

Maybe try the following code.
If you set a breakpoint, then single step through the code, you would be able to check the values.....

Dim BookId_fk As Long
Dim MemberId_fk As Long
Dim dtBorrowed As Date
Dim dtReturned As Date


BookId_fk = Me.txtfkBookId
MemberId_fk = Me.cboMember
dtBorrowed = Me.txtDateBorrowed
dtReturned = Me.txtDateReturned


BookCount = DCount("*", "tblBorrowedBooks", "fkBookId = " & BookId_fk _
            & " AND fkMemberId = " & MemberId_fk _
            & " AND DateBorrowed= #" & dtBorrowed  & "#" _
            & " AND DateReturned= #" & dtReturned & "#")

If BookCount > 0 Then
   MsgBox "A book matching these details already exist!", vbExclamation, "Library Program"

   'possible other commands
End if