Automatically create "Description" and check "Hidden Attribute"

[Tom]

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

I have added the 2nd line of code "DoCmd.DeleteObject acQuery,
"qryKeywordSearch" so that I can link a report to the query (hence it will
always show the latest selected criteria.

I have 2 questions now:
1. What line(s) of code need to be added in order to automatically add a
"Description" in the properties?
2. What line(s) of code need to be added to automatically check the "Hidden
Attribute" in the properties?


Thanks,
Tom




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

Private Sub cmdCreateQDF_Click()

DoCmd.DeleteObject acQuery, "qryKeywordSearch"

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", "Query1",
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

 

[Wayne Morgan]

Instead of deleting the query and recreating it, which also creates the need you mention
here, why not just change the SQL of the query.

qdf.SQL = Me.txtSQL

 

[Tom]

Wayne:

Thanks for your prompt reply.

I presume that your way of modifying the function will "overwrite" the
existing query. Right? If that's the case, I'd like to follow up w/ the
following questions:

1. If the qry is overwritten, will the "properties" (description & hidden
attribute) be unchanged?

2. What line(s) of code must be replaced by "qdf.SQL = Me.txtSQL"?


Thanks again,
Tom

 

[Wayne Morgan]

1) I just tried it and it did NOT remove the hidden attribute or the description when I
changed the SQL of the query.

2) Replace

Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
qdf.Close

With

Set qdf = db.Querydefs(strName)
qdf.SQL = Me.txtSQL

Or actually, your not using qdf for anything else in the procedure, you could just drop it
out and use

db.Querydefs(strName).SQL = Me.txtSQL

 

[Tom]

Wayne:

Thanks so much for getting back on this.

Yes, this works great!

Thanks again,
Tom