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...
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...
Code:
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
Comment