Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002

    Cool Unanswered: Using unbound forms to create Make Table Queries with DAO

    I have a problem with the query I am trying to construct using the query def in DAO I have referenced all the parameters which the form uses in the query, but when the query runs it creates a table with the name of the control rather than the data value contained within it.
    A sample of the code is contained below. What is confusing me is that the other two parameters on the form work perfectly well.

    Private Sub cmdMake_Click()
    Dim dbs As Database
    Dim wrk As Workspace
    Dim strmSql As String
    Dim strmParam As String
    Dim strmMa As String
    Dim strmWh As String
    Dim rstComfortZone As Recordset
    Dim qdfMake As QueryDef
    Dim frmAllocations As Form
    Dim ctlId As Control, ctlQuanRef As Control, ctlIdEnd As Control, ctlMake As Control
    Dim ctlOperator As Control, ctlFileRef As Control

    Set frmAllocations = Forms![frmAllocation]
    Set ctlId = Forms![frmAllocation]![txtID]
    Set ctlIdEnd = Forms![frmAllocation]![txtIdEnd]
    Set ctlQuanRef = Forms![frmAllocation]![txtQuanRef]
    Set ctlOperator = Forms![frmAllocation]![txtOperator]
    Set ctlFileRef = Forms![frmAllocation]![txtFileRef]
    Set ctlMake = Forms![frmAllocation]![txtMake]

    strParam = "PARAMETERS ctlId Integer, ctlIdEnd Integer, ctlMake TEXT; "
    strUp = "SELECT Comfortzone .* INTO ctlMake FROM ComfortZone "
    strWh = "WHERE Id BETWEEN ctlId AND ctlIdEnd "
    strSql = strParam & strUp & strWh

    Set dbs = CurrentDb()
    Set qdfMake = dbs.CreateQueryDef("", strSql)
    qdfMake.Parameters(0) = ctlId
    qdfMake.Parameters(1) = ctlIdEnd
    qdfMake.Parameters(2) = ctlMake
    Set rstComfortZone = dbs.OpenRecordset("ComfortZone", dbOpenDynaset)

  2. #2
    Join Date
    Nov 2001

    strUp = "SELECT Comfortzone .* INTO " & ctlMake & " FROM ComfortZone "
    In this case you don't need ctlMake as a parameter.


  3. #3
    Join Date
    Nov 2001
    I took another look at your code. Have you considered the following code?

    strSQL = "SELECT Comfortzone .* INTO " & ctlMake & " FROM ComfortZone WHERE Id BETWEEN " & ctlId " AND " & ctlIdEnd & ";"
    currentdb.execute strSQL

  4. #4
    Join Date
    Feb 2002
    Thanks for that, its helped me out of a jam.

Posting Permissions

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