Change data source

[bosk00]

I have many state reports that have as a record source a query whos data is entered in a form. This allows the user to select and print an individual client's informatin for a specific state. There will be times when there will be a need to print these reports for every client in the state. I have created a query that will pull the information. I can manually go in and change the record source for the report and it will then print them all. I forsee an additional button on the form to print all clients from a state, so do I need to created a seperate report for each state for all clients or can the record source be changed on the fly as part of the on-click event. Hopefully this is clear enough. Thanks

Alan
Senility at its finest

 

[jebry]

Hi Alan!

You can change the record source of the report in the Report's Load event:

Me.RecordSource = "SomeSQL"

You can use a form's control to decide this:

If Forms!YourForm!YourControl = "Data" Then
Me.RecordSource = "SomeSQL"
End If

Of, if you have a variety of possibilities you can use:

Select Case Forms!YourForm!YourControl
Case "Data"
Me.RecordSource = "SomeSQL"
Case "OtherData"
Me.RecordSource = "OtherSQL"
Etc.
End Select

hth


Jeff Bridgham
bridgham@purdue.edu

 

[scriverb]

You can use the same saved query and just change the WHERE portion of the SQL depending upong what you want to print. here is an example of modifying the .SQL property of a query with new code. Modify the string cancatenation to fit your needs.

Dim db as DAO.Database
db.QueryDefs("[i][red]yourqueryname[/red][/i]").SQL = Mid$(db.QueryDefs("[i][red]yourqueryname[/red][/i]").SQL, 1, Instr(1,db.QueryDefs("[i][red]yourqueryname[/red][/i]").SQL, "WHERE")+ 5) & . . .[I][blue]cancatenate on new expression to select your records[/blue][/i] & "ORDER BY ...[I][blue]order by fields[/blue][/i]
db.close

Now that is cancatenated into this .SQL property of your query will vary depending upon what you want the query to select. So this depends upon your selections on the form. This code is not run in the report itself but rather behind a command button on the selection form.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[bosk00]

Thank You both for your responses. Now to sound like a complete moron.

Jeff, When I open the report in design view and select properties, I have an "On Open" event but no "On Load" Event. Same thing? I hate to assume anything. Also I have saved queries, where you are saying "SomeSQL" could I just copy my exsisting queries SQL view and paste it in there?

Bob, I grasp where to put your code, but the what is the problem. In your code at the ...cancatenate etc... would I just paste in my queries SQL View? Attempting to understand both.

Alan
Senility at its finest

 

[jebry]

Hi Alan!

My mistake, I forgot that a report doesn't have the Load event. You should be able to use the Open event. You can use the SQL copied and pasted from your queries. You should also be able to use the name of the queries and get the same results.

One thing to be careful about is to make sure that the queries all contain the fields that the report will be looking for.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[scriverb]

Yes, you would paste in the appropriate SQL code behind the WHERE statement. So if you wanted all records to be included you would put the expression to select them all, otherwise if you wanted to selective choose only a subset then you cancatenate the expression to select only those required at this time.

You see you are actually taking everthing from the "Select . . . . ." through the "WHERE " then cancatenating the expression for selection, then cancatenating on the "ORDER BY . . . expression".

Really easy to do and can be modified at any time you want to change it. This is a great way to only one query for a report but modify it to meet your needs for different subsets of the recordset.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[nimarii]

scriverb,

I'm trying to use your code to change my query sql string, but I'm getting an error:
run-time error 3129: Invalid SQL statement; expected 'DELETE', 'INSERT', 'SELECT', or 'UPDATE'.

Here's the original sql of the query I need to use:

SELECT ProVision.Status, ProVision.RFPnumber, ProVision.ReadServices, ProVision.AddressA, ProVision.CityA, ProVision.StateA, ProVision.AddressZ, ProVision.CityZ, ProVision.StateZ, ProVision.CustMRC, ProVision.CustNRC, ProVision.CustomerID, CustomerInformation.*, ProVision.CreateDate, Now()-[CreateDate] AS CalcAge
FROM CustomerInformation INNER JOIN ProVision ON CustomerInformation.CustomerID = ProVision.CustomerID
WHERE (((ProVision.Status)="1" Or (ProVision.Status)="2" Or (ProVision.Status)="3"));

And here's the code I'm using behind the commandbutton:

Private Sub ViewReport_Click()
Dim db As DAO.Database
    Set db = CurrentDb()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim ReportName As String
Dim AssignedTo As String
Dim CustomerName As String
Dim Agent As String

ReportName = Nz(Me.ReportName.Value, "null")
AssignedTo = Nz(Me.AssignedTo.Value, "null")
CustomerName = Nz(Me.CustomerName.Value, "null")
Agent = Nz(Me.Agent.Value, "null")
db.QueryDefs(ReportName).SQL = Mid$(db.QueryDefs(ReportName).SQL, 1, InStr(1, db.QueryDefs(ReportName).SQL, "WHERE ") + 5) & AssignedTo = " & AssignedTo"
db.Close
DoCmd.OpenReport ReportName, acViewPreview
End Sub

do you know where i'm going wrong?

 

[scriverb]

Change the following red code:

Mid$(db.QueryDefs(ReportName).SQL, 1, InStr(1, db.QueryDefs(ReportName).SQL, "WHERE") + 5) & AssignedTo = " & AssignedTo"

You had an extra space behind the "[red]WHERE[/red]" so with the + 5 which did not leave a space between the WHERE and the AssignedTo.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Sorry but you need to change the 5 to a 6.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[nimarii]

thanks a ton, its working now