Searching for duplicate entries in database via a textfield

[cstickman]

Hello Everyone,

I am probably doing something wrong or have something in the wrong place. I am trying to a DLookup to see if a duplicate number already exists and if it is in the open status. The table name is settlements, the text field is called txtloan1, the columns in the database are loan1 and status. The status field is where it will check for the open status. Below is the code:

<code>
Private Sub txtloan1_AfterUpdate()

If IsNull(DLookup("[loan1]", "settlement", "[loan1] = " & Me.txtloan1.Text & " AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Loan Number already exists and is in open status", vbOKOnly, "Warning"
End If

End Sub
</code>

I have it triggered using the after update event within the text field property box. I do not want the user to fill out the form completely just to have it come back as a duplicate error.

Thanks!!

 

[ranman256]

dont use DLOOKUP.
use the query wizard, choose FIND DUPLICATES.
it will ask you where /when, etc to find
edit the query, put in your own parameters.

 

[cstickman]

Thanks Ranman, but I was able to get the code to work with the following:

<code>
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test Loan1", vbOKOnly, "Warning"
End If
</code>

The problem I am having now is it returns the first value. The loan could have multiple states and with it returning the first one it does not search for the second one. So now I guess I need to use a recordset, but have no idea where to begin to write that into this DLookup. Any thoughts?

Thanks

 

[Micron]

Umm, if you got it to work, you would not be seeking further help, no? You might have gotten "some" code to do something, but Dlookup will only return ONE value from anywhere, hence Ranman's very appropriate answer.

 

[Micron]

Or you need a select query to retrieve all occurrences where some field contains some value.