CHecking for Duplicates

[Firstomp]

I am an enthusiast in Access trying to do some god for the local chapter of the Salvation Army. I have been asked to create a database that when individuals information is entered, it checks to see if they have already registered either as a primary, a spouse, as another adult living in the household, or as a child. Every individual is requried to provide proof of residence and a proof of a valid SSN. I have choosen the SSN to check for duplicates in the related tables. I have the Primary and the spouses SSNs listed ont eh same table so checking for duplicates there is easy. What I am having problems with is checking the table with the other adults lliving in the household a duplicate SSN with the primary member. I have been trying to use DLOOKUP for two days now and can't seem to get it right. The Form is called "FormInformation" and contains subformscalled "Children subform" and "OtherAdults subform" each drawing from a table with the same name. The Primary Key in the Table "FormInformation" is [PrimarySSN] and the other tables are related to it through that key.
I got this little snipet of code form a website but I couldnot get it to work for my case. Any help would be greatly appreciated.

Code:

Dim x As Variant

   x = DLookup("[PrimarySSN]", "FormInformation", "[PRIMARYSSN] = '" & "Table![OtherAdults]![SSN] = '")

   On Error GoTo CustID_Err

   If Not IsNull(x) Then
      Beep
      MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
      Cancel = True
   End If

CustID_Exit:
   Exit Sub

CustID_Err:
   MsgBox Error$
   Resume CustID_Exit

[Chris147]

Hi,

So long as you have a Reference to the DAO Object Library you can do the following:

Dim dbs as DAO.Database, rstMain as DAO.RecordSet, rstLookUp as DAO.Recordset
Dim sql as String
Set dbs = CurrentDB
sql = "Select * From <Main Table Name Here>"
Set rstMain = dbs.OpenRecordset(sql)
sql = "Select * From <Table To Check Name Here>"
Set rstLookUp = dbs.OpenRecordset(sql)
If rstMain.RecordCount = 0 Then
Exit Sub 'Empty Table - Quit
Else
rstMain.MoveFirst
End If
if rstLookUp.RecordCount = 0 Then
Exit Sub
Else
rstLookUp.MoveFirst
End If
Do While Not rstMain.EOF
Do While Not rstLookUp.EOF
if rstLookUp!<Field To Check Name Here> = rstMain!<Field To Check Against> Then
MsgBox "Oops " & rstMain!!<Field To Check Against> & " has been duplicated."
end if
rstLookUp.MoveNext
Loop
rstMain.MoveNext
Loop
'Clean Up:
Set rstLookUp = Nothing
Set rstMain = Nothing
Set dbs = Nothing

[Firstomp]

I still don't seem to have it. I am now getting an error message: Error 3265, Item not found inthis collection:

This is what I have currently.

Private Sub PRIMARYSSN_BeforeUpdate(Cancel As Integer)

Dim dbs As dao.Database, rstMain As dao.Recordset, rstLookUp As dao.Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Select * From FormInformation"

Set rstMain = dbs.OpenRecordset(sql1)
sql = "Select * From OtherAdults"

Set rstLookUp = dbs.OpenRecordset(sql)
If rstMain.RecordCount = 0 Then
Exit Sub 'Empty Table - Quit
Else
rstMain.MoveFirst
End If
If rstLookUp.RecordCount = 0 Then
Exit Sub
Else
rstLookUp.MoveFirst
End If
Do While Not rstMain.EOF
Do While Not rstLookUp.EOF
If rstLookUp![PRIMARYSSN] = rstMain![SSN] Then
MsgBox "Oops " & rstMain![PRIMARYSSN] & " has been listed as another Adult living in a household."
End If
rstLookUp.MoveNext
Loop
rstMain.MoveNext
Loop
'Clean Up:
Set rstLookUp = Nothing
Set rstMain = Nothing
Set dbs = Nothing

End Sub

[Chris147]

Hmn...

About all I can see wrong is (assusming you have pasted your code from Access VBA) you have a typo in the 'Set' event for rstMain (should be: "Set rstMain = dbs.OpenRecordset(sql)" - not "...dset(sql1)".

You also don't need the brakets around around the table names in the If.. ..Then statement - you only need brackets when you have spaces in your table names and that's bad practice.

If none of these fix the problem double check the table names and field names - the error you have is Access looking for, and not finding, one of the above.

HTH,

Chris.

[Firstomp]

I have corrected and verified all of the tables. The only items with brackets are fields. the error that is coming up in the following line of code:

If rstLookUp![PRIMARYSSN] = rstMain![SSN] Then

When I put the mouse over either rstLookUp![PRIMARYSSN] or rstMain![SSN], I get a little message saying item is not found in this collection?

[Chris147]

Are you sure they are in the right order?

According to your last post PRIMARYSSN a Field in OtherAdults and SSN is a Field in FormInformation.

If there are no spaces in the Field Names (and there clearly isn't here) get rid of the brackets.

If no joy post here or send a personal message and I'll pick it up.

HTH,

Chris.

[Firstomp]

Thank you Chris for all of your help. I figured it out. rstLookUp and rstMain reversed. It should read

rstMain![PRIMARYSSN] or rstLookup![SSN]

Thanks again. The Salvation Army and myself greatly appreciate your assistance.

Bob