Using "ALL" in drop down to return All records option.

[kfschaefer]

I have a Mainform/Subform(s), I need to include the option of show all records or limit the record to the select item from a combo. Each subform has a different recordsource, if the user selects "ALL" then I need for each subform's recordsource to display all records for each record source/subform.

However, if the user selects a specific item in the drop down then I need to limit the record source for each subform accordingly. The problem I am having is whether to use the LinkedChildfields & linkedMasterfields (ProgramRecID). Since there will not be an actual ProgramRecID = "All" this poses a problem. in returning the correct records. when using this method without the "All" this works great.

I have tried many possible solutions without success, so I am tuning to you on the best approach to having multiple subforms records display "All" records when ask, yet still limit the results.

someone suggested just adding the following WHERE statement to all my queries/sql strings. Problme with this method is a TypeMismatch error occurs.

Private Sub cboSearch_AfterUpdate()
    Dim rs As Object
    Dim nRecID As Integer
    Dim strSQL As String
    Dim strSql0 As String
    Dim strSql1 As String
    Dim strSql2 As String
    Dim strSql3 As String
    Dim strSql4 As String
    Dim frm As Form_frmPrograms
   On Error GoTo cboSearch_AfterUpdate_Error
    
    Me.TabProgram.Visible = True
    
    nRecID = Me.cboSearch
        
         strSQL = "SELECT tblAssignPrgSchool.ProgramRecID, tblPrograms.[Program Title]," & _
                    " tblPrograms.[Program Type], tblPrograms.[Program Engagement]," & _
                    " tblPrograms.[Program Meetings], tblPrograms.[Program Updates]," & _
                    " tblPrograms.[IP Agreement], tblPrograms.[IP Expiration Date]," & _
                    " tblPrograms.ROI, tblPrograms.Linked_CTC_CTE, tblPrograms.ERT," & _
                    " tblPrograms.Program_Ranking, tblAssignPrgSchool.SchoolNameRecID," & _
                    " tblAssignPrgSchool.DateModified" & _
                " FROM tblPrograms INNER JOIN tblAssignPrgSchool ON" & _
                   " tblPrograms.ProgramRecID = tblAssignPrgSchool.ProgramRecID"
Debug.Print Forms!frmPrograms!cboSearch

         strWHERE = " WHERE ProgramRecID Like IIf(Forms!frmPrograms!cboSearch = 0, " * ", Forms!frmPrograms!cboSearch)"

Can anyone recommend the best and simiplist approach to handling "ALL", yet limiting the results to the users selection.

Thanks,

K

 

[VilaRestal]

I think you can set the rowsource of the combobox to this:

SELECT 'All' AS ProgramRecID UNION SELECT ProgramRecID FROM...

Then in the code check if ProgramRecID = 'All' and only set strWHERE if it isn't.

 

[VilaRestal]

Correction, stupid Access SQL says "query must contain a table". You can get round that with:

SELECT 'All' AS ProgramRecID FROM (SELECT TOP 1 * FROM Table1) UNION SELECT ProgramRecID FROM...

Table1 can be any table with at least one record in it and without a ProgramRecID field. Preferably a small table with few records and few fields for performance sake. (MSysObjects might be a good choice, it should always exist and always have data in it:

SELECT 'All' AS ProgramRecID FROM (SELECT TOP 1 ParentID FROM MSysObjects) UNION SELECT ProgramRecID FROM [Your Table]

)