Can I call a SQL query from a form using the actual SQL Command?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Instead of .openquery("Name of query") is there a function that will allow me
to call a SQL command using the actual SQL command string (SELECT * ....)?

Or is there a way to modify a prebuilt query from the form code?

Any help is much appreciated,

Thanks,

Blake
 
You can try this

Create a query, empty query , and use this code to change the sql within
this query, and
then run it.

Application.CurrentDb.QueryDefs("Queryname").SQL = "Select * From TableName
Where FieldName = " & Param
Docmd.OpenQuery "Queryname
 
Hi, Blake.
Instead of .openquery("Name of query") is there a function that will allow me
to call a SQL command using the actual SQL command string (SELECT * ....)?
Click to expand...

Without knowing how this SQL statement is going to be used, one can only be
vague on its uses. One may use a SQL statement for a Recordset Object, a
QueryDef Object, a Row Source Property of a combo box or list box, or with
the Execute method (for action queries only).
Or is there a way to modify a prebuilt query from the form code?
Click to expand...

Absolutely. Here's an example of an existing query named qryTemp being
modified in VBA code:

Public Sub TransferTextWParam(sDataSrc As String, sFldName As String,
sCriteria As String)

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim sqlStmt As String

sqlStmt = "SELECT * " & _
"FROM " & sDataSrc & _
" WHERE (" & sFldName & " = '" & sCriteria & "');"

Set qry = CurrentDb().QueryDefs("qryTemp")
qry.SQL = sqlStmt
DoCmd.TransferText acExportDelim, , qry.Name, "C:\Data\XferText.txt", True

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in TransferTextWParam( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top