Help! Listbox from SQL statement (1 Viewer)

[connexion]

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

 

[jatfill]

I've never created one recordset from another.. setting your row source to the original string you used to open the recordset should work.

Here's an example of what I use for searches. I test the record count for zero (no results), one record (open the edit form directly), and multiple results (open a listbox form with all the search results)... this is all from one string & the only reason I really use the recordset is to check the results before I direct the user elsewhere... But as you can see all I do is bind the rowsource to the string, I pre-design the listbox as a template with no limits so it already has the right column sizes, etc. For displaying to the user:

Dim db As Database
Dim rs As Recordset
Dim ncount As Integer
Dim strSQL As String

strSQL = "SELECT * from NOTES " & _
               "WHERE SUBJECT LIKE '*' & '" & Me.txtsubject & "' & '*'" & _
               "AND MSG LIKE '*' & '" & Me.txtmsg & "' & '*'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If rs.RecordCount = 0 Then
    MsgBox "Your search did not return any results.", vbOKOnly, "No Records Found"
    GoTo Out
End If

With rs
    .MoveFirst
    .MoveLast
    ncount = .RecordCount
End With

If ncount = 1 Then
    DoCmd.OpenForm "frmNote", acNormal,,"[NOTEID]=" & rs.Fields(0)
    DoCmd.Close acForm, "frmNoteSearch"
    GoTo Out
ElseIf ncount > 1 Then
    DoCmd.OpenForm "frmNoteList", acNormal
    Forms!frmNoteList!lstnote.RowSource = strSQL
    Forms!frmNoteList.Caption = ncount & " Matching Records Found"
     'Save the query text in a hidden field in case
     'the user wants to open the same search results again
     'after they select a record
    Forms!frmNoteList!txtsql = strSQL
    DoCmd.Close acForm, "frmNoteSearch"
    GoTo Out
End If
    
Exit Function

Out:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Hope this helps...

 

[connexion]

Thank you for your effort.

The problem is that if i am going to convert the current queries (which work very but very slowly) to code then i have to create one recordset from the other and then set the row source to a string based on the second recordset.

It just seems to me at the moment that an SQL string won't retrieve records from a temporary recordset in memory?

I can do it if i base the list box row source on an SQL string based on table or query sources, but if the string is based on the temporary recordset nothing is retrieved.

I'm going to find a very tall tree, and a slightly shorter rope!

Vince

 

[Pat Hartman]

Your query is already Dynamic. You may find it to be faster if you save it as a querydef and bind your form to it. Also, using a Having clause is much less efficient than using a Where clause. Only use a Having clause if your selection depends on some aggregated value. Your criteria can be applied to the base table and so you should use Where rather than Having.