Duplicate Entries

[TriniGal]

Hello,

I have the following code attached to the subform in my main form and it works fine.

Private Sub Location_BeforeUpdate(Cancel As Integer)
    
        Dim varLocation As Variant
    
    If Me.NewRecord Then
    
        varLocation = DLookup("[Location]", "LEAK FOUND TABLE - 2", "[Location] = '" & Me.Location & "'")
            
        If Not IsNull(varLocation) Then
        
            If MsgBox("This record already exists." & _
                        "  Do you want to cancel these changes and go to that record instead?", _
                        vbQuestion + vbYesNo, _
                        "DUPLICATE ADDRESS FOUND") _
                        = vbYes _
            Then
                Cancel = True
                Me.Undo
                Me.Recordset.FindFirst "[Location] = '" & varLocation & "'"
                bolCheckDuplicate = True
            End If
            
        End If
        
    End If

Exit_Location_BeforeUpdate:
    Exit Sub

Err_Location_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_Location_BeforeUpdate

End Sub

My problem is that diffent cities in my main form may have the same street name, therefore the address of a problem may be duplicated between cities. I have seen the vlookup using multiple criterias but I haven't seen any using multiple criterias in two different froms (main form and subfom).

Does anyone know how I would change my code to check the community name in my main form and the location in my subform?

FYI...they are two diffent tables also.

Thanks in advance for you help

 

[formerTexan]

TriniGal,

Consider creating a SQL based recordset instead of using DLookup. This will allow you to retrieve the info from multiple tables and fields. Also consider that DLookup will only return a single (first found) match, while a SQL statement can return multiple matches. As a bonus and all things being equal, SQL statements will usually return better performance than DLookup.

Cheers,
Bill

 

[TriniGal]

Bill,

I've never used a SQL based recordset instead of using the DLookup. I don't even know where to start. Can you help me do this with sample code or do you know where I can find any help on this?

Thanks

 

[formerTexan]

Something to the effect of the following. Caveat: this hasn't been tested, so there may be typos. And its rudimentary, so haven't trapped for errors or exhaustively tested for appropriate values. Also if you are using a A2003, you will need to make sure that the Microsoft DAO references are ticked in the VBA reference library.

Cheers, Bill

Dim db as dao.database
Dim rs as dao.recordset
Dim strSQL as string
Dim strWhere as string

strWhere = Me.Location & vbnullstring
strSQL ="SELECT [LEAK FOUND TABLE].Location FROM [LEAK FOUND TABLE] (((WHERE [LEAK FOUND TABLE].Location)='" & strWhere & "'))"
Set db = currentdb()
Set rs = db.openrecordset (strSQL)

If Not rs.bof and not rs.eof then
'there are rows in the recordset, so there must be duplicates
' act accordingly
End If

rs.close
set rs = nothing
db.close
set db = nothing