error opening a recordset in MS Access 2002, vba script

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • breadhead
    New Member
    • Jul 2007
    • 4

    #1

    error opening a recordset in MS Access 2002, vba script

    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...

    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
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    Try changing the first query as follows:

    [CODE=vb]
    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;"[/CODE]

    As for the syntax of the INSERT Query ...

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

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

    Comment

    • breadhead
      New Member
      • Jul 2007
      • 4

      #3
      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


      Originally posted by mmccarthy
      Try changing the first query as follows:

      [CODE=vb]
      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;"[/CODE]

      As for the syntax of the INSERT Query ...

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

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

      Comment

      • breadhead
        New Member
        • Jul 2007
        • 4

        #4
        Originally posted by mmccarthy
        Try changing the first query as follows:

        [CODE=vb]
        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;"[/CODE]
        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

        Comment

        • MMcCarthy
          Recognized Expert MVP
          • Aug 2006
          • 14387

          #5
          Originally posted by breadhead
          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 & "');"

          Comment

          Working...