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:
Thanks,
Danian
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:
Code:
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