Runtime Error 3061 (1 Viewer)

[boblife42]

Hello
I am comparing an array word to two different tables if the word is in one of the tables then I increase the count of that word, if the word is not in either table I add the word to a specific table. I thought I had the code figure out but I am getting a Runtime Error, any help would be appreciated.
The words have to be in uppercase to match the table.

Private Sub DescProblem_LostFocus()
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strSQL As String
Dim mySQL As String
Dim strArray() As String
Dim UCaseWord As String
Dim x As Integer

'Split text into array from description problem textbox
strArray = Split(DescProblem)
For x = 0 To UBound(strArray)
'Change strArray to UPPERCASE
UCaseWord = UCase(strArray(x))
'Query tbl_IndexedWord_Not_Used
    strSQL = "SELECT * FROM tbl_IndexedWord_Not_Used WHERE tbl_IndexedWord_Not_Used = '" & UCaseWord & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
'Test to see if word is in the Not_Used table if not query tbl_IndexedWords and openrecordset
If rs.EOF Then
    mySQL = "SELECT * FROM tbl_IndexedWords WHERE tbl_IndexedWords = '" & UCaseWord & "'"
Set rs1 = CurrentDb.OpenRecordset(mySQL)
'Test to see if word is in the IndexedWords table if not then add the word to the IndexedWord table
    If rs1.EOF Then
    Set rs2 = CurrentDb.OpenRecordset("tbl_IndexedWords")
        rs2.AddNew
        rs2!Uppercase = UpWordCase
        rs2.Update
'If the word is in the tbl_IndexedWords then increase the count by 1
    Else
        rs1.Edit
        rs1!SumOfCount = rs2!SumOfCount + 1
        rs1.Update
    End If
'If the word is in the tbl_IndexedWord_Not_Used then increase the count by 1
Else
    rs.Update
    rs!SumOfCount = rs!SumOfCount + 1
    rs.Update
    
End If
Next
'Close all openrecordset
rs.close
rs1.close
rs2.close


End Sub

I get the error message at this point

Set rs = CurrentDb.OpenRecordset(strSQL)

 

[KeithG]

Try DAO.Recordset instead of just Recordset.

There is both a DAO and ADODB recordset object in Access.

 

[boblife42]

Kinda new to this language how would change it to a DAO.Recordset

 

[midmented]

The query string is wrong
Aren't you missing a semicolon there on the end?
(
strSQL = "SELECT * FROM tbl_IndexedWord_Not_Used WHERE tbl_IndexedWord_Not_Used = '" & UCaseWord & "'")

k, let me edit my comment....
Define the field your looking for as well.

strSQl = "SELECT * from tbl_IndexedWord_Not_Used WHERE tbl_IndexedWord_Not_Used.FIELDNAME ='" &UCaseWord & "';"

 

[KeithG]

Just change Dim rs as Recordset to Dim rs as DAO.Recordset

 

[midmented]

DLookup

Looking through your code, I would actually use a dlookup. Alot less code.
I hate opening recordsets if there is no need to do it.

Like:

'Split text into array from description problem textbox
strArray = Split(DescProblem)
For x = 0 To UBound(strArray)
'Change strArray to UPPERCASE
UCaseWord = UCase(strArray(x))
'Query tbl_IndexedWord_Not_Used

Dim blnWordFound as Boolean

'False if word is not found
blnWordFound = nz(DLookup("FIELDNAME","tbl_IndexedWord_Not_Used","[FIELDNAME] ='" & UCaseWord & "'"),False)

If blnWordFound = false then
 'Update the recordset
else
 'Nothing?
end if

Just an idea......

 

[boblife42]

Neither of those worked

 

[pbaldy]

Are your field names actually the same as the table names?

 

[KeithG]

What is the actual error message text?

 

[midmented]

Name of Field?

You noticed that FIELDNAME is supposed to be whatever field the word is located in you are looking for?

What is the field name?

 

[boblife42]

pbaldy:
Yes the field names in the code are what I have to use.

 

[boblife42]

Message:
Runtime Error '3061'
Too few Parameters Expected 1

 

[KeithG]

I would assume tbl_IndexedWord_Not_Used is a table, correct? Look at you Where clause in the variable you donot have a field in it.

 

[midmented]

That runtime error usually means "There are too few parameters" which I think is because of the SQL strings. Simply having tbl_IndexedWord_Not_Used isn't telling it what field to look in for the data. You need to add the .FIELDNAME (change the bold part of the SQL statement below to whatever field you are looking in for the word)

strSQl = "SELECT * from tbl_IndexedWord_Not_Used WHERE tbl_IndexedWord_Not_Used.FIELDNAME ='" &UCaseWord & "';"

If the fieldname is the same as the tablename then

strSQl = "SELECT * from tbl_IndexedWord_Not_Used WHERE tbl_IndexedWord_Not_Used.tbl_IndexedWord_Not_Used ='" &UCaseWord & "';"

 

[boblife42]

Thank you that worked I feel silly for forgetting that, I had it written down.

 

[pbaldy]

You don't actually need to specify both generally, but since the table and field names are the same, Access got confused. I'd change that if it's not too late.