Dynamic Report Creation using SQL

[BitCounter]

I have a report created and in my program I am allowing the user to select a record for printing based on student ID.&nbsp;&nbsp;Therefore my DataMember property is an SQL statement that contains a variable representing the ID.&nbsp;&nbsp;It is as follows:<br><br>With DataReport2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.DataMember = &quot;SELECT * From [Information Sheet] WHERE LRID = '&quot; & varID & &quot;'&quot;<br><br>This works okay, but whenever I pull the report up, <b> nothing prints </b> in the detail section of the report.&nbsp;&nbsp;<b><font color=red>Not even labels! </font></b> I have attempted to set the DataMember property of each object within the report but that fails as well. <b><i>(I get an error message stating &quot;Object required&quot</b></i>&nbsp;&nbsp;How do I build a report based on dynamic variables?&nbsp;&nbsp;Any help would be greatly appreciated!<br>

 

[UncleChuck]

I've been doing this in Access97 and VBA.&nbsp;&nbsp;I use a user form with all the variables I need in text boxes and then in my event handler I get my form level variables (textboxes) into local variables and then plug them into my SQL statement.&nbsp;&nbsp;It might look like: intID = Forms!frmStudentLogOn!txtStudentID.&nbsp;&nbsp;Then the SQL statemet would look like: &quot;Select * FROM tblGrades WHERE tblGrades.UserID = &quot;&nbsp;&nbsp;& intID & &quot;&quot;&quot;.&nbsp;&nbsp;Using the debug window to print out the SQL statement has proven very helpful to me.&nbsp;&nbsp;It should print out just like it would look in an SQL statement eg. quotes and everything.<br>&nbsp;&nbsp;I get into most trouble when I'm plugging variables into the SQL statement.&nbsp;&nbsp;Integers require no quotes and strings require single quotes etc.&nbsp;&nbsp;When something is wrong I sometimes get an error message and other times I just get no records!&nbsp;&nbsp;groan.&nbsp;&nbsp;It nearly always seems to be in the variable format in the SQL statement if that helps.<br><br>PS.&nbsp;&nbsp;When it just gets to frustrating I try another approach to the problem.&nbsp;&nbsp;Sometimes It's just the way I'm thinking about it that gets me into trouble.&nbsp;&nbsp;)

 

[DannyB]

Define a data connection with the desired info, associate the reports data source with the aforementioned data connection. At runtime you've got a property like rs[data connection] with the desired data. Use the watch window to see how VB, data report and data connection all work together.<br><br>On a last resort, define a temporary table with the desired info and simply display that.<br><br>Good luck and be patient. <p> <br><a href=mailto: > </a><br><a href= > </a><br>