Displaying the records from a query

[Guest]

Hi Guys,

I need some help in displaying the records from a createquerydef similar to
the QBE grid. What is the best way to display all of the records?? The
code that I have used opens a temporary query using the createquerydef
function using a select query ( ie. createquerydef("", select * from table X)
). The returned records I obtain, I don't know how to display them similar
to the nice QBE grid.

Is there a way to do this??

Please help me??

Cheers,

 

['69 Camaro]

Hi, Ian.

Is there a way to do this??

Not unless you're willing to create a QueryDef object with a name so that it
can be appended to the QueryDefs Collection and opened with DoCmd.OpenQuery.
For example:

Public Sub createAndDisplayQry()

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim sqlStmt As String

sqlStmt = "SELECT MoName " & _
"FROM tblAwardMonths;"

Set qry = CurrentDb().CreateQueryDef("qryAwardMoNames", sqlStmt)
DoCmd.OpenQuery qry.Name

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in createAndDisplayQry( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description

Err.Clear
GoTo CleanUp

End Sub

You may want to consider creating a QueryDef that can be reused at any time
in VBA code by merely changing the SQL property. Any time you need a
temporary query which needs to be named or needs to be added to the
QueryDefs Collection so that certain methods can be used, you could use the
following code to alter qryReuseMe, then display the data in a Datasheet
View:

Public Sub showReusableQry()

On Error GoTo ErrHandler

Dim qry As QueryDef
Dim sqlStmt As String

sqlStmt = "SELECT CID, FName, LName " & _
"FROM tblCustomers;"

Set qry = CurrentDb().QueryDefs("qryReuseMe")
qry.SQL = sqlStmt
DoCmd.OpenQuery qry.Name

CleanUp:

Set qry = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in showReusableQry( )." & _
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 and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Thanks Camero,

Is there any way that I can write code to detect if the query is in the
database so that initially the code will detect that the query is not saved
in the database and run the createquerydef, and if the query is detected then
the code you suppkied me would run the querydef code??

Is there any code that can do this ??


Is QRY in database Y or N (determine if qry is in database??)
If not qry exists then

createquerydef etc etc

ELSE

Set qry = CurrentDb().QueryDefs("qryReuseMe")

qry.SQL = sqlStmt
DoCmd.OpenQuery qry.Name

etc

ENDIF

Cheers,

ian