Textbox curser move in Office 2007 (1 Viewer)

[danian]

All,
I am currently running Office XP and have a database in Access. The DB has a form that contains a textbox and a listbox. When you enter text in the textbox it automatically filters the listbox to find any data that contains the letters you type, letter by letter. This is all done using VBA and was written for me many moons ago.

However, I have now decided that I want to upgrade to Office 2007 and found that, the form no longer works correctly. When you start to type in the textbox it accepts a letter and then the curser moves out of the textbox, so you then have to click back in the textbox and type the next letter. This then continues until you have finished typing, but the listbox continues to filter the data as required.

Please can you advise why the curser moves from the textbox each time a letter is typed when using Office 2007, but works perfectly using Office XP.

I have attached the code below from the whole form:

Option Compare Database
Option Explicit

Dim LastKey As Integer

Private Sub cmdClear_Click()
'   CLEAR THE SEARCH FIELD AND RESET THE SEARCH

    lstClients.SetFocus
    lstClients.RowSource = "SELECT ID, Name, Surname, Address, Postcode FROM tblclients;"
    lstClients.Requery
    txtSearch.SetFocus
    txtSearch.Text = ""
    
End Sub

Private Sub cmdNew_Click()
'  ADD A RECORD

    Dim stDocName As String
    
    stDocName = "frmClientDetails"
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog
    lstClients.Requery

End Sub

Private Sub cmdClose_Click()
    'CLOSE THE FORM
    DoCmd.Close
End Sub
    
Private Sub Image15_Click()
    'CLEAR THE SEARCH FIELD AND RESET THE SEARCH
    lstClients.SetFocus
    lstClients.RowSource = "SELECT ID, Name, Surname, Address, Postcode FROM tblclients"
    lstClients.Requery
    txtSearch.SetFocus
    txtSearch.Text = ""
End Sub

Private Sub Image16_Click()
'  ADD A RECORD

    Dim stDocName As String
    
    stDocName = "frmClientDetails"
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog
    lstClients.Requery

End Sub

Private Sub Image17_Click()
'  CLOSE THE FORM

    DoCmd.Close

End Sub

Private Sub imgClear_Click()
'   CLEAR THE SEARCH FIELD AND RESET THE SEARCH

    lstClients.SetFocus
    lstClients.RowSource = "SELECT ID, Name, Surname, Address, Postcode FROM tblclients"
    lstClients.Requery
    txtSearch.SetFocus
    txtSearch.Text = ""
End Sub

Private Sub imgExit_Click()
    'Close Form
    
    DoCmd.Close
End Sub


Private Sub imgNewClient_Click()
    'Add New Client
    Dim stDocName       As String
    Dim stLinkCriteria  As String

    stDocName = "frmNewClient"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    'Forms(stDocName).Caption = "Add Client"
    'Forms(stDocName).DataEntry = True
    
    lstClients.Requery
End Sub

Private Sub lstClients_DblClick(Cancel As Integer)
'OPEN THE SELECTED RECORD
        '  Sanity check for open
        If lstClients.ListIndex < 0 Then
            Exit Sub
        End If
                
        'Original Code
        DoCmd.OpenForm "frmClientDetails", acNormal, , "[tblClients.ID]=" & lstClients.Column(0), acFormEdit
        Forms("frmClientDetails").Caption = lstClients.Column(2)
        Forms("frmClientDetails").AllowAdditions = False
End Sub


Private Sub lstClients_KeyDown(KeyCode As Integer, Shift As Integer)
'   OPEN THE SELECTED RECORD OR MOVE BACK TO SEARCH FIELD
    
    '  If Return Key is hit, Open the correct form
    If KeyCode = vbKeyReturn And Shift = 0 Then
        
        DoCmd.OpenForm "frmClientDetails", acNormal, , "tblClients.ID=" & lstClients.Column(0), acFormEdit, acDialog
        Forms(FormName).AllowAdditions = False
    End If
    
    '   If we are at the top of the list, go back to the search box
    If KeyCode = vbKeyUp Then
        If lstClients.ListIndex = 0 Then
            lstClients.ListIndex = -1
            txtSearch.SetFocus
        End If
    End If
End Sub


Private Sub txtSearch_Change()
'  SEARCH FOR THE NAME

    If LastKey = Asc(" ") Then
        Exit Sub
    End If
        
    lstClients.SetFocus
    lstClients.RowSource = "SELECT ID, Name, Surname, Address, Postcode FROM tblclients WHERE (Name LIKE '*" & txtSearch & "*') OR (Surname LIKE '*" & txtSearch & "*') OR (Address LIKE '*" & txtSearch & "*') OR (Postcode LIKE '*" & txtSearch & "*');"
    lstClients.Requery
    txtSearch.SetFocus
    txtSearch.SelStart = Len(txtSearch.Text)
    txtSearch.SelLength = 0

