Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DLookUp BeforeUpdate

Discussion in 'Information Technology' started by MikeCCC, Jul 28, 2006.

  1. MikeCCC

    MikeCCC
    Expand Collapse
    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
     
  2. kerry_ja@yahoo.com

    kerry_ja@yahoo.com
    Expand Collapse
    Guest

    Hi Mike,

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


    MikeCCC wrote:
    > 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
     
  3. Rod Plastow

    Rod Plastow
    Expand Collapse
    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

    "kerry_ja@yahoo.com" wrote:

    > Hi Mike,
    >
    > Why not just have the Client name Indexed with no duplicates allowed?
    >
    >
    > MikeCCC wrote:
    > > 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

    >
    >
     
  4. MikeCCC

    MikeCCC
    Expand Collapse
    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


    "kerry_ja@yahoo.com" wrote:

    > Hi Mike,
    >
    > Why not just have the Client name Indexed with no duplicates allowed?
    >
    >
    > MikeCCC wrote:
    > > 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

    >
    >
     
  5. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    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

    --
    Wayne
    Manchester, England.



    "MikeCCC" wrote:

    > 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
    >
     
  6. Rod Plastow

    Rod Plastow
    Expand Collapse
    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


    "MikeCCC" wrote:

    > 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
    >
    >
    > "kerry_ja@yahoo.com" wrote:
    >
    > > Hi Mike,
    > >
    > > Why not just have the Client name Indexed with no duplicates allowed?
    > >
    > >
    > > MikeCCC wrote:
    > > > 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

    > >
    > >
     

Share This Page