Results 1 to 6 of 6
  1. #1
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71

    Question Retain Form_Open event programming when Tab into new record


    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.

    Code:
    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

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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

  3. #3
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71
    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

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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:
    Code:
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    dcdimon's Avatar
    dcdimon is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Bradenton, FL
    Posts
    71
    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.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad we could help!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  2. Replies: 3
    Last Post: 09-23-2012, 08:48 PM
  3. Repopulating Table On Form_Open
    By Evilferret in forum Programming
    Replies: 3
    Last Post: 08-21-2012, 02:52 PM
  4. How to retain values selected in a listbox
    By Cedarguy in forum Forms
    Replies: 4
    Last Post: 05-09-2012, 10:05 AM
  5. VBA TypeText - Retain Formatting?
    By alpinegroove in forum Programming
    Replies: 2
    Last Post: 01-02-2012, 04:16 PM

Tags for this Thread

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