Output query to list box

[shabbaranks]

Hi,



Im trying to output a query to a listbox the problem being if I set the source of the listbox to the query the query runs regardless of the error so the incorrect results are displayed. Is there a way to output a query using VBA?

Thanks

[orange]

What is the query? And what exactly is the error you mention?

[shabbaranks]

Its not so much an error, there are 2 queries which run depending on the selection from a combo. If the user selects all then the all query runs and if a user selects individual then the individual query runs. There is only 1 listbox on the form which I am trying to output both (obviously not at the same time) queries to.

The only way I have managed to output to the listbox is to use the wizard, but Im guessing this isnt the correct way to go about this for this perticular task?

thanks

[orange]

So you have a combo, with options for all or one of several individuals.
Once you make your combo selection (afterUpdate event), then you change the row source of the list box to the ALL query or the INDIVIDUAL query. Is that correct?

[shabbaranks]

Im getting there, but the queries arent outputting to the listbox - any ideas please?

Option Compare Database
Private Sub TotalHoursOk_btn_Click()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim sProjectRef As String
    Dim sTotalhours As String
    Dim MySQL As String
    Dim sGroupID As String
    Dim sUserID As String
    Dim sFormUser As String
    sFormUser = Me.CurrentUser
    sUserID = Forms!TimesheetForm!LoggedInUser.Value
    sGroupID = DLookup("DepGroupID", "UserNames_tbl", "sUser='" & sUserID & "'")
    If Me.ProjectRef_txtbox = "" Or IsNull(Me.ProjectRef_txtbox) Then
        MsgBox "Please Enter a Project Number"
    Else
        If Me.TotalHours_Combo = "" Or IsNull(Me.TotalHours_Combo) Then
            MsgBox "Please Select from the drop down list"
        End If
       Select Case Me.TotalHours_Combo
       Case "All Employees"
       If sGroupID = "2" Then
        Me.Totalhrs_listbox.RowSource = ("Select * from qryTotalHrsALL")
        
        '*******************
        'DoCmd.OpenQuery "qryTotalHrsAll"
              'MySQL = "Select * From TimesheetTable WHERE [ProjectRef] LIKE '*" & Me.ProjectRef_txtbox & "*'"
               ' QDef (MySQL)
                'Call TotalHours("qryTotalHrsAll", MySQL)
                'Totalhrs_listbox.RowSource = MySQL
                
        '***********************
                                       
            Else
               MsgBox "You are only allowed to query your own hours, change your selection to current user", vbInformation
            End If
           Me.TotalHours_Combo.SetFocus
        Case "Current User"
        Me.Totalhrs_listbox.RowSource = ("Select * from qryTotalHrsCur")
            
        '*************************
            'DoCmd.OpenQuery "qryTotalHrsCur"
            'MySQL = "SELECT * FROM TimesheetTable WHERE ProjectRef LIKE '*" & Me.ProjectRef_txtbox & "*'AND " & sFormUser & ""
            'QDef (MySQL)
            'Call TotalHours("qryTotalHrsCur", MySQL)
       '*************************
            
       End Select
        
        
End If
End Sub