HELP String Parsing Msaccess

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.Connection
Dim rsShowTable As ADODB.Recordset
Dim iResult as Integer
Dim iCounter as Integer
DIm i as String

Set conn1 = New ADODB.Connection
conn1.Provider = "Microsoft.Jet.OLEDB.4.0"
conn1.Open "C:\Apps\dataPMC.mdb"

Set rsShowTable = New ADODB.Recordset
rsShowTable.Open "tblItems", conn1, adOpenDynamic, adLockOptimistic, adCmdTable


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
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....pls, anyone who can put me in the right direction would very appreciated.

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

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.

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,however 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.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!