none
Set rs=Currentdb().Openrecordset not working RRS feed

  • Question

  • Have the following code in the OnCurrent of a form named [Heat No Log] in Access 2016:

    Private Sub Form_Current()
      Dim rs As Recordset
      Dim strSQL As String
      strSQL = "SELECT [Lot Nos].*FROM [Lot Nos] WHERE ((([Lot Nos].[Heat No])=[Forms]![Heat No Log]![Heat No]));"
      Set rs = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset)
    If rs.RecordCount = 0 Then
            MsgBox "1"
    End If
    End Sub

    Getting an error, # 3061, Too Few Parameters, Expected 1. on the line 

    Set rs = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset)

    What do I need to fix to make this work. Want to check a subform with a recordset the same as the SQL string for no records.

    Thanks,

    Sunday, November 10, 2019 11:31 PM

All replies

  • Hi,

    Based on your description, I will move this thread to Access for Developer forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.


    Monday, November 11, 2019 2:40 AM
  • Hum, something not right.

    When you enter currentdb and the hit the “.” (dot), you should get/see openRecordSet pop up.

    As a general rule then

    Currentdb.OpenRecordSet()

    Is the correct syntax. But,

    Currentdb().OpenRecordSet() is allowed.

    I would first do a debug->compile from the VBA menu bar.

    You have some stay brackets out of place here.

    Most notable is this one: .[Heat No])<-- this one.

      

       Dim strSQL        As String
       Dim rs            As Recordset
    
       strSQL = "SELECT [Lot Nos].* FROM [Lot Nos] WHERE [Lot Nos].[Heat No] = [Forms]![formA]![Textbox1]"
    
       Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    'And you need/want a space after the .*
    
    'You have [Lot Nos].*FROM
    
    'And it should be
    
    [Lot Nos].* FROM
    
    Now, I suppose we could add some () around this, we would get:
    
       strSQL = "SELECT [Lot Nos].* FROM [Lot Nos] WHERE ([Lot Nos].[Heat No] = [Forms]![formA]![Textbox1])"

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Monday, November 11, 2019 5:12 AM
  • While the error is raised by the line cited, it is caused by the previous line.  Concatenate the value of the control into the string expression rather than a reference to it as a parameter.  If Heat No is a number data type:

    strSQL = "SELECT * FROM [Lot Nos] WHERE [Heat No] = "  & [Forms]![Heat No Log]![Heat No]

    If Heat No is a text data type:

    strSQL = "SELECT * FROM [Lot Nos] WHERE [Heat No] = """  & [Forms]![Heat No Log]![Heat No] & """"

    Ken Sheridan, Stafford, England

    Monday, November 11, 2019 5:50 PM