Change query of data source

[Kayleigh]

I have a toggle button to change queries behind data source of a report but doesn't seem to be changing the data - any ideas how to resolve?

Here is my code:

Private Sub cmdDataSource_Click()
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim qd1 As DAO.QueryDef
Dim qd2 As DAO.QueryDef
Dim db As DAO.Database
Set db = CurrentDb()
Set qd1 = db.QueryDefs("qrySumClientValBase")
Set qd2 = db.QueryDefs("qrySumClientValBaseAll")

Dim x As Boolean
Debug.Print Me.cmdDataSource.Caption
If Me.cmdDataSource.Caption = "All Sales" Then
Me.cmdDataSource.Caption = "All Enquiries"
Me.lblHeader.Caption = "Sales Source Report by Enquiry Date"
x = True
Else
Me.cmdDataSource.Caption = "All Sales"
Me.lblHeader.Caption = "Source Report by Sold Date"
x = False
End If

strSQL1 = "SELECT qryOrderExtended.fldOrderID, qryOrderExtended.fldAClientID, qryOrderExtended.cfGrandTotal, tblClients.fldCHDYHAUID, qryOrderExtended.fldOHDYHAU " & vbCrLf & _
"FROM (qryOrderExtended INNER JOIN lkptblOrderStatus ON qryOrderExtended.fldOStatusID = lkptblOrderStatus.fldOrderStatusID) INNER JOIN tblClients ON qryOrderExtended.fldAClientID = tblClients.fldClientID " & vbCrLf & _
"WHERE (((lkptblOrderStatus.fldOSSort)>45) AND ((qryOrderExtended.fldOStatusID)<>12) AND ((tblClients.fldCTradeRetailID)=[Forms]![frmdlgSalesRpt]![cmbTradeRtl]) AND ((qryOrderExtended.fldOSoldDate)>=[Forms]![frmdlgSalesRpt]![txtFrom] And (qryOrderExtended.fldOSoldDate)<=[Forms]![frmdlgSalesRpt]![txtTo]));"

strSQL2 = "SELECT qryOrderExtended.fldOrderID, qryOrderExtended.fldAClientID, qryOrderExtended.cfGrandTotal, tblClients.fldCHDYHAUID, qryOrderExtended.fldOHDYHAU " & vbCrLf & _
"FROM (qryOrderExtended INNER JOIN lkptblOrderStatus ON qryOrderExtended.fldOStatusID = lkptblOrderStatus.fldOrderStatusID) INNER JOIN tblClients ON qryOrderExtended.fldAClientID = tblClients.fldClientID " & vbCrLf & _
"WHERE (((lkptblOrderStatus.fldOSSort)>45) AND ((qryOrderExtended.fldOStatusID)<>12) AND ((tblClients.fldCTradeRetailID)=[Forms]![frmdlgSalesRpt]![cmbTradeRtl]) AND ((qryOrderExtended.fldOSoldDate)>=[Forms]![frmdlgSalesRpt]![txtFrom] And (qryOrderExtended.fldOSoldDate)<=[Forms]![frmdlgSalesRpt]![txtTo]));"

strSQL3 = "SELECT DISTINCT qryOrderExtended.fldOrderID, qryOrderExtended.fldAClientID, qryOrderExtended.cfGrandTotal, qryOrderExtended.fldOHDYHAU, tblClients.fldCHDYHAUID " & vbCrLf & _
"FROM qryOrderExtended INNER JOIN tblClients ON qryOrderExtended.fldAClientID = tblClients.fldClientID " & vbCrLf & _
"WHERE (((qryOrderExtended.fldOCreated)>=[Forms]![frmdlgSalesRpt]![txtFrom] And (qryOrderExtended.fldOCreated)<=[Forms]![frmdlgSalesRpt]![txtTo]) AND ((tblClients.fldCTradeRetailID)=[Forms]![frmdlgSalesRpt]![cmbTradeRtl]));"

strSQL4 = "SELECT DISTINCT qryOrderExtended.fldOrderID, qryOrderExtended.fldAClientID, qryOrderExtended.cfGrandTotal, qryOrderExtended.fldOHDYHAU, tblClients.fldCHDYHAUID, qryOrderExtended.fldOSoldDate " & vbCrLf & _
"FROM qryOrderExtended INNER JOIN tblClients ON qryOrderExtended.fldAClientID = tblClients.fldClientID " & vbCrLf & _
"WHERE (((qryOrderExtended.fldOCreated)>=[Forms]![frmdlgSalesRpt]![txtFrom] And (qryOrderExtended.fldOCreated)<=[Forms]![frmdlgSalesRpt]![txtTo]) AND ((tblClients.fldCTradeRetailID)=[Forms]![frmdlgSalesRpt]![cmbTradeRtl])) OR (((tblClients.fldCTradeRetailID)=[Forms]![frmdlgSalesRpt]![cmbTradeRtl]) AND ((qryOrderExtended.fldOSoldDate)>=[Forms]![frmdlgSalesRpt]![txtFrom] And (qryOrderExtended.fldOSoldDate)<=[Forms]![frmdlgSalesRpt]![txtTo]));"

If x = True Then
qd1.SQL = strSQL1
qd2.SQL = strSQL3
Else
qd1.SQL = strSQL2
qd2.SQL = strSQL4
End If


Set qd1 = Nothing
Set qd2 = Nothing
Set db = Nothing
Me.RecordSource = "qrysumorderval"
Me.Requery
End Sub

 

[arnelgp]

maybe the Report need to be close and re-open again.

 

[Ranman256]

you could do this with just queries and no code.
have a table with Caption, Qry to fill a combo. (cbo is bound to the QRY field)

User picks the Caption in the combo box , then it assigns the query to the form/rpt.

sub cboBox_afterupdate()
  me.recordsource = cboBox
end sub

 

[Uncle Gizmo]

Did you know strSQL1 & strSQL2 are identical?

 

[Kayleigh]

Thanks for your suggestions. Yes SQL did need to be changed.

 

[CJ_London]

just as an aside, when building sql strings in this way

a) you don't need to include a linefeed
b) I would advise put the space you have at the end of each section at the beginning of the next section section instead, Make it easier to see you have included it

also advise you indent code, makes it much easier to read

So far as I can see, you assign the sql to your two queries, but then what? you assign a different query to (presumably) your report

you might be better using

db.QueryDefs("qrySumClientValBase").SQL = strSQL1

etc

rather than

qd1.SQL = strSQL1

 

[Uncle Gizmo]

I would advise put the space you have at the end of each section at the beginning of the next section section instead,

I usually put hem at the end, but putting them where you can see them easily makes sense! I might have to change my ways...