Single value INSERT from code produces no result

[clotposlo3]

I'm checking combo boxes for duplicate values on a form and counting the number of occurrences. I'd like to insert the number of occurrences into a table. The code runs without errors, but nothing is being inserted.

'Get names into an array to compare and find number of occurrences
Set rst = CurrentDb.OpenRecordset("RawList")
rnum = rst.RecordCount
ReDim strRname(rnum)
rname = rst.GetRows(rnum)       
 
For i = 0 To rnum - 1
    strRname(i) = rname(3, i)
Next
 
'Count number of times a name appears in the form
 
For i = 0 To rnum - 1
    NumOccurences = 0
    With Me
        For Each ctl In .Controls
            If ctl.ControlType = acComboBox Then
                If Not IsNull(ctl.Value) Then
                    ctlR = ctl.Value
                    If (StrComp(strRname(i), ctlR) = 0) Then
                        NumOccurrences = NumOccurrences + 1
                    End If
                End If
            End If
        Next
    End With
    'Insert number of occurrences into RQty
    CurrentDb.Execute "Insert Into RawList (RQty) Values ('" & NumOccurrences & "')"
Next
 

I've checked with Debug.Print to make sure that things are comparing and adding up correctly, so I think something is wrong with the CurrentDb.Execute statement.

TIA for any help...

[clotposlo3]

Sorry, I see that the values are being added to the table, but they're being appended into the next available row. Is there a way to make them insert into the first empty space in that column?

[jforbes]

I'm not sure what your attempting to do.

CurrentDb.Execute "Insert Into RawList (RQty) Values ('" & NumOccurrences & "')"

Will insert a new line into the RawList table. If you are expecting it update one of the existing records, you should be using the Update Statement.

[clotposlo3]

jforbes,

I didn't know about the UPDATE statement. I'm new to Access and VBA, but after looking at UPDATE in the Help files, I think that will work. Thanks for your help!