Results 1 to 5 of 5
  1. #1
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218

    Problem w/ calling a private sub from OnChange event for text box search form

    I have a simple search form which uses a text box. In the OnChange event of the text box, I call cmdSearch_Click so that the form is automatically filtered as the user enters data. For an example they could type "1" in Me.txtSearchSerialNum and it will filter the form based on all serial numbers which have the number 1 in them. Then the user could type an extra 1 (so, "11") and it would further filter results. For some reason, it is not working as it should. If I type in "1" it calls cmdSearch_Click and then filters the form as if I have not typed in anything. I've created a msgBox for debugging and it will display nothing. If I were to add another 1 (so, "11"), then it will filter the form for 11. I get the same results no matter what I enter, it seems like it doesn't recognize when there's only one character maybe?

    The reason I used the OnChange event and not after update is I want the form to automatically filter without the user having to click on a button or move the focus off of the tet box.

    So I did some troubleshooting. In the OnChange event of the text box, I added msgBox(Me.txtSerialNum.text) and that displays 1. Then I added the same thing in the private sub cmdSearch_Click, and it displays nothing. So, I thought perhaps I needed to wait before calling cmdSearch from the OnChange event of the text box, so I added some code to wait 5 seconds. Still same results.



    What am I doing wrong?

    Here's my code:

    Code:
    Private Sub txtSearchSerialNum_Change() 'OnChange event is used on text boxes instead of AfterUpdate.    MsgBox (Me.txtSearchSerialNum.Text) 'for debugging
        Call cmdSearch_Click
        Me.txtSearchSerialNum.SetFocus 'Sets focus to allow user to continue to enter new data while cmdSearch_Click is being called
        Me.txtSearchSerialNum.SelStart = Me.txtSearchSerialNum.SelLength 'Brings curser to end of text.
    End Sub
    Private Sub cmdSearch_Click()
        Dim startStr As String
        Dim strFilter As String
        MsgBox (Me.txtSearchSerialNum.Text) 'for debugging, shows the value of the text box.
            If Not IsNullOrEmpty(Me.txtSearchSerialNum) Then
                startStr = IIf(strFilter = "", "", " AND ")
                strFilter = strFilter & startStr & " [SerialNum] like ""*" & Me.txtSearchSerialNum & "*""" 'Text field, doesn't require exact match.
            End If
            MsgBox (strFilter) 'for debugging, shows result of search string
    
    
                If DCount("*", "qryMainInventoryManagement", strFilter) = 0 Then
                    MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                     'Call cmdReset_Click 'Need to create this button
                 End If
         Me.frmSubInventoryManagement.Form.Filter = strFilter
         Me.frmSubInventoryManagement.Form.FilterOn = True
         Me.Form.Filter = strFilter
         Me.Form.FilterOn = True
    End Sub

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the change event does not update the value - this does not happen until you have the update event has fired. the change event fires every time you enter or delete a character

    Suspect you need to put the cmdsearch code in the txtSearchSerialNum change event and have it refer to the text property rather than the value. Note the text property is only available in the control which as the focus - you cannot refer to it from another control

  3. #3
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Thanks for the fast reply!

    You're definitely right. I have been trying to use .text. I even modified my search form to be like this:

    Code:
    If Not IsNullOrEmpty(Me.txtSearchSerialNum) Then            startStr = IIf(strFilter = "", "", " AND ")
                strFilter = strFilter & startStr & " [SerialNum] like ""*" & Me.txtSearchSerialNum.Text & "*""" 'Text field, doesn't require exact match.
            End If
    But it was not working. I noticed MsgBox(strFilter) shows nothing. Perhaps you're right and cannot refer to .text from another control. I really don't want to put the search code in the OnChange event as the search code is longer than what I pasted here, it filters through several text boxes and combo boxes.

    Is there any way for me to force the AfterUpdate event to fire every time there is a change in the text box? Then I could call cmdSearch_Click from AfterUpdate like I do with my combo boxes.

  4. #4
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Figured it out! Don't think I could've done it without your help so thank you.

    All I needed to do was add DoCmd.Save right before I called cmSearch_Click in the OnChange event of the text box. This forced the value to get updated.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Perhaps you're right and cannot refer to .text from another control
    I am! - google it!

    not sure why you want the code on a button and not the change event since clicking the button would not have worked anyway (the button would have the focus), but you now have a workaround

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

Similar Threads

  1. Calling a Private Function from Another Form
    By MintChipMadness in forum Programming
    Replies: 1
    Last Post: 01-07-2013, 12:08 PM
  2. Replies: 2
    Last Post: 10-29-2011, 02:07 AM
  3. Add date OnChange Event - A to Z Walk Through
    By JeffG3209 in forum Programming
    Replies: 7
    Last Post: 08-12-2011, 04:40 PM
  4. Calling fields into VBA Private Sub
    By fullshape in forum Programming
    Replies: 3
    Last Post: 02-18-2011, 09:22 AM
  5. Replies: 1
    Last Post: 06-22-2010, 03:15 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