Help with CreateQueryDef

[businesshippieRH]

Essentially, I am trying to pass a string to the CreateQueryDef so that I can use the results of my sql to set rowsources dynamically on a form. However, I have not used this function before and am having trouble getting it to cooperate. I'm sure this is a simple fix for those "in the know". The "GetWhere ()" bit changes depending on selected comboboxes on the form. Hence why I can't code a SQL statement into the CreateQueryDef. My code is (part that I'm having trouble with is highlighted in red):

Private Sub cmb_RecordDistinction_LostFocus()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"CStr(""Original Description:"" & Chr(13) & Chr(10) & tbl_Records.Description & Chr(13) & Chr(10) & ""Revision Notes:"" & Chr(13) & Chr(10) & tbl_Records.RevisionNotes) AS Notes " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
'Set subform equal to results of query
Forms.frm_Search.subfrm_SEARCHSelector.Form.RecordSource = Finder
Me.subfrm_SEARCHSelector.Requery
'Set up Finder for RS Use
 Dim dbsRecords As Database
   Dim qdfTemp As QueryDef
   Dim qdfNew As QueryDef
 
   Set dbsRecords = CurrentDb
 
   With dbsRecords
      ' Create temporary QueryDef.
      Set qdfTemp = .CreateQueryDef("", _
         [COLOR="Red"]"'& Finder &'"[/COLOR])
      ' Open Recordset and print report.
      GetrstTemp qdfTemp
      ' Create permanent QueryDef.
      Set qdfNew = .CreateQueryDef("FinderResults", _
         "SELECT * FROM Orders")
      ' Open Recordset and print report.
      GetrstTemp qdfNew
      ' Delete new QueryDef because this is a demonstration.
      '.QueryDefs.Delete qdfNew.Name
      .Close
   End With
End Sub

Thanks in advance!

 

[businesshippieRH]

I answered my own question... For those who may be looking, this was the solution:

Private Sub cmb_RecordDistinction_LostFocus()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"CStr(""Original Description:"" & Chr(13) & Chr(10) & tbl_Records.Description & Chr(13) & Chr(10) & ""Revision Notes:"" & Chr(13) & Chr(10) & tbl_Records.RevisionNotes) AS Notes " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
'Set subform equal to results of query
Forms.frm_Search.subfrm_SEARCHSelector.Form.RecordSource = Finder
Me.subfrm_SEARCHSelector.Requery
'Set up Finder for RS Use
    Dim dbsRecords As Database
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef
 
    Set dbsRecords = CurrentDb
 
    With dbsRecords
    'Delete Old FinderResults
    .QueryDefs.Delete "FinderResults"
    ' Create temporary QueryDef.
    Set qdfTemp = .CreateQueryDef("", _
    Finder)
    ' Open Recordset and print report.
    GetrstTemp qdfTemp
    ' Create permanent QueryDef.
    Set qdfNew = .CreateQueryDef("FinderResults", _
         Finder)
    ' Open Recordset and print report.
    GetrstTemp qdfNew
      .Close
   End With
End Sub

 

[pbaldy]

I don't think you want the quotes and ampersands in that argument, just the variable. That said, I'd just use the SQL directly:

Whatever.RecordSource = Finder

 

[pbaldy]

Arg! Too slow.