Help!
I posted this problem last week without the code behind my question, so i am trying again with an example this week.
I am trying to use an SQL statement that builds a recordset in memory, then perform various lookups etc to either update that recordset or build another, then use another SQL statement to drive a listbox to display the results.
Code that i have so far works fine, the list box "LstCompletes" being driven by "cSummary" which is the basic statement taking data from a query named "QryCommissionCompletesDetail" as follows...
Private Sub cmdCompletesDetail_Click()
Dim cSummary As String
cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"
Me.LstCompletes.RowSource = cSummary
End Sub
BUT!...because i have to sum some of the information as well as perform various lookups to tables for some values required, what i am looking to do is to use the first statement to create a recordset, then play with it, then use a second statement based on the information in this recordset to act as the row source for the listbox.
Private Sub cmdCompletesDetail_Click()
Dim cSummary As String
Dim rstData As DAO.Recordset
Dim cResult As String
cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"
'Set rstData = CurrentDb.OpenRecordset(cSummary, dbOpenSnapshot)
'cResult = "SELECT * FROM rstData;"
Me.LstCompletes.RowSource = cSummary
'Set rstData = Nothing
'rstData.Close
End Sub
Is there something obvious that i am missing?
As long as i can get the principle correct, using "cResult" driven by "rstData" to act as the rowsource for the listbox, i'm sure i can fumble my way through the rest of what i want to do.
Thanks
Vince
I posted this problem last week without the code behind my question, so i am trying again with an example this week.
I am trying to use an SQL statement that builds a recordset in memory, then perform various lookups etc to either update that recordset or build another, then use another SQL statement to drive a listbox to display the results.
Code that i have so far works fine, the list box "LstCompletes" being driven by "cSummary" which is the basic statement taking data from a query named "QryCommissionCompletesDetail" as follows...
Private Sub cmdCompletesDetail_Click()
Dim cSummary As String
cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"
Me.LstCompletes.RowSource = cSummary
End Sub
BUT!...because i have to sum some of the information as well as perform various lookups to tables for some values required, what i am looking to do is to use the first statement to create a recordset, then play with it, then use a second statement based on the information in this recordset to act as the row source for the listbox.
Private Sub cmdCompletesDetail_Click()
Dim cSummary As String
Dim rstData As DAO.Recordset
Dim cResult As String
cSummary = "SELECT QryCommissionCompletesDetail.EmployeeID AS Emp, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy') AS [Date], QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, Sum(QryCommissionCompletesDetail.Value) AS Turnover, Format(Sum(QryCommissionCompletesDetail.Commission), 'Fixed') AS Commission, Format(Sum(([Profit]))/Sum([Value])*100,'Fixed') AS Margin, QryCommissionCompletesDetail.DateOrder "
cSummary = cSummary & "FROM QryCommissionCompletesDetail GROUP BY QryCommissionCompletesDetail.EmployeeID, QryCommissionCompletesDetail.Status, Format([DateOrder],'mmmyyyy'), QryCommissionCompletesDetail.OrderID, QryCommissionCompletesDetail.CompanyID, QryCommissionCompletesDetail.CompanyName, QryCommissionCompletesDetail.DateOrder HAVING (((QryCommissionCompletesDetail.EmployeeID)= '" & Me.cboEmployee & "') AND ((Format([DateOrder],'mmmyyyy'))= '" & Me.CboMonth & Me.CboYear & "'))ORDER BY tblSOP.DateOrder DESC;"
'Set rstData = CurrentDb.OpenRecordset(cSummary, dbOpenSnapshot)
'cResult = "SELECT * FROM rstData;"
Me.LstCompletes.RowSource = cSummary
'Set rstData = Nothing
'rstData.Close
End Sub
Is there something obvious that i am missing?
As long as i can get the principle correct, using "cResult" driven by "rstData" to act as the rowsource for the listbox, i'm sure i can fumble my way through the rest of what i want to do.
Thanks
Vince
Last edited: