Help with "Search Form" (Terry Kreft and Dev Ashish)

[Tom]

I am using the "Search Form" that was designed by Terry Kreft and Dev Ashish
(http://www.mvps.org/access/forms/frm0045.htm).

In order to better follow this thread, I'd advise to download the form and
insert it to any database.


*********** Description of Problem ****************

The "CREATE" button creates then a query (if the user wishes to save it for
later use).

The default name for the query was "Query1" (found 2 in the functions). I
renamed it to "qryKeywordSearch".

Here now is the problem:

I want to link a report to "qryKeywordSearch". Currently, however, when
being prompted to save it (which again brings up "qryKeywordSearch") it will
not overwrite the existing query.

In order for the report to always show the latest query criteria, however,
that's exactly what I need to do.

So, my question is... what code needs to be added so that the
"qryKeywordSearch" is overwritten when the "OK button" on the "Save As"
dialog box is clicked?



Thanks in advance,
Tom





&&&&&&&&& Extraction of Functions &&&&&&&&&&&&&&&&&&&&&


Private Sub cmdCreateQDF_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
'first get a unique name for the querydef object
strName = Application.Run("acwzmain.wlib_stUniquedocname",
"qryKeywordSearch", acQuery)
strName = InputBox("Please specify a query name", "Save As", strName)
If Not strName = vbNullString Then
'only create the querydef if user really wants to.
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
qdf.Close
Else
'ok, so they don't want to
MsgBox "The save operation was cancelled." & vbCrLf & _
"Please try again.", vbExclamation + vbOKOnly, "Cancelled"
End If
ExitHere:
On Error Resume Next
qdf.Close
Set qdf = Nothing
db.QueryDefs.Refresh
Set db = Nothing
Exit Sub
ErrHandler:
Resume ExitHere
End Sub


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&


Private Function ExportRoutine()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim lorst As DAO.Recordset
Dim strName As String
Dim strFile As String
Const strSpecName = "~~TempSpec~~"
On Error GoTo ExportRoutine_err
With Me.lstResult
strFile = DialogFile(OFN_SAVE, "Save file", "", .Column(3) & " (" &
..Column(2) & ")|" & .Column(2), CurDir, .Column(2))
End With
If Len(strFile) > 0 Then
'first get a unique name for the querydef object
strName = Application.Run("acwzmain.wlib_stUniquedocname",
"qryKeywordSearch", acQuery)
Set db = CurrentDb
Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
qdf.Close
With lstResult
Select Case .Column(0)
Case 0 'Transferspreadsheet
DoCmd.TransferSpreadsheet acExport, .Column(1), strName, strFile,
True
Case 1 'Transfertext
If .Column(1) = acExportFixed Then
'Considerations
'Do MsysImexColumns and MsysImexSpecs exist
'Need to create if not
'Can use Max Length on each field in query to get lengths for
MsysImexSpecs

' Set lorst = db.OpenRecordset(strName)
'Do loads of other stuff in here ...
' DoCmd.TransferText .Column(1), , strName, strFile, True
Else
DoCmd.TransferText .Column(1), , strName, strFile, True
End If
End Select
End With
End If
ExportRoutine_end:
On Error Resume Next
DoCmd.DeleteObject acQuery, strName
qdf.Close
Set qdf = Nothing
db.QueryDefs.Refresh
Set db = Nothing
Exit Function
ExportRoutine_err:
Resume ExportRoutine_end
End Function


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&

 

[Gary Miller]

Tom,

I would suggest that you delete the query immediately before
creating the new one.

DoCmd.DeleteObject acQuery, "qryKeywordSearch"

Gary Miller
Sisters, OR

 

[Tom]

Gary:

Thanks, this works great!

This simplest solutions are sometimes the best. I surely didn't think of
this approach though.