I know it's a silly question, but I'm getting errors. Here's an example mini-table. Id is autonumber, Word is text(100 chrs) and count is number.
A loop inside a function takes a word from the functions argument. It uses Dlookup to return the ID of the record if it's already in the table. (Null if it's not there of course.) If it does exist I want to increase the value of the count field for that record. How please?
I am currently using
but this doesn't specifiy what record in the recordset to use and I am getting error 3020, "Update or CancelUpdate without addnew or edit"
My questions are:
1. What syntax do I use to select the record I wish to update as returned by Dlookup, and
2. What syntax for the update of the field?
My current function is below. The passed string can have anything in it, an example may be "ANT HILL ABC ANY OLD TEXT WITHOUT LIMITS". Usually no more than 5 or 6 words but I'm trying to allow for up to 10 with my later handling.
There's other logical problems I see, I never use start position for example, but I've been looking at this for an hour now and I need to take my eyes off it.
Thanks for any advice.
ID Word Count
1 apple 12
2 pear 3
3 nose 56
A loop inside a function takes a word from the functions argument. It uses Dlookup to return the ID of the record if it's already in the table. (Null if it's not there of course.) If it does exist I want to increase the value of the count field for that record. How please?
I am currently using
Code:
rstOut![Count1] = rstOut![Count1] + 1
My questions are:
1. What syntax do I use to select the record I wish to update as returned by Dlookup, and
2. What syntax for the update of the field?
My current function is below. The passed string can have anything in it, an example may be "ANT HILL ABC ANY OLD TEXT WITHOUT LIMITS". Usually no more than 5 or 6 words but I'm trying to allow for up to 10 with my later handling.
There's other logical problems I see, I never use start position for example, but I've been looking at this for an hour now and I need to take my eyes off it.
Thanks for any advice.
Code:
Public Function funFindWords(strTagSet As String) As Integer
'This is the functions return value
funFindWords = 0
'Set this to the start of the string
intStartPos = 1
'get the length of the passed string
intLength = Len(strTagSet)
Do
'Find the position of the first 'space'
intSpacePostn = InStr(strTagSet, " ")
'if none, then all text is one word.
If intSpacePostn = 0 Then
strFoundWord = strTagSet
Else
'Get the word from the full set of tags. The -1 removes the space itself.
strFoundWord = Left(strTagSet, intSpacePostn - 1)
End If
'search rstOut for current instances of this word.
intTagID = DLookup("ID", "tblTagCount", "[Tag1] = '" & strFoundWord & "'")
'(Dlookup will return Null if the record does not exist)
'If it's <> Null then it already exists in the table. Just increase the count by 1
If Not IsNull(intTagID) Then
rstOut![Count1] = rstOut![Count1] + 1
'If it IS Null then it doesn't exist in the table. Add a record and update the count
Else
rstOut.AddNew
rstOut![Tag1] = strFoundWord
rstOut![Count1] = rstOut![Count1] + 1
rstOut.Update
intStartPos = intSpacePostn + 1
strTagSet = Right(strTagSet, (intLength - intSpacePostn))
End If
'Set return value, number of words found.
funFindWords = funFindWords + 1
Loop Until intSpacePostn = 0
End Function