Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Good afternoon

    MS Access 2007
    I have the following select statement

    strSql = "Select " & sTable & ".* "
    strSql = strSql & "FROM " & sTable & Chr(32)
    strSql = strSql & "WHERE " & sQFld & Chr(32)
    strSql = strSql & "=" & Chr(32) & sFrm

    This yields the following select statement with debug.print

    Select tblcontacts.* FROM tblcontacts WHERE tblcontacts.ContactId = [forms]![frmContactEdit]![ContactId]

    The create queryDef statement below works and behaves as expected
    Set qdf = db.CreateQueryDef("qry", strSql)

    However the attempt to open the recordset fails with error, too few parameters, expected 1
    Set rs2 = db.OpenRecordset("qry", dbOpenDynaset)

    When I stop the code I can manually select and execute the query successfully.

    What am I doing wrong?

    Regards

    Geof

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DAO operates at a lower level than Access; it doesn't "know" about Access forms so it doesn't recognize [forms]![frmContactEdit]![ContactId]

    You can do the following:

    strSql = "SELECT " & sTable & ".*"
    strSql = strSql & " FROM " & sTable
    strSql = strSql & " WHERE " & sQFld
    strSql = strSql & "=" & Forms!frmContactEdit!ContactID

    Set rs2 = db.OpenRecordset(strSql, dbOpenDynaset)

    This code doesn't create a stored query, it opens the recordset directly on the SQL string, and the SQL string uses the literal value of ContactID at runtime.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hello Hans
    Thanks for the assistance.

    In the last line of the query I would like to supply a variable that holds the expression [Forms]![FormName]![ControlName]

    strSql = strSql & "=" & varName

    The variable I am using reports correctly.
    Debug.print varName reports correctly and
    debug.print the var contents reports correctly.

    I have tried creating a function and addressing that in the SQL

    WHERE sFld = " & functionName

    I will have to try another approach.

    Geof

    [quote name='HansV' post='793831' date='18-Sep-2009 07:51']DAO operates at a lower level than Access; it doesn't "know" about Access forms so it doesn't recognize [forms]![frmContactEdit]![ContactId]

    You can do the following:

    strSql = "SELECT " & sTable & ".*"
    strSql = strSql & " FROM " & sTable
    strSql = strSql & " WHERE " & sQFld
    strSql = strSql & "=" & Forms!frmContactEdit!ContactID

    Set rs2 = db.OpenRecordset(strSql, dbOpenDynaset)

    This code doesn't create a stored query, it opens the recordset directly on the SQL string, and the SQL string uses the literal value of ContactID at runtime.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I get the impression you still have problems but you don't tell us what they are.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    [quote name='HansV' post='793952' date='18-Sep-2009 23:07']I get the impression you still have problems but you don't tell us what they are.[/quote]

    Hello Hans
    The same problem, too few parameters.

    I do not understand why concatenating the fully qualified reference, Forms!ForName!ContactId works but concatenating the varName doesnt.
    Problems with the keyboard/chair interface I am afraid.


    Here is some of my code
    Thanks
    Geof

    ---------
    code sample start
    -------
    Set db = currentdb()
    Set rs = db.OpenRecordset(oFrm.RecordSource, dbOpenDynaset)
    '--------------------------------------------------------
    'locate the sourceTable of activeControl & then get pk
    ' passing sTable to findPrimaryKey
    '--------------------------------------------------------
    sTable = rs(oFrm(sCtl).ControlSource).SourceTable
    sPK = FindPrimaryKey(db.TableDefs(sTable))
    sPK = Right(sPK, Len(sPK) - 1)

    sField = rs(oFrm(sPK).ControlSource).SourceField

    sQFld = sTable & "." & sPK 'primary Key field
    sFrm = "[forms]!" & "[" & oFrm.Name & "]" & "!" & "[" & sPK & "]"

    strSql = "Select " & sTable & ".* "
    strSql = strSql & "FROM " & sTable & Chr(32)
    strSql = strSql & "WHERE " & sQFld & Chr(32)
    strSql = strSql & "=" & Chr(32) & sFrm


    Set rs2 = db.OpenRecordset("strSql", dbOpenDynaset)
    '-------
    code sample end
    '-------
    db.openrecordset() errors out, Too few parameters

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your code will include the expression Forms!FormName!ContactId in the SQL, not its value. DAO does not recognize the expression Forms!FormName!ContactId because it doesn't know anything about Access forms.

    Try changing the line

    sFrm = "[forms]!" & "[" & oFrm.Name & "]" & "!" & "[" & sPK & "]"

    to

    sFrm = oFrm.Controls(sPK)

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    [quote name='HansV' post='793956' date='18-Sep-2009 23:51']Your code will include the expression Forms!FormName!ContactId in the SQL, not its value. DAO does not recognize the expression Forms!FormName!ContactId because it doesn't know anything about Access forms.

    Try changing the line

    sFrm = "[forms]!" & "[" & oFrm.Name & "]" & "!" & "[" & sPK & "]"

    to

    sFrm = oFrm.Controls(sPK)[/quote]

    Hello Hans
    You are a magician.
    Thanks for the education. I now understand your point about DAO.

    It works like a Swiss watch.

    Cheers
    Geof

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •