Howto increment the value of a field in a table? (1 Viewer)

[Woof]

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.

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

rstOut![Count1] = rstOut![Count1] + 1

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.

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

 

[Beetle]

First, a few things to note;

1) Count is a Reserved Word in Access and should not be used as the name of a user defined object (like a field). Maybe change it to something like WordCount. For more on Reserved Words look here.

2) You should always declare your variables.

3) You could just update the table directly rather than creating a recordset to update (seems like unnecessary overhead in this case).

Here is another coding option you might consider where you just split the string then loop through the resulting array. This just uses simple SQL to update the table.

Public Function funFindWords(strTagSet As String) As Integer

Dim varArray As Variant
Dim intCount As Integer
Dim intNewWords As Integer
Dim intExistingWords As Integer
Dim intTagID As Integer
Dim strMsg As String
Dim strInsert As String
Dim strUpdate As String

varArray = Split(strTagSet, " ")

intNewWords = 0
intExistingWords = 0

For intCount = LBound(varArray) To UBound(varArray)
    intTagID = Nz(DLookup("ID", "tblTagSet", "Word=""" & varArray(intCount) & """"), 0)

    If intTagID = 0 Then
        intNewWords = intNewWords + 1
        strInsert = "Insert Into tblTagSet (Word,WordCount) Values(""" & varArray(intCount) & """, " & "1)"
        CurrentDb.Execute strInsert, dbFailOnError
    Else
        intExistingWords = intExistingWords + 1
        strUpdate = "Update tblTagSet Set [WordCount] = [WordCount] + 1 Where ID=" & intTagID
        CurrentDb.Execute strUpdate, dbFailOnError
    End If

Next
        
strMsg = "Added " & intNewWords & " new words." & vbCrLf
strMsg = strMsg & "Updated " & intExistingWords & " existing words."

MsgBox strMsg

End Function

 

[Woof]

Hi and thanks for the reply. It is appreciated.

First, a few things to note;

1) Count is a Reserved Word in Access and should not be used as the name of a user defined object (like a field). Maybe change it to something like WordCount. For more on Reserved Words look here.

The only "Count" I have is a field name called Count1. There is also count2 count 3 and so on but I apprecieate the warning.

2) You should always declare your variables.

They are declared at form level making them available for other functions and routines.

3) You could just update the table directly rather than creating a recordset to update (seems like unnecessary overhead in this case).

I'm always pleased to hear of other options, I'll examine how that works and see if I can use it. Thanks.

Here is another coding option you might consider where you just split the string then loop through the resulting array. This just uses simple SQL to update the table.

Public Function funFindWords(strTagSet As String) As Integer
 
Dim varArray As Variant
Dim intCount As Integer
Dim intNewWords As Integer
Dim intExistingWords As Integer
Dim intTagID As Integer
Dim strMsg As String
Dim strInsert As String
Dim strUpdate As String
 
varArray = Split(strTagSet, " ")
 
intNewWords = 0
intExistingWords = 0
 
For intCount = LBound(varArray) To UBound(varArray)
    intTagID = Nz(DLookup("ID", "tblTagSet", "Word=""" & varArray(intCount) & """"), 0)
 
    If intTagID = 0 Then
        intNewWords = intNewWords + 1
        strInsert = "Insert Into tblTagSet (Word,WordCount) Values(""" & varArray(intCount) & """, " & "1)"
        CurrentDb.Execute strInsert, dbFailOnError
    Else
        intExistingWords = intExistingWords + 1
        strUpdate = "Update tblTagSet Set [WordCount] = [WordCount] + 1 Where ID=" & intTagID
        CurrentDb.Execute strUpdate, dbFailOnError
    End If
 
Next
 
strMsg = "Added " & intNewWords & " new words." & vbCrLf
strMsg = strMsg & "Updated " & intExistingWords & " existing words."
 
MsgBox strMsg
 
End Function