report based on custom query

[fergcu]

Hi, hopefully someone can help me with this - being relatively new to access, i might be misunderstanding some basic principles...

I have created a query that is based on a number of list boxes in my main form. One of the list boxes controls the headings/fields that is displayed in the query. All code (inc SQL) is generated on a single button click event (ie the query has no embedded sql, it is all dependant on the options from the list boxes).

This works absolutely fine, however i am unsure how i can translate this over to a report form - i obviously cant base the report on the query, because it is constantly variable (ie one report might have just two fields (name and id), and the next report might need to contain upto 30 fields).

Can someone point me in the right direction as to how to go forward with this?

Any help would be gratefuly received...

Many thanks,

Fergus.

 

[pbaldy]

You might try something like this, as the basic premise is the same:

http://support.microsoft.com/kb/328320/en-us

 

[fergcu]

thanks paul - i'll have a gander at that....hopefully it will clear it up in my head!

cheers,

Ferg.

 

[fergcu]

ok, i have tried and tried with this, but dont seem to be getting anywhere.

I'm going to explain what i need to do again, in case my first description didnt work:

I used an example called "Select Fields for Query" to do a neat little trick. This allows the user to create custom queries on the fly, by selecting the field columns that they want to display. For instance, from my employee database, i could display just the name and employee ID number; or I could run a full query with the Name, ID, start date, end date, wage etc etc. Now this is all very nice, but what i would LOVE to be able to do is create reports on the fly in the same manner (just be able to select which columns i want to display).

If anyone has any ideas how to do this, I would be so grateful.

Many thanks in advance,

Fergcu.

 

[pbaldy]

I understand what you're trying to do, and it's not simple. The main problem you have is that the report won't be bound to the same data every time, so you need to dynamically assign control sources to the controls on the report. That's what the code in that link does. I suppose one way around that would be to bind controls to "Field1", "Field2" etc, and code your query so it selects Name AS Field1, ID as Field2 type of thing, but you'd also have to account for the user selecting fewer fields than on the report (since a control bound to a field not existing in the query would kick out a parameter prompt).

Further, you're going to have issues with the formatting and widths of the controls. If the first control on the page might have a long name field one time it's run and a short ID field the next time, you would want it to be different widths each time. That also implies moving the controls so they remain in their appropriate relative positions to each other. I suppose you could get around that by forcing certain things on the user, but that doesn't sound like what you want.

 

[fergcu]

ok, thanks paul - I was kind of hoping that you might have misunderstood my aim, and that there was a simple function built into access reporting to do this sort of thing. I think I'm going to resign myself to the fact that this is way over my current access capabilities.

Unless anyone can think of any other possible way, i'll call it a day, and create a number of set reports which will unfortunately be quite limiting.

Thanks again,

Fergcu.

 

[fergcu]

ok, thought of a possible fix.

Is it possible to use VB to launch the report wizard, and fill in which details it needs (ie formatting, where it is geting the data from (my dynamic query))?

this could be an ugly but possible way to solve it.

fergcu.