A macro that Calls Results of a select QRY into an update QRY. 3

[davo]

I would like a macro to use the results from a select query to make a table.<br><br>I have a an IIF(a,1,0) in the select QRY. I want to call the results of this select QRY to make a new table. How can I code this in VB?<br><br>Thanks

 

[kathryn]

Good morning!<br><br>If I understand you correctly, you want to run a select query in code and then use the results to create a new table.&nbsp;&nbsp;I am assuming that there is a reason why you can't use a Make Table query directly.<br><br>To do this, you will need to use recordsets.<br><br>The code will look something like this:<br><br>dim rstMyQuery as Recordset<br>dim rstMyTable as Recordset<br><br>set rstMyQuery = CurrentDB.OpenRecordset(&quot;Your Query Name&quot<br><br>'make sure that there are records returned<br>if not rstMyQuery.eof and not rstMyQuery.bof then<br>&nbsp;&nbsp;&nbsp;'I'm not sure that you need this, but if <br>&nbsp;&nbsp;&nbsp;'you do, create your table here.&nbsp;&nbsp;Use<br>&nbsp;&nbsp;&nbsp;' the Append method of TableDefs and Fields<br>&nbsp;&nbsp;&nbsp;'collection.&nbsp;&nbsp;Call your new table NewTable.<br>&nbsp;&nbsp;&nbsp;'If your table will always have the same fields<br>&nbsp;&nbsp;&nbsp;'create it manually.&nbsp;&nbsp;Create it in code if you <br>&nbsp;&nbsp;&nbsp;' need to get the field names from the select<br>&nbsp;&nbsp;&nbsp;'query <br>end if<br><br>set rstMyTable = currentdb.openrecordset(&quot;NewTable&quot<br>if not rstMytable.eof and not rstmytable.bof then<br>rstmytable.movefirst<br>rstMyQuery.movefirst<br><br>do until rstmyQuery.eof<br><br>&nbsp;&nbsp;&nbsp;rstmytable.addnew <br>&nbsp;&nbsp;&nbsp;rstmytable!fieldname = rstmyquery!fieldname<br>&nbsp;&nbsp;&nbsp;....etc.<br>&nbsp;&nbsp;&nbsp;rstmytable.update<br>&nbsp;&nbsp;&nbsp;rsttable.movenext<br>loop<br><br><br>Let me know if you need more detail or if I totally missed the point of your question.<br><br><br>Kathryn

 

[davo]

Sorry:
I should be useing the CreateQueryDef( )
Instead of the Openrecordset() I forgot the diff b/w
an action and non action QQRY

Thanks a lot for your help!

Davo

 

[Amiel]

<br>Kathryn it is refreshing to hear from someone else.<br><br>How a about a make table query, ie.<br>&quot;SELECT * From table1 into TableNew&quot;<br><br>... no macros, no additional code.<br><br><br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>