End Sub

Private Sub txtSearch_GotFocus()
'  SELECT ALL OF THE TEXT

    txtSearch.SelStart = 0
    txtSearch.SelLength = Len(txtSearch.Text)

End Sub

Private Sub txtSearch_KeyDown(KeyCode As Integer, Shift As Integer)
'   TRAP THE DOWN CURSOR KEY
    
    If KeyCode = vbKeyDown Then
        If lstClients.ListCount > 0 Then
            lstClients.SetFocus
            lstClients.ListIndex = 0
        End If
    End If
    
End Sub

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
'   RECORD THE LAST KEY PRESSED FOR THE CHANGE EVENT
    
    LastKey = KeyAscii
    
End Sub

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmClientDetails"
    
    stLinkCriteria = "[tblClients.ID]=" & Me![lstClients]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command22_Click:
    Exit Sub

Err_Command22_Click:
    MsgBox Err.Description
    Resume Exit_Command22_Click
    
End Sub
Private Sub Command28_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmClientDetails"
    
    stLinkCriteria = "[tblClients.ID]=" & lstClients.Column(0)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Thanks,
Danian

 

[DCrake]

Try this:

Private Sub TxtCriteria_Change()
'Purpose: to refresh the contents of the list box based on the text entered into the TxtCriteria control
If Me.CmdReset.Enabled = False Then
    Me.CmdReset.Enabled = True
End If
On Error Resume Next
Dim StrSql              As String 'SQL statement for the record source
Dim sText               As String 'Contents of the criteria control
sText = Trim(Me.TxtCriteria.Text)

StrSql = "SELECT fldPracticeCode, fldPracticeName, fldAddressLine1, fldAddressLine2, fldAddressLine3, fldAddressLine4, fldPostcode, fldPCT FROM TblPracticeMasterfile "

    
         


'Is there any text to test?
    If Not sText = "" And IsDelOrBack = False Then
            
            StrSql = StrSql & "WHERE " & Me.CboFieldNames.Column(1) & " Like '" & sText & "*' ORDER BY " & Me.CboFieldNames.Column(1) & ";"
            
            
            'Refresh the rowsource with the new SQL
            Me.LstPatients.RowSource = StrSql
                
    End If
    'Requery the list box to show results
    Me.LstPatients.Requery

End Sub

Remember to substitute your table and field names and control names in the example shown above.

CodeMaster:

 

[danian]

DCrake,

Many thanks for the help.

I have tried to edit the code you gave but unforyunitly i am unable to get it working.

Would somebody kindley help me convert my code, so i can try the example given.

I am very new to vba

Thanks,
D

 

[danian]

Can anyone help?

 

[jal]

I don't see why much code conversion is necessary. I think the last poster was primarily showing you the need to put your code in the TextChanged event (i.e. the Change event) instead of a click event. Aside from that, your code should pretty much remain the same, I would think.

If you call SetFocus at the end of the textbox's Changed event, I am guessing that the cursor will return to the textbox automatically. I'm not sure.

 

[danian]

I don't see why much code conversion is necessary. I think the last poster was primarily showing you the need to put your code in the TextChanged event (i.e. the Change event) instead of a click event. Aside from that, your code should pretty much remain the same, I would think.

If you call SetFocus at the end of the textbox's Changed event, I am guessing that the cursor will return to the textbox automatically. I'm not sure.

Thanks,

But the command is on Change:

Private Sub txtSearch_Change()
'  SEARCH FOR THE NAME

    If LastKey = Asc(" ") Then
        Exit Sub
    End If
        
    lstClients.SetFocus
    lstClients.RowSource = "SELECT ID, Name, Surname, Address, Postcode FROM tblclients WHERE (Name LIKE '*" & txtSearch & "*') OR (Surname LIKE '*" & txtSearch & "*') OR (Address LIKE '*" & txtSearch & "*') OR (Postcode LIKE '*" & txtSearch & "*');"
    lstClients.Requery
    txtSearch.SetFocus
    txtSearch.SelStart = Len(txtSearch.Text)
    txtSearch.SelLength = 0

End Sub

But still the cursor moves from the textbox.

Danian

 

[jal]

My appologies. I didn't read your code closely enough. If setFocus isn't working, maybe you can find a way to trick it into working. To hazard a wild guess, maybe try this:

Dim storage as string
storage = txtSearch.Value
txtSearch.Value = ""
txtSearch.Value = storage
txtSearch.SetFocus

I doubt it will work, but anything is worth a try, at this point.

 

[jal]

But if you use that method, you will also need a boolean flag to prevent "looping" (in other words my code reexecutes the Change event, possibly interminably).

If stillProcessing exit sub
stillProcessing = true
......code here
still processing = false

prevents looping (global variable initially set to false)