opening query from VBA

Hi all,

I want to create a temporary querydef, open it, inspect
the contents and then close it.

Creating it works fine :
Set qdf = db.CreateQueryDef("", strSQL)

But then how do I open it and read it ?

Thanks in advance,

Wim

On Sat, 31 Jan 2004 21:38:51 -0800, "Wim"
<anonymous@discussions.microsoft.com> wrote:

>Hi all,
>
>I want to create a temporary querydef, open it, inspect
>the contents and then close it.
>
>Creating it works fine :
>Set qdf = db.CreateQueryDef("", strSQL)
>
>But then how do I open it and read it ?
>
>Thanks in advance,
>
>Wim
Open a Recordset based on the querydef:

Dim rs As DAO.Recordset
Set rs = qdf.OpenRecordset
rs.MoveFirst
Do Until rs.EOF
thisvar = rs!fieldname
thatvar = rs!anotherfield
rs.MoveNext
Loop

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Hi John,

Thanks for your answer. I'm afraid I didn't explain my
problem well enough. As a matter of fact I want to have a
table-like browser, based on the on-the-fly query, so that
I can verify very quickly if all my records are valid.

Could you suggest a snippet of code for that ?

Thank you,

Wim



>-----Original Message-----
>On Sat, 31 Jan 2004 21:38:51 -0800, "Wim"
><anonymous@discussions.microsoft.com> wrote:
>
>>Hi all,
>>
>>I want to create a temporary querydef, open it, inspect
>>the contents and then close it.
>>
>>Creating it works fine :
>>Set qdf = db.CreateQueryDef("", strSQL)
>>
>>But then how do I open it and read it ?
>>
>>Thanks in advance,
>>
>>Wim
>Open a Recordset based on the querydef:
>
>Dim rs As DAO.Recordset
>Set rs = qdf.OpenRecordset
>rs.MoveFirst
>Do Until rs.EOF
>thisvar = rs!fieldname
>thatvar = rs!anotherfield
>rs.MoveNext
>Loop
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.
>

Hi Wim,

These are the steps I would take:

1. Create a form and set ALL the fields you ever want to
view on that form.
2. Use code to change your query as you did in your code.
Make sure the SQL is correct of course!
3. Using code :
- change the visible property of each field you wish to
view (or not to view)
- change the recordsource of the form by using the
following code snippet:
frm.Recordsource = qdf.Name if the qry is the query you
just altered!


Good luck!

>-----Original Message-----
>Hi John,
>
>Thanks for your answer. I'm afraid I didn't explain my
>problem well enough. As a matter of fact I want to have
a
>table-like browser, based on the on-the-fly query, so
that
>I can verify very quickly if all my records are valid.
>
>Could you suggest a snippet of code for that ?
>
>Thank you,
>
>Wim
>
>
>
>>-----Original Message-----
>>On Sat, 31 Jan 2004 21:38:51 -0800, "Wim"
>><anonymous@discussions.microsoft.com> wrote:
>>
>>>Hi all,
>>>
>>>I want to create a temporary querydef, open it, inspect
>>>the contents and then close it.
>>>
>>>Creating it works fine :
>>>Set qdf = db.CreateQueryDef("", strSQL)
>>>
>>>But then how do I open it and read it ?
>>>
>>>Thanks in advance,
>>>
>>>Wim
>>Open a Recordset based on the querydef:
>>
>>Dim rs As DAO.Recordset
>>Set rs = qdf.OpenRecordset
>>rs.MoveFirst
>>Do Until rs.EOF
>>thisvar = rs!fieldname
>>thatvar = rs!anotherfield
>>rs.MoveNext
>>Loop
>>
>> John W. Vinson[MVP]
>> Come for live chats every Tuesday and Thursday
>>http://go.compuserve.com/msdevapps?loc=us&access=public
>>.
>>
>.
>

Hi Flupke,

Thanks for your help.
I'll give it a try.

Kwik
>-----Original Message-----
>Hi Wim,
>
>These are the steps I would take:
>
>1. Create a form and set ALL the fields you ever want to
>view on that form.
>2. Use code to change your query as you did in your code.
>Make sure the SQL is correct of course!
>3. Using code :
>- change the visible property of each field you wish to
>view (or not to view)
>- change the recordsource of the form by using the
>following code snippet:
>frm.Recordsource = qdf.Name if the qry is the query you
>just altered!
>
>
>Good luck!
>
>>-----Original Message-----
>>Hi John,
>>
>>Thanks for your answer. I'm afraid I didn't explain my
>>problem well enough. As a matter of fact I want to have
>a
>>table-like browser, based on the on-the-fly query, so
>that
>>I can verify very quickly if all my records are valid.
>>
>>Could you suggest a snippet of code for that ?
>>
>>Thank you,
>>
>>Wim
>>
>>
>>
>>>-----Original Message-----
>>>On Sat, 31 Jan 2004 21:38:51 -0800, "Wim"
>>><anonymous@discussions.microsoft.com> wrote:
>>>
>>>>Hi all,
>>>>
>>>>I want to create a temporary querydef, open it,
inspect
>>>>the contents and then close it.
>>>>
>>>>Creating it works fine :
>>>>Set qdf = db.CreateQueryDef("", strSQL)
>>>>
>>>>But then how do I open it and read it ?
>>>>
>>>>Thanks in advance,
>>>>
>>>>Wim
>>>Open a Recordset based on the querydef:
>>>
>>>Dim rs As DAO.Recordset
>>>Set rs = qdf.OpenRecordset
>>>rs.MoveFirst
>>>Do Until rs.EOF
>>>thisvar = rs!fieldname
>>>thatvar = rs!anotherfield
>>>rs.MoveNext
>>>Loop
>>>
>>> John W. Vinson[MVP]
>>> Come for live chats every Tuesday and Thursday
>>>http://go.compuserve.com/msdevapps?loc=us&access=public
>>>.
>>>
>>.
>>
>.
>