Results 1 to 10 of 10
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Text box detect change as you type

    I am hoping to detect when a text box is updated with type



    the change event isn't working for me as I type on the text box I have on a form

    I'm hoping that as I type I filter something - in vba

    but the change event only seems to happen once - any way around this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Yeah I found that earlier - but I only wanted to find out how to detect the change. The rest I have a different method for...

  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
    Quote Originally Posted by Ruegen View Post

    ...but the change event only seems to happen once ...
    Then you've either got a corrupted Control...or you're doing something with code that's effecting it. The OnChange event should fire with each character entered into the Textbox.

    Can we see the code you're trying to run off of the OnChange event?

    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
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by Missinglinq View Post
    Then you've either got a corrupted Control...or you're doing something with code that's effecting it. The OnChange event should fire with each character entered into the Textbox.

    Can we see the code you're trying to run off of the OnChange event?

    Linq ;0)>

    oh... maybe that's it.


    I'll have to get to my other office and I will post the code.

  6. #6
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    so the form's code is

    Code:
    Public objS As School
    
    
    Private Sub Form_Load()
    
    
    Set objS = New School
    
    
    Me.Text3 = "Search using a school's ID"
    
    
    End Sub
    
    
    Private Sub Text3_Change()
        
        Debug.Print "render " & Me.Text3
        
        If IsNull(Me.Text3) = False Then
            If IsNumeric(Me.Text3) = True Then
                Dim lng1 As Long
                lng1 = Nz(Me.Text3.value, 0)
                Me.txtName = Nz(objS.Name(lng1), "")
            End If
        End If
        
    End Sub
    
    
    
    
    
    
    Private Sub Text3_GotFocus()
    
    
    If Me.Text3 = "Search using a school's ID" Then
    Me.Text3.Format = "General Number"
    Me.Text3 = ""
    End If
    
    
    End Sub
    
    
    Private Sub Text3_LostFocus()
    If Me.Text3 = "" Then
    Me.Text3.Format = "ABC"
    Me.Text3 = "Search using a school's ID"
    End If
    End Sub
    The class object I made is

    Code:
    Option Compare Database
    
    
    Dim rs As DAO.Recordset
    Dim missingID As String
    
    
    
    
    
    
    Private Function schoolLoad(SchoolID As Long) As Boolean
        
        missingID = "This record ID was not found in the databse"
         
        If IsNumeric(SchoolID) = False Then
            schoolLoad = False
            Exit Function
        End If
        
        'find particular school by the schools own table ID
        Dim db As DAO.Database
        
        Dim strSQL As String
        
        strSQL = "SELECT * FROM tblSchools WHERE NewSchoolsID = " & SchoolID & " ;"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSQL)
        
        schoolLoad = True
        
        'check to see if there is a record in database table, false if missing
        If rs.RecordCount = 0 Then
            schoolLoad = False
            Exit Function
        End If
        
    End Function
    
    
    Private Function FieldValueFrom(FieldName As String) As Variant
        FieldValueFrom = Nz(rs.Fields(FieldName).value, "")
    End Function
    
    
    Public Function Name(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Name = missingID
            Exit Function
        End If
        
        Name = FieldValueFrom("SchoolName")
    End Function
    
    
    Public Function Suburb(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Suburb = missingID
            Exit Function
        End If
        Suburb = FieldValueFrom("SchoolSuburb")
    End Function
    
    
    Public Function Phone(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Phone = missingID
            Exit Function
        End If
        Phone = FieldValueFrom("SchoolPhone")
    End Function
    
    
    Public Function Email(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Email = missingID
            Exit Function
        End If
        Email = FieldValueFrom("SchoolEmail")
    End Function
    
    
    Public Function CallBackDate(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Exit Function
        End If
        CallBackDate = FieldValueFrom("CallBackDate")
    End Function
    
    
    Public Function ID(SchoolID As Long) As Variant
        If schoolLoad(SchoolID) = False Then
            Exit Function
        End If
        ID = FieldValueFrom("NewSchoolsID")
    End Function
    
    
    Public Function change_Name(SchoolID As Long, NewName As String) As Boolean
        If schoolLoad(SchoolID) = False Then
            change_Name = False
            Exit Function
        End If
        
        If IsValidEmail(NewName) Then
            change_Name = True
            
            rs.Edit
            rs.Fields("SchoolName").value = NewName
            rs.update
            
        Else
            change_Name = False
        End If
        
        
    End Function
    
    
    Public Function change_Email(SchoolID As Long, NewEmail As String) As Boolean
        If schoolLoad(SchoolID) = False Then
            change_Email = False
            Exit Function
        End If
        
        If IsValidEmail(NewEmail) Then
            change_Email = True
            
            rs.Edit
            rs.Fields("SchoolEmail").value = NewEmail
            rs.update
            
        Else
            change_Email = False
        End If
        
    End Function
    The concept is to have a variable which gets searched - and refreshed when required. So everything in done in memory.

    I deleted both controls - still the same issue.

  7. #7
    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
    Ruegen, I'm going to give you a short version, then a long version, of what I think is misleading you here, vis-à-vis the OnChange event; hopefully the short version will point you in the right direction, but you might still want to look at the more detailed, long version.

    First, you need to understand something about the OnChange event and data in the Control while this event is firing; I think this is probably the root of your problem:

    Me.Text3

    is the same thing as

    Me.Text3.Value

    because Value is the Default Property for Textboxes and doesn't have to be explicitly added to the Control's name. Also, the Value Property refers to the data that is assigned, via code, to a Textbox, as in

    Me.Text3 = "Search using a school's ID"

    or

    Me.Text3 = ""

    It also refers to data that has been entered into a Textbox when you exit that Textbox, which is to say when the data is committed/saved to the Control. The Value Property does not refer to data currently being entered into a Textbox while the Textbox still has the Focus!

    While the Textbox has Focus and data is actually being entered into it, that data is held in the .Text Property. So, if you write code that goes

    Me.Text3 = "Search using a school's ID"

    or

    Me.Text3 = ""

    Me.Text3.Value is Search using a school's ID or "", respectively.

    If the user then tabs into the Text3 Control, and then types in

    ABCDEFG

    and leaves the cursor in the Control, as you are doing when you're using the OnChange event,

    Me.Text3.Text is ABCDEFG, but

    but

    Me.Text3.Value remains Search using a school's ID or "", respectively!


    Now for the long version, which hopefully will explain, in detail, why your code isn't working as you want it to.

    The order of events firing, here, is

    • Form_Load
    • Text3_GotFocus
    • Text3_Change
    • Text3_LostFocus

    In the Form_Load event you're populating Text3, which is to say Me.Text3.Value with the string Search using a school's ID:

    Me.Text3 = "Search using a school's ID"

    Next, in the GotFocus event (the first event to fire that is associated with the Text3 Textbox) you're testing to see if the Value in Me.Text3.Value is "Search using a school's ID"

    If Me.Text3 = "Search using a school's ID" Then

    which it is, of course, and always will be, because you just set it to that in the Form_Load event! So you absolutely know that the code, below, is going to run, every time you enter the Text3 Control

    Me.Text3.Format = "General Number"
    Me.Text3 = ""


    and that Me.Text3.Value is now a Zero Length String ("").

    So (I'm guessing) the intent of your code

    Debug.Print "render " & Me.Text3.Value

    is to print, in the code module's Immediate Window,

    "render " & Me.Text3

    but Access is only going to print

    render

    each time you enter a character, because the Value of Me.Text3 is now, as I said, above, a Zero Length String!

    If your aim is to print

    render

    and the character(s) in Text3, you'd have to use

    Debug.Print "render " & Me.Text3.Text

    After this, you run a test

    If IsNull(Me.Text3.Value) = False Then

    which is always going to be True because

    IsNull(Me.Text3.Value) = False

    is a true statement; once again, the Value of Me.Text3 is a Zero Length String...it is not Null!

    A Zero Length String ("") is not the same as a Null!

    And so, you subsequent code

    Dim lng1 As Long
    lng1 = Nz(Me.Text3.value, 0)
    Me.txtName = Nz(objS.Name(lng1), "")


    will always run! To be honest, I don't understand what you're doing with this code, especially the class object you created (never had a need to do that in Access/VBA) but the line

    lng1 = Nz(Me.Text3.value, 0)

    will never assign a Zero to lng1, because...you guessed it, Me.Text3.Value is still a Zero Length String...it is not Null! And because you've dimmed lng1 as a number (i.e. Long) and then are trying to assign it a Zero Length String, you'll get a Run time Error 13: Type Mismatch.

    I know this has been a long, shaggy dog story of an explanation, and there might be other things involved, as well, but I suspect it covers what is hurting you, here. Go over it, and try making changes, as needed, and let us know how you're doing.

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

    All posts/responses based on Access 2003/2007

  8. #8
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    So I should be testing the length of .text

    len(me.text3.text)

    and checking to see if that has changed?

    and if it has, then compare it to the last entry (stored in a public var)

    so
    if me.text3.text <> varPreviousText then
    'search
    end if

  9. #9
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Hey that works beautifully - and it works very quickly which is what I like about what I have done too

    cheers and many thanks Missinglinq for taking the time to explain it too (that helped immensely)

    much appreciated!

  10. #10
    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: 2
    Last Post: 02-10-2015, 02:12 PM
  2. How Do I Change Data Type
    By LeadTechIG in forum Database Design
    Replies: 9
    Last Post: 01-07-2015, 03:05 PM
  3. Replies: 9
    Last Post: 11-14-2014, 06:11 PM
  4. Replies: 2
    Last Post: 10-03-2014, 10:07 AM
  5. Change chart type
    By jtan in forum Reports
    Replies: 1
    Last Post: 11-12-2013, 02:24 AM

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