Subreport on form seach issues

[mike02]

Hey,



I have a subreport set up to search on CompanyID (selected from combobox on Form,

I have tried setting a where clause in my reports query under properties and I have tried searching in the Query like in subforms. but to no prevail it doesnt work. Wondering how this is done. I have tried searching ways online but all i can find is ways of opening the report from the form. I know how to do that with the search, but I want it to be in the subreport on the form.

Thanks.

[rpeare]

In the CRITERIA of your COMPANYID field in the query driving your report your syntax for pulling the COMPANYID field from a form is:


[forms]![frmTest]![CompanyID]

Where frmTest is the name of your form and [CompanyID] is the name of the field on that form that is defining the search.

[mike02]

I have been doing that, yet, it still doesnt work, Access says it doesnt recognize it, so i try and ad the parameter with the ribbon portion, and access still doesnt recognize it

[rpeare]

can you provide a sample of your database (just the part that's not working and the sample tables that go with it) in something prior to access 2010 and I can take a look.

[mike02]

I re did everything, to try again before I take more of your time up, and now access doesnt provide an Error, but it leaves the subreport blank (white Screen), any ideas on that? By the way, there is records there, when I run a cmd, open report search .. opens the report with a record. but it wont veiw in subreport.

Thanks

[rpeare]

you likely have to do a subform requery to show the results if you change the rowsource.

[mike02]

I have a cmd to requery after update/

[rpeare]

So that leads back to asking for a sample of your db , something prior to 2010 and just the part that's not working

[mike02]

Ok, Ill put a template together.

do you know why access doesnt recognize the control source of my report when I have the parameter set? Whenever I set the parameter in the query on the report and then view my report in design view, It doesnt recognize the control source. Also, Sometimes the report prompts a parameter setting when veiwed in form view, and sometimes it just views blank. I will attach a copy when i have one put together~!

Thanks.

[rpeare]

without seeing what you did I can't say.

[mike02]

The file I have is not compatible with Access 2003, and i cant modify the data in these tables to allow it to be.

I think I have figured out the issue from more research.

My report is based of a query. The query gets it information from a Crosstab query. I am now getting the error set ColumnHeading. I am confused as what they mean by this, and i cant figure it out. On the Cross Tab query I have it set, as a column heading, and row heading and value. But on the regular query you wouldnt set the column heading?

any ideaS?

[rpeare]

you must be using multi value fields or calculated values in your tables, something you should probably avoid at all costs, they lead to more trouble than they're worth.

Does your crosstab query work properly on it's own. That is if you run it by itself do you get the results you want?

If you do and the crosstab is using a criteria you will likely have to set a parameter if you then use the crosstab query as part of a secondary query.

The parameter should be the same value as the criteria, for instance if you are referencing a field on a form [form]![formname]![fieldname] as part of your criteria, then in the paramters you would type it exactly as it appears in your criteria and define the data type and it should then allow you to run your secondary query using the crosstab query for information.

[mike02]

Yes the cross tab query runs perfectly. Slower then Id like but its fine.
I have it searching when i put it in report view, but when I put it as a subreport, I get the same error again, set querys Column headings property.
Which I did.

[rzw0wr]

Mike,
I have never used a Crosstab query, However I did a Google search.
"Access CrossTab query in report"
I found several hits.
The following made very little sense to me but may help you.

http://blogs.office.com/b/microsoft-...n-reports.aspx

Dale

[mike02]

Hey,

I took a look at the link, but i still cant get it to work,

I created a quick example for you to see. It is attached.