Change query of data source (1 Viewer)

Kayleigh

Member
Local time
Today, 05:00
Joined
Sep 24, 2020
Messages
706
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:
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:00
Joined
May 7, 2009
Messages
19,169
maybe the Report need to be close and re-open again.
 

Ranman256

Well-known member
Local time
Today, 01:00
Joined
Apr 9, 2015
Messages
4,339
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.

Code:
sub cboBox_afterupdate()
  me.recordsource = cboBox
end sub
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:00
Joined
Jul 9, 2003
Messages
16,245
Did you know strSQL1 & strSQL2 are identical?
 

Kayleigh

Member
Local time
Today, 05:00
Joined
Sep 24, 2020
Messages
706
Thanks for your suggestions. Yes SQL did need to be changed.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:00
Joined
Feb 19, 2013
Messages
16,553
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

Nifty Access Guy
Staff member
Local time
Today, 05:00
Joined
Jul 9, 2003
Messages
16,245
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...
 

Users who are viewing this thread

Top Bottom