Retain Form_Open event programming when Tab into new record

[dcdimon]

I have some vba programming associated with a form in the Form_Open event. It loops through the textbox controls on the form and sets the value equal to something based on certain criteria.



The thing I'm trying to figure out is how to retain that programming when tabbing into a new record. Obviously the form's not opening, or loading, so the event doesn't fire off. What form event should I be looking for? I've included the code below. I've tried putting this into the AfterUpdate event, and it does seem to work, however I can't enter design view when this code is active in that event.

Dim ctl As Control
Dim i As Integer


For Each ctl In Forms!DynamicDataForm.Controls
        If ctl.ControlType = acTextBox Then


            If InStr(ctl.Name, "Control") > 0 And ctl.Value <> "Not Used" Then
             
             ctl.Value = ""


        End If
    
    Next

Any ideas?

Thanks
DD

[John_G]

Hi -

Try the On Current event, together with the NewRecord property of the form. In the OnCurrent event code, you could have:

if me.newrecord then
..
.. do something
..

Endif


HTH

John

[dcdimon]

I've tried the On_Current event, but not with the NewRecord property. That sounds like it should do exactly what I need. I'll try it tomorrow and let you know how it works.

Thanks for the help.

DD

[Missinglinq]

The Form_Load event is never the place for this kind of code because placing it there means that it will only apply to the Record that is initially displayed when the Form opens. The syntax you need would be something like this:

Private Sub Form_Current()

Dim ctl As Control
Dim i As Integer

 If Me.NewRecord Then
   
   For Each ctl In Forms!DynamicDataForm.Controls

      If ctl.ControlType = acTextBox Then

         If InStr(ctl.Name, "Control") > 0 And ctl.Value <> "Not Used" Then
          
           ctl.Value = ""
 
         End If
      
      End If     

   Next
 
 End If

End Sub

Linq ;0)>

[dcdimon]

Linq and John: Thank you!!! That worked perfectly.

I now remember reading about the newrecord property for a form, but I've never used it so it wasn't in my head of ready solutions.

Thanks again.

[Missinglinq]

Glad we could help!

Linq ;0)>