DLookUp BeforeUpdate

[Guest]

Hello

I'm trying (and have been for ages) to get this right but am having big
problems. Any help would be really greatly appreciated.

To stop a client being placed on our records twice and am trying using the
BeforeUpdate of the input form ([cboName2]) to give a msg if they already
have a record.

Table - tblClients = Form – frmClients
Field(txt) - CD1stName = cboName1
Field(txt) – CDSurname = cboName2


Private Sub Surname_AfterUpdate()
If (Eval("DLookUp(""[CDClientID]"",""tblClients"",""[CD1stName] =
Me.[cboName1]""And Form.[CDSurname] = [cboName2]")) Then
MsgBox "Test Box", vbOKOnly, "Already in records"
End If

End Sub

Many thanks in advance.

Mike

 

[kerry_ja]

Hi Mike,

Why not just have the Client name Indexed with no duplicates allowed?

 

[Guest]

Hi Mike,

Kerry_ja's suggestion has merit but you will need to specify a compound key
(index) consisting of the surname and first name(s) to make it work. You may
even consider including a special column on your table for a sort key where
you concatenate whatever data is appropriate to: 1 - provide a unique key and
2 - provide a value that can be used to sort your records into a
sensible/meaningful sequence.

Otherwise I'm a little confused; it doesn't take much! You talk of using
the form's BeforeUpdate event but your example seems to illustrate the use of
a field's AfterUpdate event. If you use the form's BeforeUpdate event (or
any other object's BeforeUpdate event for that matter) there is a Cancel
parameter that you can set to cancel the update. You could then interrogate
the user to find out whether a mistake was made with the name or whether the
user wants to exit, etc., etc.

Regards,

Rod

Hi Mike,

Why not just have the Client name Indexed with no duplicates allowed?

Hello

I'm trying (and have been for ages) to get this right but am having big
problems. Any help would be really greatly appreciated.

To stop a client being placed on our records twice and am trying using the
BeforeUpdate of the input form ([cboName2]) to give a msg if they already
have a record.

Table - tblClients = Form - frmClients
Field(txt) - CD1stName = cboName1
Field(txt) - CDSurname = cboName2


Private Sub Surname_AfterUpdate()
If (Eval("DLookUp(""[CDClientID]"",""tblClients"",""[CD1stName] =
Me.[cboName1]""And Form.[CDSurname] = [cboName2]")) Then
MsgBox "Test Box", vbOKOnly, "Already in records"
End If

End Sub

Many thanks in advance.

Mike

 

[Guest]

Did think of that but there are many John Smiths, Jane Jones', etc so just
need a msg to let the user know that this person "may" have a record.

I "almost have it" with :-
If (Eval(DLookup("[CDClientID]", "tblClients", "[CD1stName] = '" &
Me.[Name1] & "'" And [CDSurname] = Me.[Name2]) Is Not Null)) Then

But this only checks the 1st name

Hi Mike,

Why not just have the Client name Indexed with no duplicates allowed?

Hello

I'm trying (and have been for ages) to get this right but am having big
problems. Any help would be really greatly appreciated.

To stop a client being placed on our records twice and am trying using the
BeforeUpdate of the input form ([cboName2]) to give a msg if they already
have a record.

Table - tblClients = Form - frmClients
Field(txt) - CD1stName = cboName1
Field(txt) - CDSurname = cboName2


Private Sub Surname_AfterUpdate()
If (Eval("DLookUp(""[CDClientID]"",""tblClients"",""[CD1stName] =
Me.[cboName1]""And Form.[CDSurname] = [cboName2]")) Then
MsgBox "Test Box", vbOKOnly, "Already in records"
End If

End Sub

Many thanks in advance.

Mike

 

[Guest]

Use this code in AfterUpdate - Note you have some of your code back to front
so I have changed it and also removed the extra "".


Private Sub CDSurname_AfterUpdate()
On Error GoTo CDSurname_AfterUpdate_Err

If ((DLookup("[CDClientID]", "[TblClients]", "[CD1stName] ='" &
Form!cboName1 & "' AND [CDSurname] = '" & Form!cboName2 & "'"))) Then
Beep
MsgBox "Already in records", vbOKOnly, "Text Box"
End If

CDSurname_AfterUpdate_Exit:
Exit Sub

End Sub



Hope this helps

 

[Guest]

Hi again Mike,

Your additional post makes things somewhat clearer. Personally I would
forget using any field event for this purpose and concentrate on the form's
BeforeUpdate event. There is also the consideration as to whether you want to
extract near matches as possible duplicates: are J Smith, Jon Smith and
Jonathan Smith the same person? Let's leave that for the moment but you must
allow for the fact that there may be more than one exact match and you must
give your user the opportunity to examine all of those records to see if one
of them is a duplicate.

OK, within the form's BeforeUpdate event the rough sequence is to: 1 -
perform all validation; 2 - use DCOUNT to determine whether there are any
potential duplicates; 3 - if there are potential duplicates then list all
matching records on a pop up, modal form asking the user whether he/she wants
to cancel the new user or force a new record to be added; 4 - if the user
cancels then set the CANCEL argument on the BeforeUpdate to TRUE and clean
up; 5 - if the user wants to add the record then exit the BeforeUpdate sub
normally and let Access do its thing.

If you need any code samples I am willing to help but we ought to take that
offline.

Regards,

Rod

Did think of that but there are many John Smiths, Jane Jones', etc so just
need a msg to let the user know that this person "may" have a record.

I "almost have it" with :-
If (Eval(DLookup("[CDClientID]", "tblClients", "[CD1stName] = '" &
Me.[Name1] & "'" And [CDSurname] = Me.[Name2]) Is Not Null)) Then

But this only checks the 1st name

Hi Mike,

Why not just have the Client name Indexed with no duplicates allowed?

Hello

I'm trying (and have been for ages) to get this right but am having big
problems. Any help would be really greatly appreciated.

To stop a client being placed on our records twice and am trying using the
BeforeUpdate of the input form ([cboName2]) to give a msg if they already
have a record.

Table - tblClients = Form - frmClients
Field(txt) - CD1stName = cboName1
Field(txt) - CDSurname = cboName2


Private Sub Surname_AfterUpdate()
If (Eval("DLookUp(""[CDClientID]"",""tblClients"",""[CD1stName] =
Me.[cboName1]""And Form.[CDSurname] = [cboName2]")) Then
MsgBox "Test Box", vbOKOnly, "Already in records"
End If

End Sub

Many thanks in advance.

Mike