Checking if new record already in table 1

[upplepop]

hey guys, I need some help.

When someone enters a client's [first] and [last] name into the system, I would like the DB to check if that name is already in the [Clent] table. If so, a message box should appear notifying the user of that fact. Can anyone point me in the right direction?

 

[scriverb]

In the AfterUpdate procedure of the LastName field put the following code:

Dim db as DAO.database
Dim rs as DAO.recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("tblClient", dbOpenDynaset)
rs.FindFirst "[FirstName] = '" & me![ctlFirstName] & _
"' and [LastName] = '" & me![ctlLastName] & "'"
If Not rs.NoMatch then
MsgBox "This is a duplicate entry by Name"
Me![FirstName].setfocus
else
'Continue - Unique Name
end if
rs.close
db.close

You will have to update this with the names of your table and controls but this should give you a message box when a duplicate is found and send the cursor focus back to the FirstName control. Otherwise, control will continue on to the control in the Tab order after the LastName.

Let me know if this works for you. Bob Scriver

 

[KyleS]

Hi,
First off, I think you might want to use name and address as there are many people with the same name, even in smaller areas.

You could use an agregate function like DLookup() to run your check, that would look something like this:

Private Sub cmdCheckForPrevious_Click()

If len(dlookup("ClientID","tblCustomers","tblCustomers.FName = '" & Me!txtFName & "' And tblCustomers.LName = '" & Me!txtLName & "'&quot & "&quot > 0 Then

Msgbox "You have entered a duplicate name!"
Docmd.CancelEvent

Else

Do Your Thing Here

End If

End Sub

This is of course missing error handling and the such, but should give you what you're looking for. Although I still strongly suggest the use of Address as well.

Or you could use a recordset, which wouldn't be of much advantage unless you had something like 20,000 customers, then it will speed things up for you. It would also be usefull if you intend on giving the user a list of matching names so if there is a dup entry they can select the name from a list. DLookup() will not give you this option...


Private Sub cmdCheckForPrevious_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblCustomers WHERE tblCustomers.FName = '" & Me!txtFName & "' And tblCustomers.LName = '" & Me!txtLName & "';"

If rst.RecordCount > 0 Then

Msgbox "You have entered a duplicate name!"
Docmd.CancelEvent

Else

Do Your Thing Here

End If

End Sub
Kyle

 

[KyleS]

Of course, I left off the a line from the recordset code, so let me repost...

Private Sub cmdCheckForPrevious_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblCustomers WHERE tblCustomers.FName = '" & Me!txtFName & "' And tblCustomers.LName = '" & Me!txtLName & "';"

Set rst = CurrentDB.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then

Msgbox "You have entered a duplicate name!"
Docmd.CancelEvent

Else

Do Your Thing Here

End If

End Sub Kyle

 

[upplepop]

I am having a problem with these lines:

Dim db as DAO.database
Dim rs as DAO.recordset

The error message says "User-defined type not defined" How can I fix this?

 

[KyleS]

Ah, you're using Access 2000 or XP.

The fix is simple, just go into the code page, click Tools --> References and check the box next to Microsoft DAO Objects X.X Kyle

 

[upplepop]

KyleS said that a RecordSet could
"It would also be usefull if you intend on giving the user a list of matching names so if there is a dup entry they can select the name from a list. DLookup() will not give you this option..."

How can I get it to do this?

 

[upplepop]

Is there any way I can return the ClientID so the user can look up the previous record?

 

[KyleS]

OK,

We could just give a message box with a list of ID's, or we could give them a form with a combo box and let them select which previous entry they want and go directly to it (usually only one, but give them the option of many) OR we could have the code check for the number of duplicates, if there is one we'll send them directly to that record and if there are multiple dups, then we'll give them the form to select from.

Let me know which way you want to go... Kyle

 

[upplepop]

Wow, KyleS is a great help!

Well, I think that popping up a combo box would be the best way to go. How do I go about this?

 

[KyleS]

Hey now, don't go spreading rumors!

OK, you need to build a form with a combo box and 2 buttons, label the combo box whatever you want (whatever is appropriate as it will hold the dup info) and one of the buttons is for navigating to that record and the other is you "Close" button. I'm going to let you handle the close button, but so let's start with opening the form.

Change the code you have from before to this:

Private Sub cmdCheckForPrevious_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblCustomers WHERE tblCustomers.FName = '" & Me!txtFName & "' And tblCustomers.LName = '" & Me!txtLName & "';"

Set rst = CurrentDB.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then

'Msgbox "You have entered a duplicate name!"
Docmd.OpenForm &quot;Your Form Name Here&quot; '< --- This is the new line of code
do until rst.EOF
Forms!YourFormName!YourComboBox.RecordSource = rst!ID & &quot;;&quot; & rst!LName & &quot;, &quot; & rst!FName & &quot;;&quot;
rst.movenext
loop

Else

Do Your Thing Here

End If

rst.Close
Set rst = Nothing

End Sub
Now for the OnClick of the button on your new form...

It kind of depends on what you want to do, I'm going to assume we will be taking the same form the user was entering into and then navigate to the record that has been selected.

Somehting like this:

Private Sub cmdNav_Click()

Forms!YourFormName.RecordsetClone.Findfirst &quot;ID =&quot; & Me!?YourComboName

Forms!YourFormName.Bookmark = Forms!YourFormName.RecordsetClone.Bookmark

Docmd.Close acForm, Me!Name

End Sub

This seems to be a bit more complicated than I would like, but I'm just having mental problems today : )


Kyle

 

[upplepop]

I am having trouble with this code:


'Look for duplicate entries of first/last name in DB
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = &quot;SELECT * FROM Client WHERE Client.[FirstName] = '&quot; & Me!txtFirst & &quot;' And Client.[LastName] = '&quot; & Me!txtLast & &quot;' ;&quot;

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then

MsgBox (&quot;This name already appears &quot; & rst.RecordCount & &quot; time(s) in the Database&quot
DoCmd.OpenForm &quot;frmSelectionClient&quot;
Do Until rst.EOF
Forms!frmSelectionClient!cmbRecords.RecordSource = rst![CaseNumber] & &quot;;&quot; & rst![LastName] & &quot;, &quot; & rst![FirstName] & &quot;;&quot;
rst.MoveNext
Loop

End If

rst.Close
Set rst = Nothing

End Sub


I get an &quot;Object does not support this property or method&quot; on this line: Forms!frmSelectionClient!cmbRecords.RecordSource = rst![CaseNumber] & &quot;;&quot; & rst![LastName] & &quot;, &quot; & rst![FirstName] & &quot;;&quot;


What's going wrong?

Also, I agree with people who stated above that I should include the DOB when looking for duplicates. But I'm having trouble with that too. This isn't working:

strSQL = &quot;SELECT * FROM Client WHERE
Client.[FirstName] = ' &quot; & Me!txtFirst & &quot; ' And
Client.[LastName] = ' &quot; & Me!txtLast & &quot; ' And
Client.[DOB] = ' &quot; & Me!txtDOB & &quot; ' ;&quot;

I get a &quot;Data Type mismatch in criteria expression&quot; error.


I have a feeling these errors are really easy to fix, but I just can't seem to get a hold on them!

Thanks!

 

[MichaelRed]

Considering all the trouble alreadt afoot in this thread, I really should not tread these waters. Alas alack already awery. Using an adress is also perhaps also not the best approach, as the 'free form' data entry in use these days leaves A LOT to be desired in terms of consistiency, so the 'same' address is simply entered in seeral or more formats:

123 Elm St.
123 Elm Street
123 Elm St
Combine Bldg 123 Elm St

... ad naseum

Better to use a common, short and unique identity. Phone number is my personal favorite. You can reasonably ask for and expect a ten digit phoine number for the U.S. and expect it to be reasonably unique.

Otherwise, I think I'll just butt out.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[KyleS]

OK,

The frist error is all my fault, change: Forms!frmSelectionClient!cmbRecords.RecordSource = rst![CaseNumber] & &quot;;&quot; & rst![LastName] & &quot;, &quot; & rst![FirstName] & &quot;;&quot;

To:
Forms!frmSelectionClient!cmbRecords.RowSource = rst![CaseNumber] & &quot;;&quot; & rst![LastName] & &quot;, &quot; & rst![FirstName] & &quot;;&quot;

As for your second problem, the SQL is right except you need to remove the ' from your date. Those are only for text.

So:

strSQL = &quot;SELECT * FROM Client WHERE
Client.[FirstName] = '&quot; & Me!txtFirst & &quot;' And
Client.[LastName] = '&quot; & Me!txtLast & &quot;' And
Client.[DOB] = &quot; & Me!txtDOB & &quot;;&quot;

Should be what you are looking for. Kyle