Select Query Mod doesn't display results

  • Thread starter Thread starter Steve-O
  • Start date Start date
S

Steve-O

I have a mod below that is trying to select records and
contain the query in the module (thanks to those who have
helped this far!). However, the module below doesn't
display the results? Does anyone have any idea why/can you
please tell me how to display the results? If this works,
this should be my last post. Thank you VERY much for your
help!

Option Compare Database

Sub SelectQuery()
Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Set dbCurr = CurrentDb()
strSQL = "SELECT Orders.*"
strSQL = strSQL & " FROM Orders"
strSQL = strSQL & " ORDER BY Product_ID;"
Set rsCurr = dbCurr.OpenRecordset(strSQL, dbOpenSnapshot,
dbReadOnly)
End Sub
 
HI Steve-O!!
I'm not sure exactly what you're looking for. So I'm
taking a chance here...
The way I've tested you're code is has follow:

1- Create a query using the Wizard or the 'create query
method... in the DataBase window.

INSIDE, the query sould look like this:
Select * from Orders Order By Product_ID
save as 'TestQuery'.

2-Change de code inside you're SubQuery procedure to:

Public Sub subQuery()
DoCmd.OpenQuery "TestQuery", , acReadOnly
End Sub

3- In his case I used a button to call you're Subquery
routine.
Call SubQuery (In the 'Click-Event' of the button)

THIS WILL show the result of you're query on screen for
you to view.

Hope this is what you where looking for..
 
Patrick- Thank you for your help. I'm actually trying to
prevent having a query database object, though. Rather
than having the code call the query object, I'm trying to
contain the sql for the query in the module itself. This
mod contains the query (thanks to others' help), but it
won't display. Any clues? Thanks again!
 
Sorry about that! When you say you want to display what's
inside the recordSet, are you perhaps talking about
viewing-it with the help of a report?
IF so, that I can do, for the rest I don't think that
there is alot of ways to do-it!
I can think of: Via reports or calling a query(previous
code) or sending the data to a grid or listBox maybe.

I will keep an eye on your Post, because you've actually
pic my curiosity to!
If you want to know how to send your query to a listbox or
a report let me know, else
BEST of luck to you!
 
Patrick- I appreciate your help. What I was ideally hoping
to do was have the results display as if a query object
with the same SQL had been opened. If that is not
possible, I guess displaying the data in a grid would be
the next closest thing? Thank you again for all of your
help!
 
Steve, this is what I got for the datagrid/listBox.
The example below has been tested and should work find.
I don't have access to the dataGrid but if you do, you
wont have much difficuly in upgrading....
Here goes:
call the subQuery from where you want. For starters,make
sure the listbox and button are on the same form, and after
the code works, do what you realy want to do...

Public Sub subQuery()
Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim cpt As Integer

Set dbCurr = CurrentDb()
strSQL = "SELECT * FROM clients ORDER BY ClientNo"

' If you want to absolutely use the recordSet property,
' in this case, You could use-it to see if data is returned
' from your query, if not you skip the process, else you
do-it.
' Something like this:
Set rsCurr = dbCurr.OpenRecordset(strSQL)
cpt = rsCurr.RecordCount

If cpt = 0 Then
' skip process,leave procedure.
Else
' send it to the list or grid for viewing..
listTest.RowSource = strSQL
' For this to work OK, you will need to edit a few of the
' 'ListTest' properties. Like:
' - Column count (set to whatever you need...)
' - Columnhead=Yes
' - Width: 0.5";1", etc... represents
' the width for each Column you wish to show the user.

' hopefully you will have access to the dataGrid.
Keep in mind that,
' if you don't have acces to it, the listBox is pretty
End If

'Sorry my access doesn't allow me to use Grid Active X
control,
' but the affectation of 'strSQL would be done the same
way.
' VIA the RowSource property.

'P.S. This is what I would do in this case, If there's a
bettet
' way please let us know.

'This code now, verifies if the query(in recordSet)returns
'anything, and does the tasks if it can.

'Again I hope this helps, this is how I would sholve the
problem
' until I could Stumble on something better and/or faster.



End Sub
 
Thank you for all your help :)
-----Original Message-----
Steve, this is what I got for the datagrid/listBox.
The example below has been tested and should work find.
I don't have access to the dataGrid but if you do, you
wont have much difficuly in upgrading....
Here goes:
call the subQuery from where you want. For starters,make
sure the listbox and button are on the same form, and after
the code works, do what you realy want to do...

Public Sub subQuery()
Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim cpt As Integer

Set dbCurr = CurrentDb()
strSQL = "SELECT * FROM clients ORDER BY ClientNo"

' If you want to absolutely use the recordSet property,
' in this case, You could use-it to see if data is returned
' from your query, if not you skip the process, else you
do-it.
' Something like this:
Set rsCurr = dbCurr.OpenRecordset(strSQL)
cpt = rsCurr.RecordCount

If cpt = 0 Then
' skip process,leave procedure.
Else
' send it to the list or grid for viewing..
listTest.RowSource = strSQL
' For this to work OK, you will need to edit a few of the
' 'ListTest' properties. Like:
' - Column count (set to whatever you need...)
' - Columnhead=Yes
' - Width: 0.5";1", etc... represents
' the width for each Column you wish to show the user.

' hopefully you will have access to the dataGrid.
Keep in mind that,
' if you don't have acces to it, the listBox is pretty
End If

'Sorry my access doesn't allow me to use Grid Active X
control,
' but the affectation of 'strSQL would be done the same
way.
' VIA the RowSource property.

'P.S. This is what I would do in this case, If there's a
bettet
' way please let us know.

'This code now, verifies if the query(in recordSet)returns
'anything, and does the tasks if it can.

'Again I hope this helps, this is how I would sholve the
problem
' until I could Stumble on something better and/or faster.



End Sub

.
Click to expand...
 
Back
Top