Here is my problem, I am trying to check for duplicates and if any found find the matching customer and go to that record. My coding is working all the way up to checking for a duplicate and warning the user that there is one, but that is where I am stuck. I need some code that will then find the matching user and go to that record. Here is the code that I am using now, can anyone add on? Thanks very much in advance.
Private Sub HomePhone_AfterUpdate()
Dim Answer As Variant
If DCount("[HomePhone]", "customer tbl", "[homephone] = [Forms]![customer frm]![homephone]") = 1 Then
Answer = MsgBox("This Customer is already in the Database. OK to find existing record", vbOK, "Duplicate!")
If Answer = vbOK Then Cancel = True
Exit Sub
End If
End Sub
I would suggest using an unbound combo box for this. One of the primary advantages is you get a fairly capable wizard to help you with the intricacies. Make sure you pick the third option, "Find an existing record..."
If it helps, I went through exactly the same stages as you did. First I had No Duplicates set, then I got it to check for duplicates with Dcount, then I made a combo box to lookup existing records and go to them. I can post for you all the relevant code I used if it will help.
That is good advise, but my users aren't going to take the time to check a combo box to see if the customer is already in there. Sometimes they just start typing and never even think to check. So this would be like their check.
You would use the Not In the List event which would then open a normal data entry form. Take a look at the "Solutions" sample database it has examples of how to do this, along with several more. It should be in the samples folder or on the MS Download site.
HTH
Although I appreciated the advice about the combo boxes, I would really like to just use some code to do this. All I really need is some code to use the data in the [Homephone] field and find the matching data and move my focus there. Does anyone know how to do this? Thanks
The trick is to make it comprehensive for you, and still as easy for them as it's ever been. Here's what I use, which works in 95% of the cases (see disclaimer).
Logically speaking, there are four cases I was concerned with:
a) In an old record, looking for an existing record. We'll move to the other record.
b) In an old record, looking for a record that doesn't exist. We move to a new record and move the value in the lookup box over to the primary key box.
c) In a blank record, looking for an existing record. We'll move to the existing record, making sure we blank out the new one if it's been partially entered.
d) In a blank/new record, looking for a record that doesn't exist. We blank out the current record if it's been partially entered, and move the value in the lookup box to the primary key box.
My Primary Key is ParticipantID, so I have an unbound Combobox called ParticipantID_Lookup. In the AfterUpdate event, put the following:
Code:
Private Sub ParticipantID_Lookup_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
'Include check for Len(ParticipantID) = 6
Set rs = Me.Recordset.Clone
rs.FindFirst "[ParticipantID] = '" & Me.ParticipantID_Lookup & "'"
If rs.Nomatch = True Then
If (Me.NewRecord = True) Then
If (Me.Dirty = True) Then
Me.Undo
Else
End If
If Me.ParticipantID_Lookup <> "" Then
Me.ParticipantID = Me.ParticipantID_Lookup
Me.ParticipantID_Lookup = ""
End If
Else
DoCmd.GoToRecord , , acNewRec
End If
Else
'Go to the matching record
Me.Bookmark = rs.Bookmark
End If
Me.ParticipantID_Lookup.Requery
Me.subformDevices.Requery
End Sub
Along with this I needed code in the Form's Current event to make it more user-friendly:
Code:
Private Sub Form_Current()
'Interfaces with ParticipantID_Lookup_AfterUpdate() to clear the lookup box and where to set the cursor
If (Me.ParticipantID_Lookup = "") Then 'No lookup, just browsing records
Me.ParticipantID_Lookup.SetFocus
Exit Sub
Else
If Me.NewRecord = False Then
Me.FirstName.SetFocus
Else
'New record, import the data stored in Me.ParticipantID_Lookup
If (Me.ParticipantID <> "") Then Me.ParticipantID = Me.ParticipantID_Lookup
Me.ParticipantID.SetFocus
End If
End If
'Clean up our mess
Me.ParticipantID_Lookup = ""
End Sub
Diclaimer: Occasionally when I've been looking up new records, the ParticipantID_Lookup box will not move over, but instead clear. However my coworker, who does 90% of the data entry, doesn't notice it, so I don't worry about it much. I figure it has something to do with me filling the lookup box, searching, then clearing it and looking up something else w/o changing records.
I hope that helps. I realize it's a lot of code to look at but most of it does pretty straightforward things.
David R
[This message has been edited by David R (edited 01-09-2002).]
The only thing I needed to train my coworker to do was type the ParticipantID into the unbound combo box, instead of the blank box. The fact that Form_Current sets the focus there helps this considerably.
