dynamic query? 1

[lora]

is there such an animal? i'm sure there is but i'm very new to all this...<br>
<br>
I need to distribute a number of reports based on a persons code. i have a table with a list of codes matching the persons name, these codes populate other talbes, which my reports are based on(of course)...i'm thinking i need to open the 'name' table, scroll through to pull the field code into a variable then run the various queries with the field variable to print the reports...sounds logical enough but can someone point me in the right direction on where to start..any sample code out there anywhere i can use as a start? or give me other ideas if i'm totally off base...<br>
<br>
thanks in advance! <br>

 

[WP]

lora,<br>
<br>
There are two ways of doing this:<br>
<br>
Using the where clause of the report<br>
and Creating and applying an SQL string. Bothe methods require reading a recordset of names.<br>
<br>
Ok,<br>
<br>
The first method :<br>
<br>
Dim dbs As Database<br>
Dim rstName As Recordset<br>
<br>
Set dbs = Currentdb<br>
set rstName = dbs.Openrecordset(&quot;tblNames&quot<br>
<br>
Do Until rstName.eof<br>
dbs.Execute &quot;UPDATE ... WHERE NameCode='&quot; & rstName!NameCode<br>
<br>
Docmd.OpenReport &quot;Report1&quot;,,,&quot;NameCode='&quot; & rstName!NameCode<br>
Docmd.OpenReport &quot;Report2&quot;,,,&quot;NameCode='&quot; & rstName!NameCode<br>
Docmd.OpenReport &quot;Report3&quot;,,,&quot;NameCode='&quot; & rstName!NameCode<br>
rstName.MoveNext<br>
Loop<br>
<br>
rstName.Close<br>
<br>
Here Report1 , 2 and 3 have a recordset containing all names.<br>
<br>
<br>
Second:<br>
<br>
Dim dbs As Database<br>
Dim rstName As Recordset<br>
<br>
Set dbs = Currentdb<br>
set rstName = dbs.Openrecordset(&quot;tblNames&quot<br>
<br>
Do Until rstName.eof<br>
dbs.Execute &quot;UPDATE ... WHERE NameCode='&quot; & rstName!NameCode & &quot;';&quot;<br>
<br>
strSQL=&quot;SELECT ..... WHERE NameCode='&quot; & rstName!NameCode & &quot;';&quot;<br>
Docmd.OpenReport &quot;Report1&quot;<br>
strSQL=&quot;SELECT ..... WHERE NameCode='&quot; & rstName!NameCode & &quot;';&quot;<br>
Docmd.OpenReport &quot;Report2&quot;<br>
strSQL=&quot;SELECT ..... WHERE NameCode='&quot; & rstName!NameCode & &quot;';&quot;<br>
Docmd.OpenReport &quot;Report3&quot;<br>
rstName.MoveNext<br>
Loop<br>
<br>
rstName.Close<br>
<br>
You must have strSQL defined as a global string<br>
<br>
On the On Open event of each report set the recorset to strSQL ( Me.Recordset=strSQL )<br>
<br>
Hope this helps. <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[lora]

i feel so stupid!! but spent enough time trying to be beyond humility...i need some explanation<br>
<br>
What am i updating in : dbs.Execute &quot;UPDATE ... WHERE NameCode='&quot; & rstName!NameCode<br>
<br>
without it, OpenReport doesn't recognize &quot;NameCode'&quot;= &rstName!NameCode as a parameter, so i'm guessing it defines this somehow? i have managed to get the right names in to read in my codes so this will work awesome if the OpenReport would recognize the parameter...<br>
<br>
thanks again.

 

[WP]

the &quot;NameCode='&quot; & rstName!NameCode & &quot;'&quot; should be substituted with the name of the field in your table. I just used Name code as an example . So if your field is just Code then you wold have rstName!Code .<br>
<br>
&quot;Code= ' &quot; & rstName!Code & &quot; ' &quot; <br>
<br>
Note also the single quote immediatlely after the = sign and between the end quotes.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[lora]

i have:<br>
<br>
Set dbs = CurrentDb<br>
Set rstName = dbs.OpenRecordset(&quot;TMT&quot<br>
<br>
Do Until rstName.EOF<br>
dbs.Execute &quot;UPDATE ... WHERE TMCode='&quot; & rstName!TMCode<br>
<br>
DoCmd.OpenReport &quot;Collection Balance&quot;, acViewPreview, , &quot;TMCode='&quot; & rstName!TMCode<br>
etc.<br>
<br>
but get an error: incomplete query clause when i run it so i'm assuming i need to put something into the ... in the UPDATE ... WHERE? <br>
<br>
also i guess i better double check, when you said the reports had to have a recordset containing all names does that mean that the record source property of the report is set to a database or query containing all the TMCodes? <br>
<br>
thanks for your patience, you're literally a job saver!

 

[WP]

Lora,<br>
<br>
The code I sent originally was not meant to be taken literally and was only example code.<br>
<br>
In your first posting I thoght you had queries to run based on the name code. The line dbs.Execute &quot;UPDATE ... WHERE NameCode='&quot; & rstName!NameCode & &quot;';&quot;<br>
<br>
is only an example of that where you would enter the update SQL string. If you do not require it the delete it.<br>
<br>
Yes you are right about the reports record source.<br>
<br>
Let me see the complete code.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[lora]

i finally got it to work...i needed to add & &quot;'&quot; to the end of the line:<br>
<br>
DoCmd.OpenReport &quot;Collection Balance&quot;, , , &quot;tmt.tmcode='&quot; & rstName!TMCode & &quot;'&quot;<br>
<br>
Thanks so much for your help again, i never would have gotten this without a jump start!