Run query without a form (1 Viewer)

E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
I have a select query which has an input parameter in the where clause. I want to run this query and use the results as a recordset. I have already set the create query definition object. How can I run this query without using a hidden form.Here is the preparation part of the code
Code: 
Sub PrepareExport()
'--------------------------------------------------------------------------------
'   Get input from user and Open the output txt file using Stream number 2
'--------------------------------------------------------------------------------
    Dim Out As Variant
    Dim SEL As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef
    Dim SelQy As QueryDef
    Dim SQLStatement As String
    Dim sterm As String
    SQLStatement = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
                "WHERE (TelephoneAAA.SOYADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADRES Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.SEL Like '*" & sterm & "*')" & _
                "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
    
    Set qdf = CurrentDb.CreateQueryDef(SelQy, SQLStatement)
and here is the code trying to use it as a recordset:
Code: 
Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SelQy")

Any help is appreciated.
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:19
Joined
Jul 9, 2003
Messages
16,280
What Action causes the query to Run?
 
E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
Uncle Gizmo said:
What Action causes the query to Run?
Click to expand...

I tried
Code: 
db.Execute "SelQy"
DoCmd.RunSQL SQLStatement
They both do not work.
 
J

JHB

Have been here a while
Local time
Today, 14:19
Joined
Jun 17, 2012
Messages
7,732
You don't need to create a query extra for that, you put the query string direct in the Openrecordset.
Code: 
   Set rs = db.OpenRecordset("SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
           "WHERE (TelephoneAAA.SOYADI Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.ADI Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.ADRES Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.SEL Like '*" & sterm & "*') " & _
           "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;")
One of the problem you had was, you've declared SelQy but you didn't assign it.
Below is a link which shows you how to do it, (post back if you can't see the difference).
https://msdn.microsoft.com/en-us/library/office/ff195966.aspx
 
E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
JHB said:
You don't need to create a query extra for that, you put the query string direct in the Openrecordset.
Code: 
   Set rs = db.OpenRecordset("SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
           "WHERE (TelephoneAAA.SOYADI Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.ADI Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.ADRES Like '*" & sterm & "*') " & _
           "or (TelephoneAAA.SEL Like '*" & sterm & "*') " & _
           "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;")
One of the problem you had was, you've declared SelQy but you didn't assign it.
Below is a link which shows you how to do it, (post back if you can't see the difference).
https://msdn.microsoft.com/en-us/library/office/ff195966.aspx
Click to expand...
Thanks. It runs the query but does not prompt to get the input called sterm from the user. What should I do to have this prompt?
 
E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
JHB said:
Sorry, I didn't read you question correct.
You could have an Input box just before the query runs.
Here is a link to the function of the Input box.
https://support.office.com/en-za/article/InputBox-Function-17821927-28b7-4350-b7f1-4786575314d9
Click to expand...
No I do not have an input box before the query. The query itself should prompt for the input. That is I consider it this way. There is a main menu with a button on it. When I hit the button this select query is fired. The query itself asks for input of the sterm by displaying a pop-up box. The user enters the sterm and presses enter. After this point the query runs as a recordset, which I use as input to a report. This is the target I am trying to hit.
 
J

JHB

Have been here a while
Local time
Today, 14:19
Joined
Jun 17, 2012
Messages
7,732
Okay, then good luck because you can't use a parameter query in connection with a recordset, if it is consider or not. :rolleyes:
 
E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
JHB said:
Okay, then good luck because you can't use a parameter query in connection with a recordset, if it is consider or not. :rolleyes:
Click to expand...
So what you are saying is I have to use a form, if necessary in hidden format, to execute a SELECT query. Otherwise it is not possible to run a query using for example the DoCmd command. Indeed, I was thinking in the same direction. My only point was perhaps there is a way out and only the experts in the forum can say yes or no. So thank you that confirms my idea. I'll go ahead and define a dummy form and set the query as its recordsource.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Jan 23, 2006
Messages
15,379
Try this as your query definition

Code: 
Parameters sTerm text;
SELECT TelephoneAAA.*
FROM TelephoneAAA
WHERE (TelephoneAAA.SOYADI LIKE '*" & sterm & "*')
	OR (TelephoneAAA.ADRES LIKE '*" & sterm & "*')
	OR (TelephoneAAA.SEL LIKE '*" & sterm & "*')
ORDER BY TelephoneAAA.SOYADI
	,TelephoneAAA.ADI
	,TelephoneAAA.TEL;

Just run the query. It should ask for sTerm once. ( I didn't test this but have done similar)
 
J

JHB

Have been here a while
Local time
Today, 14:19
Joined
Jun 17, 2012
Messages
7,732
exaccess said:
So what you are saying is I have to use a form, if necessary in hidden format, to execute a SELECT query. Otherwise it is not possible to run a query using for example the DoCmd command.
Click to expand...
No, I'm not saying that you've to use a form, I'm saying: You can't use a parameter query in connection with a recordset.
It can be done in other ways, I've already mentioned one, which in this case appears to be the fastest and smartest way.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Jan 23, 2006
Messages
15,379
JHB,

You are correct. I focused on the query and parameter and did not consider the recordset. (failed to read post thoroughly)
I agree that the OP could just use an sql string and some inputbox statement(s) to get the value(s) and substitute it/them in the sql, then open the recordset.

There is an older post here using functions and public variables.
 
E

exaccess

Registered User.
Local time
Today, 14:19
Joined
Apr 21, 2013
Messages
287
Re: Run query without a form [SOLVED}

Thanks to All who contributed in the discussion. In the end I figured out my solution which is a combination of various techniques proposed. I created an action query to drop and recreate a temporary table. The source data of this table is coming from the same query running on another table. Finally I read the table as a recordset and create the VCF file line by line. The query when executed prompts for the input. The existence of the temporary table offers additional functionality should this be necessary in the future.
For those who may be looking for a similar case in the future I am posting the code here:
Code: 
'-----------------------------------------------------------------------------
'   open access query as recordset
'-----------------------------------------------------------------------------
    Dim SQry As String
    Set db = CurrentDb
    Debug.Print "dbname="; db.Name
    sSQL = "SELECT TelephoneAAA.* INTO TelephoneSelectedTbl FROM TelephoneAAA " & _
        "WHERE (TelephoneAAA.SOYADI like ('*')+[SearchPhrase]+('*'))" & _
        " or (TelephoneAAA.ADI like ('*')+[SearchPhrase]+('*'))" & _
        " or (TelephoneAAA.ADRES like ('*')+[SearchPhrase]+('*'))" & _
        " or (TelephoneAAA.SEL like ('*')+[SearchPhrase]+('*'))" & _
        " ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
    SQry = "SelQy"
    If QueryExists(SQry) Then db.QueryDefs.Delete (SQry)
    Debug.Print "query deleted"
    Set QDF = db.CreateQueryDef(SQry, sSQL)
    Debug.Print "query created"
    DoCmd.RunSQL sSQL
    Set rs = db.OpenRecordset("TelephoneSelectedTbl")
    Debug.Print "rs assigned"
 
You must log in or register to reply here.

Similar threads

B
Best way to execute querydefs in a loop using DoEvents?
Replies
7
Views
301
ebs17
E
R
  • Question
Save the names of tables, forms, and reports in a Microsoft Access database to a table named "ObjectNames.
2
Replies
32
Views
936
spaLOGICng
S
J
error in procedure
Replies
12
Views
280
Pat Hartman
P
N
Solved Challenges with Json against VBA
Replies
7
Views
555
nector
N
N
Solved How to grab fields from Json string to be part of the invoice
Replies
14
Views
445
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom