I need to run a query in a on click event and display it in a report with this logic

[kee2ka4]

Hey peeps,

I need some guidance here. I have the following vba code in a On Click event:

Private Sub Command12_Click()
 
Dim intCountryID As Integer
intCountryID = DLookup("CountryID", "qryCompanyFilter", "[CompanyID] = " & Me.Text10)
 
Select Case intCountryID
Case 1
 
 
DoCmd.OpenReport "ReportOne", acViewPreview
Case 2
 
 
DoCmd.OpenReport "ReportThree", acViewPreview
Case 3
 
DoCmd.OpenReport "ReportTwo", acViewPreview
 
End Select
 
End Sub

Now, I want to run a query in the above case statment and display the results in the report. So for example, say Case 1 is true, I want to run a query(which I will add to the code) and display the records in "ReportOne". So something like this:

Case 1
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournament"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportOne", acViewPreview
 
Case 2
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournamentTwo"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportTwo", acViewPreview
 
Case 3
mysql = "SELECT Countries"
mysql = mysql & " FROM qryFootballTournamentThree"
mysql = mysql & " qryFootballTournament.Points =" & Me.Points
DoCmd.OpenReport "ReportThree", acViewPreview

My question is how do I run the query and source the results in a report!

Thanks,
Ket

 

[vbaInet]

Open the report in design view from your form:

DoCmd.OpenReport "ReportTwo", acViewDesign

Set the reports record source:

Reports("ReportName").RecordSource = mysql

Then open the report in preview, as you have it.
Save the changes, or undo the changes after closing.

 

[DCrake]

What are the differences between the three reports, if any, apart from the data that is?

It seams to me you need to refine your code to simplify it and to correct it syntactically.

Dim intCountryID As Integer
Dim RptName As String
Dim RptSQL as string
Dim QryName As String

intCountryID = DLookup("CountryID", "qryCompanyFilter", "[CompanyID] = " & Me.Text10)
 
Select Case intCountryID
   Case 1
    QryName = "qryFootballTournament"
    RptName = "ReportOne"
  Case 2
    QryName = "qryFootballTournamentTwo"
    RptName = "ReportTwo"
  Case 3
    QryName = "qryFootballTournamentThree"
    RptName = "ReportThree"
End Select

           mysql = "SELECT Countries, Points "
mysql = mysql & " FROM " & QryName & " "
mysql = mysql & " WHERE Points =" & Me.Points


DoCmd.OpenReport RptName, acViewPreview

I'll bet you that each of the queries are sourced from the same table. If not then the user will have identical tables one for each tornament. Which is correct?

David

 

[kee2ka4]

Hey guys.. thanks for you replies.. i'll give that a go. David, you are right about simplifying the code. Acually there is gonna be only one query for all the three case condidtions, only the report will be different because each report has different layout! I will post the actuall code if I get stuck.

Thanks,
Ket