Access VBA Code or event to see if user has started to enter a new record

Murray Brown
Murray Brown used Ask the Experts™
Hi

On an Access form what VBA code would I use to check whether the user has begun adding a new record?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use the NewRecord property.
E.g. in the forms before update event you can check to see if its a new record:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  if Me.NewRecord=True then
    'NewRecord so do This
  Else
    'Existing record being updated, do something else
  End If
End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
I also use the NewRecord property, usually in the Form_Current and sometimes in the Form_BeforeUpdate events.
Distinguished Expert 2017
In the Current Event, you can check the Me.NewRecord property and that will tell you that the form is positioned on an empty row  rather than an existing row and so the user is in position to create a new record but hasn't actually started one.

If you only want to trigger code when the user has actually STARTED to create a new record, then use the Form's BeforeInsert event.  It runs ONLY for NEW records, only ONCE,  and it runs IMMEDIATELY after the use enters the first character in ANY control.  This is the correct event to use to populate data that you are copying from the OpenArgs or from some other open form or to populate default values using some code logic.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi. So far I have the following code but it fires as I enter the first field value. I want it to fire as I try to go to the next record


Private Sub Form_BeforeUpdate(Cancel As Integer)

  If Me.NewRecord = True Then
    'NewRecord so do This
    If validate = False Then
        MsgBox "Please fill out all mandatory fields in new ammunition entry!"
    End If
  Else
    'Existing record being updated, do something else
    If validate = False Then
        MsgBox "Please fill out all mandatory fields in existing ammunition entry!"
    End If
  End If
  
End Sub

Function validate()

    If Len(Nz(Me.Headstamp, "")) < 1 Then
       MsgBox "Please fill in headstamp in ammunition list", , "CAVR - ATD (Ammunition)"
       validate = False: Exit Function
    End If
    
    If Me.Calibre.ListIndex = -1 Then
       MsgBox "Please fill in calibre in ammunition list", , "CAVR - ATD (Ammunition)"
       validate = False: Exit Function
    End If
    
       If Nz(Me.Quantity, 0) < 1 Then
        MsgBox "You must enter a quantity of at least one", , "CAVR - ATD"
       validate = False: Exit Function
    End If
    
    validate = True
    
End Function

Open in new window

Distinguished Expert 2017
If the code is firing when you enter data in the first field, there is code in one of that control's events that is forcing the record to be saved.  You need to post all the code for the form and tell us which control is causing the problem.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks for the help
Distinguished Expert 2017
So, does that mean that you found the code that was causing the problem?
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Yes Pat.  I had a lot of extra code allover the place that I removed
Distinguished Expert 2017
When I know I don't need code, I simply remove it to remove the clutter.  While I'm testing though, I sometimes comment out blocks of code as I try other things.  It is important to clean up after the fact.  Keeping old code around doesn't help and it can cause issues.

Glad you worked it out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial