Sorting a subform using dependent combobox

[deb_anup]

I had a query with field name
Name
Date
DateOfRecd (Converted to Month and Year)
RR No
Package
Amount
Now I want to add 3 dependent combobox in the form for sorting
Like Name, Year & Month which will sort the data & view as per combobox selection.

I am very new in Access. Plz. help.

Regards
Anup

 

[CHAOSinACT]

Honestly I'll tell you but I suspect you'll need more help. Let me know how this goes.


Here is an example of (see jpg and code) of something that does what you want.

This is a form with controls with a SubForm in it. As you change the values of the controls, the VBA rebuilds the SQL for the subform and reinserts it into the SubForm RecordSource Property.

Private Sub frmJobFilter_AfterUpdate()
    Call SetFilter(Me!frmJobFilter)
End Sub

Private Sub frmSortOrder_AfterUpdate()
    Call SetSort(Me!frmSortOrder)
End Sub

Public Sub SetSort(CurrentSort)
    Select Case CurrentSort
        Case 1
            Me!txtCurrentSort = "Number Largest"
        Case 2
            Me!txtCurrentSort = "Number Smallest"
        Case 3
            Me!txtCurrentSort = "Name A-Z"
        Case 4
            Me!txtCurrentSort = "Name Z-A"
    End Select
    Call SubControlSource
    
End Sub

Public Sub SetFilter(CurrentFilter)
    Select Case CurrentFilter
        Case 1
            Me!txtCurrentDisplay = "Active Unfinished"
        Case 2
            Me!txtCurrentDisplay = "Finished"
        Case 3
            Me!txtCurrentDisplay = "Quote Only"
    End Select
    Call SubControlSource
End Sub


Public Sub SubControlSource()
    Dim QuoteActive As Boolean
    Dim JobInit As Boolean
    Dim Finished As Boolean
    Dim SQL As String
    Dim strOrderBy As String
    Dim strRecordSource As String
    Select Case Me!txtCurrentDisplay
        Case "Active Unfinished"
            QuoteActive = True
            JobInit = True
            Finished = False
        Case "Finished"
            QuoteActive = True
            JobInit = True
            Finished = True
        Case "Quote Only"
            QuoteActive = True
            JobInit = False
            Finished = False
    End Select
    Select Case Me!txtCurrentSort
        Case "Number Largest"
            strOrderBy = "ORDER BY tblDGroupCivilMinorJobs.[Civil Job Number];"
        Case "Number Smallest"
            strOrderBy = "ORDER BY tblDGroupCivilMinorJobs.[Civil Job Number] DESC;"
        Case "Name A-Z"
            strOrderBy = "ORDER BY tblDGroupCivilMinorJobs.[Civil Job Name];"
        Case "Name Z-A"
            strOrderBy = "ORDER BY tblDGroupCivilMinorJobs.[Civil Job Name] DESC;"

    End Select
SQL = "SELECT tblDGroupCivilMinorJobs.[Civil Job Number] AS JobNo, tblDGroupCivilMinorJobs.[Civil Job Name], tblDGroupCivilMinorJobs.[Start Date], tblDGroupCivilMinorJobs.[Finish Date]" & _
      "From tblDGroupCivilMinorJobs WHERE (((tblDGroupCivilMinorJobs.JobInitiated)=" & JobInit & ") AND ((tblDGroupCivilMinorJobs.Finished)=" & Finished & ") AND ((tblDGroupCivilMinorJobs.[Quote Only])=" & QuoteActive & "))"

strRecordSource = SQL & strOrderBy

Forms!frmPlantManagementJobList.frmJobList.Form.RecordSource = strRecordSource

Forms!frmPlantManagementJobList.frmJobList.Requery
Forms!frmPlantManagementJobList.frmJobList.Form.Requery

End Sub