Find Duplicates in two fields against Another two fields

Local time
Today, 00:39
Joined
Apr 29, 2001
Messages
47
I am trying to find out when a user enters an account number and account
name into a from, I want to be able to see if these details are held against and old table called old account number or old account name, if it does I what to flag a record to indicate that this is the case.

Does anyone have a way of achieving this?

Regards PR
 
Use DCount(). You can look up each field independently or both fields together depending on what your business rules are. for example:

FoundCount = DCount("*","SomeTable","FieldA = " & Me.FieldA & " AND FieldB = """ & Me.FieldB & """)

this example assumes that FieldA is numeric and FieldB is a string.
 
Pat,
Thanks for this, how do I use this to set a flag...

do I .update record if foundcount = true?

Regards - Paul
 
Pat,
I also get a compile error: expected: list separator or )

regards - Paul
 
What I wrote was psuedo code since I have no idea what your table or field names are. You need to supply your own names. I was just attempting to show you the structure. If the count is >0 then a record was found and you probably do not want to update. Post your code and someone will help you with it.
 
sorry Pat,
here is my code... but I may have over complicated it...

>>>>>>>code start<<<<<<<
Private Sub AGA_BankSortCode_AfterUpdate()
Dim db As Database
Dim rstRecordset As Recordset
Dim strFoundCount As String

Set rstRecordset = CurrentDb.OpenRecordset("tblPersonnelDetails")

strFoundCount = DCount("*","tblPersonnelDetails","AWSA_BankAccountNo = """ & me.AGA_BankAccountNo & """ AND AWSA_BankSortCode = """ & Me.AGA_BankSortCode & """)

If strFoundCount > 0 Then

With rstRecordset
!flag_dup_bank = ytes
.Update
End With

>>>>>end of code<<<<<

Regards - Paul
 
You have a space in a column name. Might need brackets:

[AWSA_BankAccount No]

Also the usual way is to use a sinqle quote char to delimit strings, not a double quote char (but maybe double works too). If the field is numeric, don't add any quotes. I'll assume that both are non-numeric

FoundCount = DCount("*","tblPersonnelDetails","[AWSA_BankAccount No] = '" & me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'")
 
Another problem:

Dim strFoundCount As String

Dcount should return a numeric value, so why you have FoundCount as string? May I propose:

Dim FoundCount as Long
 
Jal,
Many thanks for this suggestion; I now get an error with this line highlighted:

Stating runtime error 2001 – you cancelled previous operation?

strFoundCount = DCount("*", "tblPersonnelDetails", "[AWSA_BankAccount No] = '" & Me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'")


regards - Paul
 
Jal,
Many thanks for this suggestion; I now get an error with this line highlighted:

Stating runtime error 2001 – you cancelled previous operation?

strFoundCount = DCount("*", "tblPersonnelDetails", "[AWSA_BankAccount No] = '" & Me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'")


regards - Paul

That usually means one of your column names (or your table name) is spelled wrong. In a moment I'll give you an alternative to Dcount

And by the way, did you redefine strFoundCount? It shouldn't be a string. It should be long

Dim FoundCount as Long
 
Try this code:

Public Function ReturnOneValue(ByVal query As String) As Variant
Dim rs As New ADODB.Recordset
rs.Open query, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.recordCount > 1 Then
Call Err.Raise(500, , "Your custom Execute Scalar method has returned more than one value.")
ElseIf rs.recordCount = 0 Then
ReturnOneValue = Null
Else: ReturnOneValue = rs("Result")
End If
rs.Close
Set rs = Nothing
End Function

Here's how to use it. Thie function assumes that you use the word "Result" in the query string.

Dim FoundCount as Long, SQL as String
SQL = "SELECT COUNT(*) as Result FROM tblPersonnelDetails WHERE [AWSA_BankAccount No] = '" & Me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'"
MsgBox SQL 'for testing purposes
FoundCount = ReturnOneValue(SQL)

When the MsgBox popus up, use Control-C to copy and paste the SQL into SQL view (manually delete the extra chars) and run it from SQL view. This will help you figure out if any column names are spelled wrong.
 
Jal,
You were absolutely right I had name a table incorrectly... thanks for the code your time and patience…


Regards - Paul
 
Dear All,
I thought I had this but the record does not update... have I done this correct?

<<<<<Code>>>>>
Private Sub AGA_BankSortCode_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rstRecordset As Recordset
Dim strFoundCount As Long
Dim upFlag As Integer
upFlag = -1

Set rstRecordset = CurrentDb.OpenRecordset("tbl_temp_Account")

strFoundCount = DCount("*", "tbl_temp_Account", "[AWSA_BankAccountNo] = '" & Me.AGA_BankAccountNo & "' AND AWSA_BankSortCode = '" & Me.AGA_BankSortCode & "'")

If strFoundCount > 0 Then

MsgBox "This member as a Duplicated Bank Records" & Chr(13) & Chr(10) & _
" Thier Record will be marked as a Duplicate", vbYesNo

If vbYes Then ' User chose Yes.
With rstRecordset
.Edit
!flag_dup = -1
.Update
End With
Else
With rstRecordset
.Edit
!flag_dup = 0
.Update
End With
End If
End If
End Sub
<<<<<End Code>>>>

Regards - Paul
 
I would suggest two changes. First, invoke the function:

Dim response as Long
response = MsgBox("This member as a Duplicated Bank Records" & Chr(13) & Chr(10) & " Thier Record will be marked as a Duplicate", vbYesNo)


Second, change your IF-Then accordingly:

If response = VBYes Then
....flag it true
ElseIf response = VBNo Then
...Flag it false
End IF
 
Jal,
I am missing something simple here I am sure... the record does not seem to update... have watch the code run and put watch points on the varibles... which gives me the signs that this should work...

I have attached the db...

Regards - Paul
 

Attachments

One issue here (by the way it's hard for me to proceed with this because I myself avoid databinding even though all the Access pros seem to love it - it confuses the heck out of me).

When you open your recordset the pointer is to the first record. The record that you wish to flag may be a different record. You'll need some kind of logic to move to the correct record. Maybe I'll give you a tip.
 
Your textbox names are terribly confusing. In the table your column names are:

Account
AccountName

and on your form you use:

AWSA_BankAccountNo
AGA_BankSortCode

Arghh! This discrepancy makes your code REALLY hard to read and debug.
 
How is the user supposed to put your form to use? Is he supposed to scroll through the records using the record navigator buttons until he finds the desired record?

Your whole setup looks foreign to me. It's hard for me to proceed because I don't understand it.


And why would you want a column to flag dups? Why not just tell the user, "The data you entered is a dup of the data already in the table. Therefore, no changes will be made."
 
Jal,
sorry about this... I just quickly created a database to show what I was trying to do... I should off taken more care...

You maybe right I may not be updating the correct record...

Regards - Paul
 

Users who are viewing this thread

Back
Top Bottom