REPORT BASED ON A COMBO BOX SELECTION

[villica]

Is me one more time. I posted this question before but I was not able to get my report going. Thank you in advance to all of you you tried to help me.<br>
I have a combo box that displays all the tables in my database, every table has field &quot;Initial&quot;. I have also another combo box on the same form that displays all the initials on the table they pick.<br>
My form has a button preview report and under this button I have the following code.<br>
<br>
DoCmd.OpenReport &quot;Crosstab&quot;, acPreview, , &quot;Initials = &quot;&quot;&quot; & Forms!frmlogdate!INITIALS & &quot;&quot;&quot;&quot;<br>
<br>
My problem is that the report does not print any data. I don't have a record source on my report.<br>
Any idea as to what else I can try other than just deleting the report completely.(I did started again and still does not work).<br>
Thank YOu<br>
<br>
Villica<br>
<br>
<p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[elizabeth]

Why don't you have a record source for your report? This command is telling Access to open a report, using the data in the form as a criteria. Does this report work if you open it directly? In other words, this command is designed to apply a selection criteria to a report that already works.

 

[villica]

Hi Elizabet, the report does open up but there are no records, so the selection criteria does not work, but I put a msg box right after and it does show the right information but when the report opens all fields displays as errors. I don't have a record source because the user can pick any table and then compare the initials on the table. Maybe I am going about it the wrong way.<br>
<br>
Thank you for your help. What a headache <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[DougP]

I made a slight change to your code<br>
<br>
Dim Criteria As String<br>
Criteria = &quot;Initials = &quot;&quot;&quot; & Forms!frmlogdate!INITIALS & &quot;&quot;<br>
DoCmd.OpenReport &quot;Crosstab&quot;, acPreview, , Criteria<br>
<br>
I also put an extra variable in so you can see the value of <br>
what you are trying to pass to the report.<br>
<br>
OK<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

Sorry, but I don't get where the record source is specified. It seems to me the user has to select the table name on the form also, and then villica could build a SQL string with that tablename and specify it as the recordsource.

 

[DougP]

villica,<br>
Open your report in design view<br>
2. Bring up properies for the report.<br>
3. In the &quot;Record Source&quot; box (top one in the list)<br>
4. Find the table your interested in.<br>
5. Pick it so it shows in that box.<br>
<br>
Then put my code in your command button on your form.<br>
Sorry I did not read the whole post<br>
Where it said<br>
&quot;I don't have a record source on my report&quot;<br>
It will be quicker if villica does it this way then try to write a recordsource thingy.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

yes, unless villica wants the user to be able to select which table they are interested in at run time.

 

[villica]

Hi Doug and Elizabeth and Jim and everyone else that has try to help me. Doug I can't have a record source based on a table because the user could pick any table so I put this string query under report open and it still does not work .<br>
strrecordsource = &quot;Select * from logfile where Initials = &quot; & Forms![frmlogdate]![INITIALS]<br>
<br>
<br>
Sorry I could not figure if I was supposed to try something.<br>
see below ??<br>
<br>
Hi Elizabet what do you think of the string query I have. <br>
<br>
Then put my code in your command button on your form.<br>
Sorry I did not read the whole post<br>
Where it said<br>
&quot;I don't have a record source on my report&quot;<br>
It will be quicker if villica does it this way then try to write a recordsource thingy.<br>
<p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>

 

[elizabeth]

Sorry I don't have enough time to test this, but here's my idea:<br>
<br>
Say the name of the combobox that displays all the names of the tables in your db is Forms![frmlogdate]!

. Then try this in your query window, while the form is open and the tablename & initals have been selected:<br>
<br>
Select * from Forms![frmlogdate]!

where Initials = Forms![frmlogdate]![INITIALS]<br>
<br>
If it works, cut and paste the SQL into your Recordsource property in your Report.<br>
<br>
<br>