Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179

    Popup Message for Duplicate Entry

    Hello,



    When opening the database there is the Main Form. The Clerk button is for entering new records (password "clerk"). On that form there is an AFR Number field. Is there a way to popup a message there if the user enters a previous AFR Number? I'm not sure if this will conflict with searching for AFR Numbers on the other forms: Administrator (password "admin"), Technician (password "tech") or the Research form (no password).

    I appreciate the help,
    Bill

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    usual way would be to set an index on your AFR field and set allow duplicates to No

    alternative is use the dcount function in the AFR afterupdate event - something like

    Code:
    if dcount("*","myTable","AFR=" & txtAFR)>0 then
        msgbox "This number already exists please endter another one"
        txtAFR=null
    end if

  3. #3
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thank you Ajax. I have the index in the table set to no for duplicates but I was hoping to immediately have the message. Will this code do that?

  4. #4
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Hi,

    I entered the code but it goes to debug what did I do wrong?

    Code:
    Private Sub AFRNumber_AfterUpdate()
        If DCount("*", "AFRs", "AFRNumber=" & txtAFRNumber) > 0 Then
        MsgBox "This AFR Number already exists please enter another number"
        txtAFRNumber = Null
        End If
        
    End Sub

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    no idea, what line is highlighted? what is the error message?

    My guess would be you appear to have the control named the same as the field

  6. #6
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    The "If" line is highlighted

    Run-time error '3075':
    Syntax error (missing operator) in query expression 'AFRNumber='.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    try replacing txtAFRNumber with just AFRNumber - also need to do this a couple of lines down

    there is a naming convention used by many professional developers to indicate the type of control (e.g. txt means textbox, cbo means combo, etc). It is a practice worth adopting because it enables you to easily differentiate between the control and the associated field.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Note, the code provided is basic without any error control - it also assumes that the AFR number is numeric and not text. You might find you get an error if the user changes the AFR from some valid value to blank

  9. #9
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Okay I have no errors with this code but it doesn't allow 'any' number to be entered. It should allow unique numbers to be entered:

    Code:
    Private Sub AFRNumber_AfterUpdate()
        If DCount("*", "AFRs", "AFRNumber" & txtAFRNumber) > 0 Then
            MsgBox "This AFR Number already exists please enter another number"
            AFRNumber = Null
            Me.AFRNumber.SetFocus
        End If
    
    
    End Sub
    Any ideas?

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You need to format the txtAFRNumber Control as Numeric

  11. #11
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thanks Mike is this done at table level or from the form?

  12. #12
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I cannot get this to work sorry.

  13. #13
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Maybe it's not possible sorry for the trouble.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your criteria is not correct. This

    DCount("*", "AFRs", "AFRNumber" & txtAFRNumber)

    should be this (presuming a numeric data type):


    DCount("*", "AFRs", "AFRNumber = " & txtAFRNumber)

    Personally I use the before update event, which can be cancelled.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thank you pbaldy for helping.

    I attempted but failed making the adjustment.

    Code:
    Private Sub AFRNumber_BeforeUpdate(Cancel As Integer)
    If DCount("*", "AFRs", "AFRNumber=" & txtAFRNumber) > 0 Then
            MsgBox "This AFR Number already exists please enter another number"
            AFRNumber = Null
            Me.AFRNumber.SetFocus
        End If
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-13-2018, 12:59 AM
  2. Replies: 4
    Last Post: 04-26-2018, 01:23 PM
  3. Replies: 4
    Last Post: 01-14-2016, 02:18 PM
  4. Duplicate data message for a data entry form
    By JulieMarie in forum Access
    Replies: 5
    Last Post: 07-30-2013, 08:18 AM
  5. Command continues to popup message box..
    By Stephanie53 in forum Forms
    Replies: 12
    Last Post: 05-30-2013, 02:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums