HELP String Parsing Msaccess

**Stewart Ross** · Feb 14 '08, 05:14 PM

Originally posted by cephal0n

Hi there! I've been racking my brains out for 4 days and still not getting why is msaccess stubborn about string parsing, first I have a table named tblItems, this table has columns: ProductItem, ItemID. Now I want to search and count my items found and produce the result in a textbox and a label control called: txtItemResult and lblCount so I have the code

Public Sub StringFinder()
Dim conn1 As ADODB.Connectio n
Dim rsShowTable As ADODB.Recordset
Dim iResult as Integer
Dim iCounter as Integer
DIm i as String

Set conn1 = New ADODB.Connectio n
conn1.Provider = "Microsoft.Jet. OLEDB.4.0"
conn1.Open "C:\Apps\dataPM C.mdb"

Set rsShowTable = New ADODB.Recordset
rsShowTable.Ope n "tblItems", conn1, adOpenDynamic, adLockOptimisti c, adCmdTable

i= "NYL"

iResult = StrComp(txtItem sResult.Value, i)
Select Case iResult
Case 1
MsgBox "the first string is greater than the second"

'Iterate through each character

startPosition = InStr(txtActivi ty.Value, 1)
iCount = iCount + 1
lblCount.Captio n = iCount
Me.txtItemsResu lt.Value = rsShowTable!("P roductItem")
Case Else
MsgBox "One or more strings are null"
End Select
End sub

this produces unfamiliar errors for me and I appologize for the msgbox, I just want to see if I compared the right string. I know, that I should be using sql count(*) here, to be honestI should have but it dosent give what I want or my understanding is the problem (which is usually the case). The output should be like:

ProductItems
------------
Nylon
Nylon Cable
Nylon hose
Nylon string

there are: 4 items found

pls....pls....p ls, anyone who can put me in the right direction would very appreciated.

Not sure what the object of all this is, but it sure looks a complex way to do things! SQL has the "like " operator to match substrings. To see how it works, without using code, add the base table to a query then add the ProductItem field to the query. Put "like [Item name or part of?]" in the ProductItem field criteria. This will pop up a parameter box when you run the query, you can then type NYL or anything else, and whatever matches there are will be shown in the query.

SQL for this is along the lines of

Code:

Select [ProductItems] from [name of your table] Having ([ProductItems] like [Item name or part of?]);

Your code has a number of errors: InStr is intended to find a match between two strings; you are supplying only one in the code shown. You don't need to iterate through each character. Not sure what icount is doing, but it is unnecessary unless you are searching for multiple occurrences of the substring in a main string. If you were, you would need to put icount in place of the 1 in the InStr.

If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure!

-Stewart

**cephal0n** · Feb 15 '08, 05:13 AM

Originally posted by Stewart Ross Inverness

Not sure what the object of all this is, but it sure looks a complex way to do things! SQL has the "like " operator to match substrings. To see how it works, without using code, add the base table to a query then add the ProductItem field to the query. Put "like [Item name or part of?]" in the ProductItem field criteria. This will pop up a parameter box when you run the query, you can then type NYL or anything else, and whatever matches there are will be shown in the query.

SQL for this is along the lines of

Code:

Select [ProductItems] from [name of your table] Having ([ProductItems] like [Item name or part of?]);

Your code has a number of errors: InStr is intended to find a match between two strings; you are supplying only one in the code shown. You don't need to iterate through each character. Not sure what icount is doing, but it is unnecessary unless you are searching for multiple occurrences of the substring in a main string. If you were, you would need to put icount in place of the 1 in the InStr.

If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure!

-Stewart

-------------------------------------------------------------------------------------------------------------
HI Stewart!
thank you for responding to my post. I apologize for providing a confusing code and hoping this second time I get it right. I hope I under stand youre request:
If you could provide more detail about what it is you are really wanting to do, and the structure of your base queries/tables, I would be happy to help more. No need for code in this case, I am sure! Let me start over, as I mentioned earlier ihave a table named: tblItems
with a field columns: itemID, ProductItem, ProdOrder, Comments

this is the structure:

Code:

itemID  ProductItems         ProdOrder  Comments
------  ------------         ---------  ---------------
01      Nylon Belt           03         ok
02      Nylon Hose           03         ok
03      Nylon String         03         ok
04      Tarlon               07         ok
05      Glass Field Nylon    03
06      Plastic              05
07      Plastic              05

***Im sorry if the table seemed mixed up, its the best I can do *******
** Edit ** Tidied up layout using [ CODE ] tags.
each field has been populated accrordingly, including my field ProductItem with Nylon as its listed items. now for the big brain crunching!

problem 1: I have a ProductItem and ProOrder sync with each other, its like where theres cheese theres a mouse behind it (forgive the analogy).

problem 2: My ProdOrder has repeated number with a different ProductItem description, that will have an effect in my searching later.

Now I planned my supper design for my table. my objective here is provide a search box that when a user typed in the string "NYL" it will provide the necessary ouput, so that is why I created a function StringFinder in VB, to find the specific string.
hence my code:

Code:

i="NYL"

iResult = StrComp(txtItemsResult.Value, i)
Select Case iResult
Case 1
MsgBox "the first string is greater than the second"

'Iterate through each character

startPosition = InStr(txtActivity.Value, 1)
iCount = iCount + 1
lblCount.Caption = iCount
Me.txtItemsResult.Value = rsShowTable!("ProductItem") 
Case Else

Of course I planed to put an IF Else condition there but I want to try if msaccess
would like my code first.

This may seemed over the top, but this is the only way I can think of, I know that sql provides a count and like functions but when I tried it, the output only provides a particular Item. ex. LIKE "NYL*" in access, it only shows all my nylon items. I really dont want my search to eliminate other items since they share ProdOrder. assuming I am successful in creating my search, the output would be: there are: 3 Nylon and 1 Glass Field Nylon found

I hope this does not complicate it more, coz I really need help here. Thanks gain!

**Stewart Ross** · Feb 15 '08, 09:27 AM

Hi Cephalon. If you use an Access form to display your records you can add an unbound text box for a user to enter words to match. I have provided below a general search routine which checks whether or not there are any matches before applying a filter to the form. It is the filter that uses the 'like' clause I mentioned in my previous reply.

The code itself is for the AfterUpdate event of your unbound string match text box. In this version I refer to TitleText, the control in the form which this was taken from; substitute the name of the actual control for TitleText in the SearchFor line. I have changed the 'like' clause to refer to field ProdItems in your table.

Code:

Private Sub TitleText_AfterUpdate()
    Dim Recs As DAO.Recordset, BookMark As Variant
    Dim IsFound As Boolean, TheForm As Form
    Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
    If Not IsNull(Me.TitleText) Then
        SearchFor = Me.TitleText
        SearchString = "[ProdItems] like '*" & SearchFor & "*'"
        Me.FilterOn = False
        Set Recs = EventForm.RecordsetClone
        If Recs.RecordCount > 0 Then
           Recs.MoveLast
           Recs.FindFirst SearchString
           If Recs.NoMatch Then
                Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
            Else
                Me.Filter = SearchString
                Me.FilterOn = True
            End If
        Else
            Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
        End If
    End If
End Sub

Try this out for your search and see how you get on.

Remember, the active lines that do the actual matching in the code are very few - the rest is simply checking to see that there is something to match. If I extract the matching bits these are just

Code:

      SearchFor = Me.TitleText
      SearchString = "[ProdItems] like '*" & SearchFor & "*'"
      Me.Filter = SearchString
      Me.FilterOn = True

I have used the wildcard "*" on each side of the searchstring so it will return matching substrings from anywhere in the main string.

Hope you find some of this useful.

Cheers

Stewart

**cephal0n** · Feb 18 '08, 03:48 AM

Originally posted by Stewart Ross Inverness

Hi Cephalon. If you use an Access form to display your records you can add an unbound text box for a user to enter words to match. I have provided below a general search routine which checks whether or not there are any matches before applying a filter to the form. It is the filter that uses the 'like' clause I mentioned in my previous reply.

The code itself is for the AfterUpdate event of your unbound string match text box. In this version I refer to TitleText, the control in the form which this was taken from; substitute the name of the actual control for TitleText in the SearchFor line. I have changed the 'like' clause to refer to field ProdItems in your table.

Code:

Private Sub TitleText_AfterUpdate()
    Dim Recs As DAO.Recordset, BookMark As Variant
    Dim IsFound As Boolean, TheForm As Form
    Dim SearchString As String, Response As VbMsgBoxResult, SearchFor As String
    If Not IsNull(Me.TitleText) Then
        SearchFor = Me.TitleText
        SearchString = "[ProdItems] like '*" & SearchFor & "*'"
        Me.FilterOn = False
        Set Recs = EventForm.RecordsetClone
        If Recs.RecordCount > 0 Then
           Recs.MoveLast
           Recs.FindFirst SearchString
           If Recs.NoMatch Then
                Response = MsgBox("No match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
            Else
                Me.Filter = SearchString
                Me.FilterOn = True
            End If
        Else
            Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
        End If
    End If
End Sub

Try this out for your search and see how you get on.

Remember, the active lines that do the actual matching in the code are very few - the rest is simply checking to see that there is something to match. If I extract the matching bits these are just

Code:

      SearchFor = Me.TitleText
      SearchString = "[ProdItems] like '*" & SearchFor & "*'"
      Me.Filter = SearchString
      Me.FilterOn = True

I have used the wildcard "*" on each side of the searchstring so it will return matching substrings from anywhere in the main string.

Hope you find some of this useful.

Cheers

Stewart

------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
HI Stewart!

I appologize for the delay replying to you, Im currently studying
the code using DAO and thank you very much for the explanation and sample code you provided,howeve r I have a few question so I hope you dont mind, you said earlier that the code is for the AfterUpdate event for the textbox. Is this hit "EnterKey" after typing inside the string finder textbox, coz I add a cmdFind control within the code:

Code:

with cmdFind
If Not IsNull(Me.txtTitle) Then
        'Me.TextTitle = Recs.Fields(ProductItems).Value
        SearchFor = Me.txtTitle
        SearchString = "[ProductItems] like '*" & SearchFor & "*'"
        Me.FilterOn = False
    Set Recs = Form.RecordsetClone
    
    'theForm.RecordsetClone 'EventForm.RecordsetClone
    
            If Recs.RecordCount > 0 Then
                Recs.MoveLast
                Recs.FindFirst SearchString
                    If Recs.NoMatch Then
                    With cmdFind
                    Response = MsgBox("No Match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
                    End With
                    Else
                        Me.Filter = SearchString
                        Me.FilterOn = True
                    End If
                Else
                    Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
            End If
    End If 
End With

and I seemed to get a cold response from it, I also tried hiting the enter key and no response and I think I'm quite confused with executing the vbExclamation, vbOkOnly functions.How about Bookmark, TheForm, how does this things comes into place.pls! provide a few explanation, this would be very helpful. Thank you again!

**Stewart Ross** · Feb 18 '08, 09:28 AM

Originally posted by cephal0n

------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
HI Stewart!

I appologize for the delay replying to you, Im currently studying
the code using DAO and thank you very much for the explanation and sample code you provided,howeve r I have a few question so I hope you dont mind, you said earlier that the code is for the AfterUpdate event for the textbox. Is this hit "EnterKey" after typing inside the string finder textbox, coz I add a cmdFind control within the code:

with cmdFind
If Not IsNull(Me.txtTi tle) Then
'Me.TextTitle = Recs.Fields(Pro ductItems).Valu e
SearchFor = Me.txtTitle
SearchString = "[ProductItems] like '*" & SearchFor & "*'"
Me.FilterOn = False
Set Recs = Form.RecordsetC lone

'theForm.Record setClone 'EventForm.Reco rdsetClone

If Recs.RecordCoun t > 0 Then
Recs.MoveLast
Recs.FindFirst SearchString
If Recs.NoMatch Then
With cmdFind
Response = MsgBox("No Match within products for '" & SearchFor & "'", vbExclamation + vbOKOnly, "No match")
End With
Else
Me.Filter = SearchString
Me.FilterOn = True
End If
Else
Response = MsgBox("There are no products currently recorded", vbExclamation + vbOKOnly)
End If
End If
End With

and I seemed to get a cold response from it, I also tried hiting the enter key and no response and I think I'm quite confused with executing the vbExclamation, vbOkOnly functions.How about Bookmark, TheForm, how does this things comes into place.pls! provide a few explanation, this would be very helpful. Thank you again!

Hi Cephalon. The AfterUpdate event of the textbox is indeed triggered by an enterkey press (or by data entry followed by mousing to another control). I'm not sure what CmdFind is - you are using a With statement to refer to it in two places, but don't appear to be referring to any of CmdFind's properties thereafter.

To add the AfterUpdate code, view the set of Properties of your StringFinder control (double-click on the control in Design View to bring up the properties tabs if not already showing), select the Event tab and you will see the list of events - including Before Update, After Update, and several others.

Right-click on the After Update event line and choose Builder from the menu. This places a sub procedure header and footer in the form's code, ready for you to add the body of the After Update routine. You should then copy the routine as provided in my previous reply, with any changes for the differing control names.

vbExclamation and vbOkOnly are actually system constants which give names to integer values and are simply more readable than referring to numbers. They are associated with the Message Box function.

Bookmark is an internal property that is used to match records between copied recordsets. As I mentioned, these are used in this routine just to check that there are some records to find - the bookmark property is not otherwise used. In other applications Bookmark can be used to go to a specific record by finding a match and then moving the record to the bookmark setting - just like placing a bookmark in a book - but I am not using this method in the routine provided.

TheForm is a local variable of mine of type Form, defined in the Dim statement associated with the code.

I would strongly suggest that you copy the code provided ' as is ' for the present, making only the change suggested to the control names to ensure that it fits your circumstances.

Regards

Stewart

**cephal0n** · Feb 21 '08, 12:45 PM

Hi Stewart!

Now I get IT!!! sorry for the delayed response but I'm still on the process of studying the code you provided, I hope it will be only a matter of time before I get my database Up n running smoothly.

Thanks for helping and explaining it more. It means a gazillion and more!

**Stewart Ross** · Feb 21 '08, 07:45 PM

Delighted to have been able to assist, Cephalon.
Thanks for your kind words.
-Stewart