Altering RecordSource property

[Peter Bellamy]

Can anyone help me solve this?
A form takes some user entered data and provides a button to open a report in preview.

I want the report to get its record source dynamically, from the form (an SQL string).

If I run the code with report closed it errors saying it isn't open or misspelt.
If I run the code with it open it says it can't alter an open report!

Ugh!!

 

[RuralGuy]

I take it you can not satisfy your needs with the WhereCondition of the OpenReport command?

 

[Brianwarnock]

Open the report in design view alter the recordsource and then open in preview or normal

Brian

 

[Peter Bellamy]

Thanks for your replies.
And congratulations Liverpool celebrating Capital of Culture tonight!

The code is on a db at work so this is from memory

Report has Record source of a query by default
Form button vba is currently as follows:
Assign user entered data
Create SQL string from a template plus inputed variables plus a changing variable from a loop in the code (the plan is for this to change and enable multiple reports based on different variables)
Open the report, in preview
Alter the record source property to the created SQL string in a With loop.
Move on to the next variable to produce the next report and go through the process again. (total of 8 reports)

I will certainly try openeing in design mode however, Msoft's help on recordsource says it should requery when it is open??
Peter

 

[Peter Bellamy]

Thanks Brian opening in design view worked !

Cheers

 

[Peter Bellamy]

RG, I think the Where condition will not allow me enough options but I will have a look at it
Thanks

 

[Peter Bellamy]

It seems that some report properties can only be set in vba in design view and others only in preview. Are these 'rules' detailed somewhere?

Altering the recordsource in design view then open in preview works fine, however...
I am also taking the user input to provide a heading and discount. Setting the heading can only be done in preview but it does not appear unless I return the report to design mode then return to preview (manually) !!

Any suggestions please?

Cheers

 

[Brianwarnock]

I'm not sure that ACCESS has any rules but rather works in mysterious ways its wonders to perform.

I've never done what you are trying to do but it does seem odd that you cannot do it programmatically.

Brian

 

[Peter Bellamy]

I was afraid of that!

I found a useful link that describes exactly what I want to do and the example there works perfectly. I am even more puzzled now!

Cheers
http://www.fontstuff.com/access/acctut19.htm