none
Automation Error RRS feed

  • Question

  • I keep getting the following error:

    "The Following Automation Error has occurred:  Too few parameters.  Expected 1.

    and I believe it is with my SELECT query.  Is this correct:

    
        Set rsProjects = CurrentDb.OpenRecordset( _
                "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization " & _
                "FROM [Event Attendants] INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID " & _
                "WHERE ([Event Attendants].[Unescorted Acces Request] =True AND (([Event Attendants].idrEventID)=[forms]![Event]![ID]))")

    Thursday, May 31, 2018 6:37 PM

Answers

  • You are embedding the Form value in the SQL statement before it can be evaluated. Try the following:

        Set rsProjects = CurrentDb.OpenRecordset( _
                "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization " & _
                "FROM [Event Attendants] INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID " & _
                "WHERE ([Event Attendants].[Unescorted Acces Request] =True AND (([Event Attendants].idrEventID)=" & [forms]![Event]![ID] & "))")

    If idrEventID is a text data type you will need to add single quotes:

        Set rsProjects = CurrentDb.OpenRecordset( _
                "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization " & _
                "FROM [Event Attendants] INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID " & _
                "WHERE ([Event Attendants].[Unescorted Acces Request] =True AND (([Event Attendants].idrEventID)='" & [forms]![Event]![ID] & "'))")




    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 31, 2018 8:29 PM

All replies

  • Hi,

    One way to avoid the error is to try and use Leigh's Generic Recordset found here.

    You will then replace your code with something like:

    Set rsProject = fDAOGenericRst("SELECT...

    Hope it helps...

    Thursday, May 31, 2018 6:44 PM
  • You are embedding the Form value in the SQL statement before it can be evaluated. Try the following:

        Set rsProjects = CurrentDb.OpenRecordset( _
                "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization " & _
                "FROM [Event Attendants] INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID " & _
                "WHERE ([Event Attendants].[Unescorted Acces Request] =True AND (([Event Attendants].idrEventID)=" & [forms]![Event]![ID] & "))")

    If idrEventID is a text data type you will need to add single quotes:

        Set rsProjects = CurrentDb.OpenRecordset( _
                "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization " & _
                "FROM [Event Attendants] INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID " & _
                "WHERE ([Event Attendants].[Unescorted Acces Request] =True AND (([Event Attendants].idrEventID)='" & [forms]![Event]![ID] & "'))")




    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 31, 2018 8:29 PM
  • "The Following Automation Error has occurred:  Too few parameters.  Expected 1.

    Hi NoviceVBAuser1775,

    You get this kind of error when one or more of the fields are not known: check for the spelling.

    As you can see, because of the spaces in the object names you need square brackets. It is harder to read and to type, so avoid spaces in object names!

    I have placed the reference to the Event table outside the quoted string, to get the value of this parameter instead of the literal tekst.

    Further I transfered the separating spaces at the end of each line from the end to the beginning of the lines for much better readability.

    I use very generalized functionality for many purposes. That is why I always fill a global variable (active_sql) with a value, and use active_sql for further processing.

    When you use nested joins, it has advantages to surround each join with round brackets.

    Example:

      active_sql = "SELECT [Event Attendants].Attendant, [Contacts Extended].Organization" _
          & " FROM ([Event Attendants]" _
          & " INNER JOIN [Contacts Extended] ON [Event Attendants].Attendant = [Contacts Extended].ID)" _
          & " WHERE [Event Attendants].[Unescorted Acces Request] = True AND [Event Attendants].idrEventID = " & Forms("Event")!ID
      Set rsProjects = CurrentDb.OpenRecordset(active_sql)

    Imb.


    • Edited by Imb-hb Thursday, May 31, 2018 8:57 PM
    Thursday, May 31, 2018 8:55 PM
  • Hello NoviceVBAuser1775,

    Is your original issue resolved? If it is, I would suggest you mark the helpful reply to close the thread.

    If not, please feel free to let know the current state.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 4, 2018 7:33 AM
  • Paul,  it seems that your first example has worked. Than you all for help.
    Saturday, June 9, 2018 1:24 PM