Results 1 to 5 of 5
  1. #1
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179

    Set Focus and Clear Control on Duplicate Code

    Hello,



    On my Clerk form (password "clerk") when entering an AFR Number that exists can the cursor return to the AFR Number control and have it cleared? Also when trying to exit this form with no entries it goes to debug can there be an error checker added? One AFR Number that exists is '99999'.

    Thank you very much for your help,
    Bill

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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Add:

    Me.AFRNumber = ""
    Cancel = True

    ...after your MsgBox line and before the End If

  3. #3
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    Thanks but I am getting debug at :

    Me.AFRNumber = ""

    Run time error '-2147352567 (80020009)':

    The Macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing AFR System from saving the data in the field.

  4. #4
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I'm sorry I didn't think it would be trouble.

  5. #5
    billgyrotech1 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    May 2019
    Posts
    179
    I figured it out thanks:

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-09-2018, 11:11 PM
  2. Replies: 15
    Last Post: 05-12-2016, 02:27 PM
  3. Set the control in focus
    By ScottXe in forum Forms
    Replies: 3
    Last Post: 04-06-2015, 11:31 AM
  4. Tab Control Out of Focus - HELP!
    By derek7467 in forum Forms
    Replies: 2
    Last Post: 02-13-2014, 01:38 PM
  5. Focus on Key Control
    By windwardmi in forum Access
    Replies: 1
    Last Post: 09-07-2011, 04:57 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