Desperate help needed Report from a SQL statement

[Gavuk]

Hiya all,

Sorry about this a bit desperate here!! I have got a form that fires off a SQL statement that populates a list view. Basically i need the data from the list view to be transfered onto a report how do i set the form up to dynamically take the SQL as its feed?? At the moment they press a button and i get my report up but i cant appear to get the fields populated with the SQL strings data.

Thank you

 

[scriverb]

If you store that SQL statement in a Global variable then you can update the Record Source property of your report in the On Open event procedure of the Report.
DATABASE MODULE:
Global gblvarSQLStatement as string

STORE SQL IN GLOBAL VARIABLE:
gblvarSQLStatement="SELECT Customers.CusteomerID, Customers.Name FROM Customers ORDER BY Customers.Name DESC;"

REPORTS ON OPEN EVENT PROCEDURE:

Me.RecordSource = gblvarSQLStatment

Let me know if you need more assistance with this.



Bob Scriver

 

[Gavuk]

Thank you bob gave taht a shot it appears to work ok but how can i avoid it asking for parameters everytime i want it to view the report or print it?? I know it is something to do with the way it links to the SQL statement:

Any (and i mean any!!! advice is much appreciated- just had to demo it!!!!)

Heres my sql (i bit complictaed and messy i know).

SELECT DISTINCTROW tblMPDetails.Lastname & ' ' & tblMPDetails.Firstname AS Name, tblMPDetails.PoliticalParty AS Political_Party, tblVoteDetails.Name AS Vote_Name, tblMPVote.Response FROM tblVoteDetails INNER JOIN (tblMPDetails INNER JOIN tblMPVote ON tblMPDetails.MpID = tblMPVote.MPID) ON tblVoteDetails.VoteID = tblMPVote.VoteID WHERE (((tblMPVote.Response)="Y&quot AND ((tblVoteDetails.VoteID)=1) AND ((tblMPVote.AmendmentID)=0) AND ((tblMPDetails.Lastname)="Hackett&quot AND ((tblMPDetails.Firstname)="Sarah&quot AND ((tblMPDetails.PoliticalParty)="Green&quot) ORDER BY tblVoteDetails.Name(tblVoteDetails.VoteID=tblMPVote.VoteID);

 

[scriverb]

If it is asking for parameters then I believe it has to do with the report itself. There must be controls or code that is referencing query names that are non-existent in your SQL. After looking over your query I don't see anything that wouldn't be know by the query so the query isn't the culprit. Take the SQL that you provided and paste it into a new queries SQL window and run it. If the query runs without prompts then we know for sure that the report is asking for the user to enter data for controls on the report.

You should have a pretty good idea about where to look as the prompts ask for specific name values. Let me know what you find.

Bob Scriver

 

[Gavuk]

Worked in the query window, but it still keeps asking for parameters i just cant see what can be causing it to keep on asking, there must be something on the report i agree i just cant see anything.

 

[Gavuk]

Scratch that BOB just done it!!!!! At last! it was the sorting and Grouping within the Details section. Happy at last, ten minutes after i had to explain it away in yet another demo but its done. Thank you for your help Bob (kept me sane last night!!)

Cheers again (owe yer a drink next time yer in London)

Gav

 

[scriverb]

Glad to help. If my wife and I travel to UK I will certainly look you up.

Bob Scriver