The solution I've implemented is to define the query in the same button that calls the report, using the same staff criteria. Then I put in an unlinked subreport that reads that query. Seems to work well!
Thanks for working with me on this.
Whitney
wjfraser
Last Activity: Dec 6 '06, 12:14 AM
Joined: Oct 16 '06
Location:
-
That does make sense. But the subreport is in the report header, not the group header.
This did not work - it stopped printing the bio altogether....Leave a comment:
-
I left out some of the process because I thought it would confuse things...but I think I need to lay it all out. The first time I tried to make this subreport, Access truncated the bios because they were involved with the SELECT DISTINCT query. So now I run the staff list (one or more staff members listed for each project) through a SELECT DISTINCT query first, then match the resulting list with the bios from a master staff list. Like this:...Leave a comment:
-
Yes, the Group Header is based on ProjectStaff, but I don't think the grouping has a direct relationship with the subreport.
When I look at the subform properties, I see
Link Child Fields......Pro jectStaff
Link Master Fields...Projec tStaff
Is there another place to look for master/child relationships?...Leave a comment:
-
The report is a list of projects. The subreport is a list of bios for all staff who have worked on the projects, so it needs to occur just once, at the beginning of the report. After a bit more experimentation , it looks like the subreport is only displaying the first record (no matter how short or long the bio is - so, even if there is space left on the page, it won't display any more records BUT if the first record is more than a page long, the...Leave a comment:
-
Multiple page subreport cuts off in report header
Hello,
I have a report with a subreport in its header. When I run the subreport by itself, it shows multiple pages, correctly. When I view the main report, the subreport prints to the bottom of the first page and does not go on to display the rest of its records - the records of the main report begin on the second page. I have tried setting the CanGrow property of everything to "yes" and experimented with lots of the other properties.... -
For the future reference of anyone who encounters this same problem, I have gotten the query to work properly now, by building it completely in the code behind the button calling the query.
...Code:Set MyDB = CurrentDb() strSQL = "SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords] " & _ "FROM tblSubProjectKeyWords " & _ "WHERE (([ProjectKeyWords]="
Leave a comment:
-
Well, thank you for trying to work on it with me. Is there any other way to conceptualize a solution to this? The basic issue is that I want to pass selected items from a list box (key words) as criteria to end up with a list of projects using those key words. Each project has a number, and each project number can be associated with one or more key words. So, I have been using a form to let the user select the key words and then constructing...Leave a comment:
-
How frustrating. I pasted both of these statements into my query's SQL View, with the same results as ever (the get_global one didn't return anything but the hard-coded version worked fine)....Leave a comment:
-
It shows identical values:
'Air Quality' - 'Air Quality'...Leave a comment:
-
Same result - not working....Leave a comment:
-
It is in a module....Leave a comment:
-
My apologies - I think I misunderstood your question. When you said query design, I thought you meant the Query Design window. I am using the SQL View of my query to work with it, not coding it elsewhere....Leave a comment:
-
Using code....Leave a comment:
-
Sure thing. This is the original that I started with:
This is the one that doesn't use get_global:Code:SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords] FROM tblSubProjectKeyWords WHERE (([tblSubProjectKeyWords].[ProjectKeyWords]=get_global("KeyWordsSelected")));
...Code:SELECT [tblSubProjectKeyWords].[ProjectNumber], [tblSubProjectKeyWords].[ProjectKeyWords]
Leave a comment:
-
Thnks for sticking with me on this. Just to make sure I got what you said - the only thing you added was "As String" to the end of the first line, is that correct?
It still doesn't work though. Plus, per a different suggestion above, I wrote a different version where I didn't use the get_global function at all (instead storing the value in a text box), and it still didn't work, but the value came up correctly in the text...Leave a comment:
-
Thanks for your continued help. The query you gave below with "*"s doesn't work either.
The data type of the field ProjectKeyWords is text.
get_global should be returning a string.
This is the get_global function:
...Code:Public Function get_global(G_name As String) ' property of blueclaw-db.com ' Select Case G_name Case "KeyWordsSelected"Leave a comment:
-
Do I have to declare tmpValue anywhere or take any other preparatory steps? When I try this, it just pops up a box asking for a value for tmpValue like it is a simple parameter query.
Thanks,
Whitney...Leave a comment:
-
I tried this second suggestion and it seems to work the same way as the msgBox. It prints the correct value in the text box on the form, but when I paste the same reference into the SQL query it doesn't work.
This puts the global value into the text box (works!):
[Forms]![frmKWtoStaff]![txtHoldSelected] = GBL_KeyWordsSel ected
and this is the WHERE statement (doesn't work!):
WHERE (([tblSubProjectKe yWords].[ProjectKeyWords]=[Forms]![frmKWtoStaff]![txtHoldSelected]));...Leave a comment:
-
Thanks for the idea. Unfortunately it did not work. I do append the single quotes in the variable itself, so literally the variable contains the value
'Air Quality'
with the single quotes already on. Could they be stripped somehow in the parsing process?
I will look into your second suggestion too, thanks.
Whitney...Leave a comment:
No activity results to display
Leave a comment: