Welcome to SPN

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

Sign Up Now!

message box on duplicate value

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

  1. gopi

    gopi
    Expand Collapse
    Guest

    Hi, im tyring to prompt the user with a message everytime a duplicate value
    is entered in a field. But i cant seem to get it working, the code im using
    is:
    ------------------------------------
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    Me.Text228.Value)) Then
    MsgBox "Record Exist"
    End If

    End Sub
    -----------------------------------

    South_Tracker is the table name,
    Upgrade Number is the field name in the table (which is a primark key)
    Text228 is the field name in the form.

    If anyone can help me with this code, i would much appreciate it.
    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    How message of love from Sikh gurus are helping Pakistani Christians to fight forced conversions Sikh Sikhi Sikhism Oct 12, 2015
    Messages from God Sikh Sikhi Sikhism Aug 23, 2015
    Message of Guru Granth Sahib Essays on Sikhism Jan 30, 2014
    World U.N. chief's message to North Korean leader Breaking News Apr 12, 2013
    Christianity Pope Francis prays for world peace in Easter Sunday message Interfaith Dialogues Apr 1, 2013

  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    DLookUp will look for a value in a field withinn a table, so you can't use *.

    If Not IsNull(DLookup("[Upgrade Number]", "South_Tracker", "[Upgrade Number]
    =" & Me.Text228.Value)) Then


    DCount will count how many records you have in the Table so you can use *

    If DCount("*", "South_Tracker", "[Upgrade Number] =" & Me.Text228.Value) > 0
    Then

    --
    Good Luck
    BS"D


    "gopi" wrote:

    > Hi, im tyring to prompt the user with a message everytime a duplicate value
    > is entered in a field. But i cant seem to get it working, the code im using
    > is:
    > ------------------------------------
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    > Me.Text228.Value)) Then
    > MsgBox "Record Exist"
    > End If
    >
    > End Sub
    > -----------------------------------
    >
    > South_Tracker is the table name,
    > Upgrade Number is the field name in the table (which is a primark key)
    > Text228 is the field name in the form.
    >
    > If anyone can help me with this code, i would much appreciate it.
    > Thanks
     
  4. davidp

    davidp
    Expand Collapse
    Guest

    "gopi" wrote:

    > Hi, im tyring to prompt the user with a message everytime a duplicate value
    > is entered in a field. But i cant seem to get it working, the code im using
    > is:
    > ------------------------------------
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    > Me.Text228.Value)) Then
    > MsgBox "Record Exist"
    > End If
    >
    > End Sub
    > -----------------------------------
    >
    > South_Tracker is the table name,
    > Upgrade Number is the field name in the table (which is a primark key)
    > Text228 is the field name in the form.
    >
    > If anyone can help me with this code, i would much appreciate it.
    > Thanks


    try using DLookup("[Upgrade Number]", "[South_Tracker]",
    "[Upgrade Number] =" & Text228)

    I found that everything must have [ and ], never tried it with an *.
     
  5. gopi

    gopi
    Expand Collapse
    Guest

    Thanks, that worked.

    "Ofer Cohen" wrote:

    > DLookUp will look for a value in a field withinn a table, so you can't use *.
    >
    > If Not IsNull(DLookup("[Upgrade Number]", "South_Tracker", "[Upgrade Number]
    > =" & Me.Text228.Value)) Then
    >
    >
    > DCount will count how many records you have in the Table so you can use *
    >
    > If DCount("*", "South_Tracker", "[Upgrade Number] =" & Me.Text228.Value) > 0
    > Then
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "gopi" wrote:
    >
    > > Hi, im tyring to prompt the user with a message everytime a duplicate value
    > > is entered in a field. But i cant seem to get it working, the code im using
    > > is:
    > > ------------------------------------
    > > Private Sub Form_BeforeUpdate(Cancel As Integer)
    > >
    > > If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    > > Me.Text228.Value)) Then
    > > MsgBox "Record Exist"
    > > End If
    > >
    > > End Sub
    > > -----------------------------------
    > >
    > > South_Tracker is the table name,
    > > Upgrade Number is the field name in the table (which is a primark key)
    > > Text228 is the field name in the form.
    > >
    > > If anyone can help me with this code, i would much appreciate it.
    > > Thanks
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    As far as I know, you can't use * in DLookup.

    Try replacing * with the name of a field that exists in the table (use
    [Upgrade Number] if you like)

    You should also set Cancel = True if a duplicate is found. Otherwise, it'll
    try to save it:

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Not IsNull(DLookup("[Upgrade Number]", _
    "South_Tracker", "[Upgrade Number] =" & _
    Me.Text228.Value)) Then

    MsgBox "Record Exist"
    Cancel = True

    End If

    End Sub


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "gopi" <gopi@discussions.microsoft.com> wrote in message
    news:B780E895-F95E-4CD6-8435-9B1F6ED49ECE@microsoft.com...
    > Hi, im tyring to prompt the user with a message everytime a duplicate

    value
    > is entered in a field. But i cant seem to get it working, the code im

    using
    > is:
    > ------------------------------------
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    > Me.Text228.Value)) Then
    > MsgBox "Record Exist"
    > End If
    >
    > End Sub
    > -----------------------------------
    >
    > South_Tracker is the table name,
    > Upgrade Number is the field name in the table (which is a primark key)
    > Text228 is the field name in the form.
    >
    > If anyone can help me with this code, i would much appreciate it.
    > Thanks
     
  7. gopi

    gopi
    Expand Collapse
    Guest

    Even better. Thanks

    "Douglas J Steele" wrote:

    > As far as I know, you can't use * in DLookup.
    >
    > Try replacing * with the name of a field that exists in the table (use
    > [Upgrade Number] if you like)
    >
    > You should also set Cancel = True if a duplicate is found. Otherwise, it'll
    > try to save it:
    >
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Not IsNull(DLookup("[Upgrade Number]", _
    > "South_Tracker", "[Upgrade Number] =" & _
    > Me.Text228.Value)) Then
    >
    > MsgBox "Record Exist"
    > Cancel = True
    >
    > End If
    >
    > End Sub
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "gopi" <gopi@discussions.microsoft.com> wrote in message
    > news:B780E895-F95E-4CD6-8435-9B1F6ED49ECE@microsoft.com...
    > > Hi, im tyring to prompt the user with a message everytime a duplicate

    > value
    > > is entered in a field. But i cant seem to get it working, the code im

    > using
    > > is:
    > > ------------------------------------
    > > Private Sub Form_BeforeUpdate(Cancel As Integer)
    > >
    > > If Not IsNull(DLookup("*", "South_Tracker", "[Upgrade Number] =" &
    > > Me.Text228.Value)) Then
    > > MsgBox "Record Exist"
    > > End If
    > >
    > > End Sub
    > > -----------------------------------
    > >
    > > South_Tracker is the table name,
    > > Upgrade Number is the field name in the table (which is a primark key)
    > > Text228 is the field name in the form.
    > >
    > > If anyone can help me with this code, i would much appreciate it.
    > > Thanks

    >
    >
    >
     

Share This Page