Change query of data source (1 Viewer)

Krayna

Member
Local time
Today, 11:51
Joined
Sep 24, 2020
Messages
379
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

once i caught a fish alive...
Local time
Today, 18:51
Joined
May 7, 2009
Messages
13,201
maybe the Report need to be close and re-open again.
 

Ranman256

Well-known member
Local time
Today, 06:51
Joined
Apr 9, 2015
Messages
3,889
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, 11:51
Joined
Jul 9, 2003
Messages
13,221
Did you know strSQL1 & strSQL2 are identical?
 

Krayna

Member
Local time
Today, 11:51
Joined
Sep 24, 2020
Messages
379
Thanks for your suggestions. Yes SQL did need to be changed.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Feb 19, 2013
Messages
13,093
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, 11:51
Joined
Jul 9, 2003
Messages
13,221
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