error opening a recordset in MS Access 2002, vba script

[breadhead]

Good morning, all.

I am creating an Access 2002 application to run on XP clients and I'm struggling with the task of testing whether a single-field record exists before allowing the user to add it to a look up table. I considered creating a unique index on the field in the table, but the users here would not know how to handle the error message. From there, I moved to creating a recordset that tests for the presence of a matching record before performing the insert, but I have not been able to open the recordset.

I received the error message "Run-time error 3061. Too few parameters. Expected 1." on the line of code, below, where I try to open the recordset.

Can anyone offer advice on how to open the recordset, or another way to test for duplication of data before allowing the insert of the new record?

Thanks in advance, Mark


The code for the recordset follows...

Dim newCat, status As String
newCat = Text_newCat 'set variable equal to text on screen
status = "Checking if Category already exists..."  ' tell the user what's up
Text_status = status  ' update text box with the message
 
Dim db As database
Dim rs As Recordset
Dim Occurances As Integer
Dim countsql As String
 
'open record set of household categories matching newCat
countsql = "SELECT [Household Category].HHC_Desc, Count([Household Category].HHC_Key) AS CountOfHHC_Key " & _
           "FROM [Household Category] " & _
           "GROUP BY [Household Category].HHC_Desc " & _
           "HAVING ((([Household Category].HHC_Desc)=[Forms]![Add Household Category]![Text_newCat]));"
 
Set db = CurrentDb
Set rs = db.openrecordset(countsql)
rs.MoveLast
Occurances = rs.RecordCount
 
If Occurances = 0 Then
Text_status = "Adding New Household Category..."
Dim sql1, sql2 As String
sql2 = "INSERT INTO [Household Category] ( HHC_Desc ) select '" & newCat & "';"
DoCmd.RunSQL sql2
Text_status = "New Household Category added..."
Else
    Text_status = "This Household Category already exists."
End If

[MMcCarthy]

Try changing the first query as follows:

countsql = "SELECT [Household Category].HHC_Desc, Count([Household Category].HHC_Key) AS CountOfHHC_Key " & _
"FROM [Household Category] " & _
"WHERE [Household Category].HHC_Desc=[Forms]![Add Household Category]![Text_newCat] " & _
"GROUP BY [Household Category].HHC_Desc;"

As for the syntax of the INSERT Query ...

sql2 = "INSERT INTO [Household Category] ( HHC_Desc ) select '" & newCat & "';"

What is a sample of the text value normally behind newCat?

[breadhead]

The newCat text would be something like...

Christmas card recipient
Small Business Loan luncheon
member of the Country Club

These values will be stored in a two field table, with the first column an autonumbered key and the second a 150 character text field.

I'll try your changes this morning and let you know how it goes. Thanks for your advice.

Mark

Try changing the first query as follows:

countsql = "SELECT [Household Category].HHC_Desc, Count([Household Category].HHC_Key) AS CountOfHHC_Key " & _
"FROM [Household Category] " & _
"WHERE [Household Category].HHC_Desc=[Forms]![Add Household Category]![Text_newCat] " & _
"GROUP BY [Household Category].HHC_Desc;"

As for the syntax of the INSERT Query ...

sql2 = "INSERT INTO [Household Category] ( HHC_Desc ) select '" & newCat & "';"

What is a sample of the text value normally behind newCat?

[breadhead]

Try changing the first query as follows:

countsql = "SELECT [Household Category].HHC_Desc, Count([Household Category].HHC_Key) AS CountOfHHC_Key " & _
"FROM [Household Category] " & _
"WHERE [Household Category].HHC_Desc=[Forms]![Add Household Category]![Text_newCat] " & _
"GROUP BY [Household Category].HHC_Desc;"

I made this change, but I am still receiving the same error message as previously stated. It seems to me I'm doing something wrong in the initiation of the recordset. This is the first instance of this I've done, and all the help files I have seen include connection objects pointing to external data sources, not within the same Access mdb file as I am trying to do.

Still seeking guidance,
Mark

[MMcCarthy]

I made this change, but I am still receiving the same error message as previously stated. It seems to me I'm doing something wrong in the initiation of the recordset. This is the first instance of this I've done, and all the help files I have seen include connection objects pointing to external data sources, not within the same Access mdb file as I am trying to do.

Still seeking guidance,
Mark

Change your second query to ...

sql2 = "INSERT INTO [Household Category] ( HHC_Desc ) VALUES ('" & newCat & "');"