NOT VB .... VBA
-
Hello... I ran into an interesting situation yesterday when writing some code behind a form in MS Access 2003. On change for a textbox I wanted to search a list of parts so as the user typed in the part number the code would search down the list and find the first match. My problem was it took a little longer then what I expected. I would like to try to make this a multi-threaded app where I do the search in another thread and return control to the screen. Would I need to use C# or VB.NET and create an add-in or is there a way to create new threads? Thanks Will
-
Hello... I ran into an interesting situation yesterday when writing some code behind a form in MS Access 2003. On change for a textbox I wanted to search a list of parts so as the user typed in the part number the code would search down the list and find the first match. My problem was it took a little longer then what I expected. I would like to try to make this a multi-threaded app where I do the search in another thread and return control to the screen. Would I need to use C# or VB.NET and create an add-in or is there a way to create new threads? Thanks Will
The VB6 IDE cannot handle debugging a multithreaded app. If you stop the execution ANYWHERE in your code while another thread is running, you'll instantly crash the IDE. I'm not saying that it's can't be done, it's just very difficult to do. A speed improvement to your search would probably be in indexing your primary search field. Another one is if your using something like
SELECT * FROM table WHERE something LIKE 'som%'
The '*' represents returning all fields in all the records that match the search criteria. Don't do this. Narrow it down to only the fields you need to return.
SELECT field1, field2 FROM table WHERE something LIKE 'som%'
RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
The VB6 IDE cannot handle debugging a multithreaded app. If you stop the execution ANYWHERE in your code while another thread is running, you'll instantly crash the IDE. I'm not saying that it's can't be done, it's just very difficult to do. A speed improvement to your search would probably be in indexing your primary search field. Another one is if your using something like
SELECT * FROM table WHERE something LIKE 'som%'
The '*' represents returning all fields in all the records that match the search criteria. Don't do this. Narrow it down to only the fields you need to return.
SELECT field1, field2 FROM table WHERE something LIKE 'som%'
RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Thank you for the reply. Couple things: 1) The view that I an using is linked from SQL Server and yes the table has been normalized 2) I am searching a listbox that has been populated and not hitting the view at all Let me post some code to clear this up a little TEXTBOX EVENT:
Private Sub txtPartSearch_Change() ' ' Created by William O'Malley ' I would like to user to be able to type in part of a Part Number ' and have the list activly search for a match baised on what is typed in ' ' Ex: ' If I type in 12 then go to the first match ' if I type in 1234 then go to the first match ' ' This will happen as they type ' Dim sLength As Integer 'how long is the string Dim sSearch As String 'what am I looking forgy Dim iListCount As Long ' ' sLength = Len(Me.txtPartSearch.Text) sSearch = Me.txtPartSearch.Text iListCount = Me.lstPartNumbers.ListCount ' Me.lblSearchingOf.Caption = Me.lstPartNumbers.ListCount ' findInListBox sLength, sSearch, iListCount ' End SubFUNCTION USED TO SEARCH:Function findInListBox(ByVal sLength As Integer, ByVal sSearch As String, ByVal NumberOfRowsToSearch As Long) ' ' This function will be used to perform the search on ' the list box ' Dim i As Long Dim sStringToCheck As String ' For i = 0 To NumberOfRowsToSearch ' Me.lblSearching.Caption = CStr(i + 1) sStringToCheck = Left(Me.lstPartNumbers.Column(0, i), sLength) ' If (sStringToCheck = sSearch) Then ' Me.lstPartNumbers.Selected(i) = True ' Exit For ' End If ' Next i ' End FunctionAlthough this only takes a few seconds I want it faster. So I figured that I could call my findInListBox function in a new thread that and return control to the screen. So the user would be able to continue typing as fast as they want with no problem because the search would be in the background. Thank you again for the reply. William O'Malley -- modified at 9:00 Friday 9th September, 2005 -
Thank you for the reply. Couple things: 1) The view that I an using is linked from SQL Server and yes the table has been normalized 2) I am searching a listbox that has been populated and not hitting the view at all Let me post some code to clear this up a little TEXTBOX EVENT:
Private Sub txtPartSearch_Change() ' ' Created by William O'Malley ' I would like to user to be able to type in part of a Part Number ' and have the list activly search for a match baised on what is typed in ' ' Ex: ' If I type in 12 then go to the first match ' if I type in 1234 then go to the first match ' ' This will happen as they type ' Dim sLength As Integer 'how long is the string Dim sSearch As String 'what am I looking forgy Dim iListCount As Long ' ' sLength = Len(Me.txtPartSearch.Text) sSearch = Me.txtPartSearch.Text iListCount = Me.lstPartNumbers.ListCount ' Me.lblSearchingOf.Caption = Me.lstPartNumbers.ListCount ' findInListBox sLength, sSearch, iListCount ' End SubFUNCTION USED TO SEARCH:Function findInListBox(ByVal sLength As Integer, ByVal sSearch As String, ByVal NumberOfRowsToSearch As Long) ' ' This function will be used to perform the search on ' the list box ' Dim i As Long Dim sStringToCheck As String ' For i = 0 To NumberOfRowsToSearch ' Me.lblSearching.Caption = CStr(i + 1) sStringToCheck = Left(Me.lstPartNumbers.Column(0, i), sLength) ' If (sStringToCheck = sSearch) Then ' Me.lstPartNumbers.Selected(i) = True ' Exit For ' End If ' Next i ' End FunctionAlthough this only takes a few seconds I want it faster. So I figured that I could call my findInListBox function in a new thread that and return control to the screen. So the user would be able to continue typing as fast as they want with no problem because the search would be in the background. Thank you again for the reply. William O'Malley -- modified at 9:00 Friday 9th September, 2005OMalleyW wrote: 2) I am searching a listbox that has been populated and not hitting the view at all OK. You didn't mention this before... Instead of do string comparisons, my only suggestion would be to build an index tree of the data in the listbox. When you hit a key, you're search only returns items that start with that letter. With the next key, you only search the items returned from the first search. When the third key is his, you only search the items from the second search, and so on, ... Check out this[^] series of articles on MSDN for some theory and examples. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
OMalleyW wrote: 2) I am searching a listbox that has been populated and not hitting the view at all OK. You didn't mention this before... Instead of do string comparisons, my only suggestion would be to build an index tree of the data in the listbox. When you hit a key, you're search only returns items that start with that letter. With the next key, you only search the items returned from the first search. When the third key is his, you only search the items from the second search, and so on, ... Check out this[^] series of articles on MSDN for some theory and examples. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome