[MS_AccessPros] Cascading List Box from Option Group

 

I have invested no less than 10 hours trying to get a list box to fill based on filters on the form and cannot figure out what I am doing wrong.  I have an unbound form the main purpose of which is to display a list box of samples.  These are water samples with the list box to display equipment which was sampled, the sample point and date of the sample. 

The form has a combo box which selects the customer site to use as one of the filters for the list box.  I also want to filter the list box based on either the "application group" or the "sample group".  Under the customer combo box, I have an option group with only two choices, Option 1 is the "application group" and Option 2 is the "sample group".

If Option 1 is selected, there is a combo box to select the Application Group to be used in the filter.  If Option 2 is selected, there is a combo box to select the appropriate Sample Group.  If Option 1 is selected, the combo for Option 2 (sample group) is disabled.  If OPtion 2 is selected, the combo for Option 1 is disabled.  

So the user selects a customer site and then selects whether he wishes to see the samples organized by application group or by sample group.  I build the SQL statement based on what selection is made in the option group.  And then assign the SQL to the row source of the lst.Samples.  When the user makes a selection in the combo box, there is a Requery lst.Samples in the after update.

I hope this is clear enough.

It all works well as long as the selection is Option 1.  If the selection is Option 2, I cannot for the life of me get the list box to display the list of samples organized by sample group.  

I know it is not the SQL statement that is the problem because I added a debug.print to the event code to print the row source for the list box, and then pasted the SQL into a query design window, and it produces the correct list of samples.  After hours of trying different things I still cannot get the list box to display any records when the Option Group value is 2. I can go back and forth between Option 1 and 2, and Option 1 always displays the correct records.  Option 2 will not display anything.

Below is my event code for after update of the option group:

Private Sub optApplicationOrSample_AfterUpdate()

    Dim strSQLAppGroup As String
    Dim strSQLSamGroup As String

    'build the Select query statement for filtering by Application Group
    strSQLAppGroup = ""
    strSQLAppGroup = strSQLAppGroup & "SELECT tblSamples.SampleID, qrySitesAndSamplePoints.EquipmentName AS Equipment, " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "qrySitesAndSamplePoints.SamplePointName AS [Sample Point], tblSamples.SampledDateTime AS [Date Time], " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "tblSamples.LIMSSampleID AS [LIMS Sample ID], tblSamples.LIMSProjectID " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "FROM qrySitesAndSamplePoints " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "INNER JOIN tblSamples ON qrySitesAndSamplePoints.SamplePointID = tblSamples.SamplePointID "
    strSQLAppGroup = strSQLAppGroup & "WHERE (((qrySitesAndSamplePoints.SiteID) = [Forms]![frmSelectSample]![cboSite]) " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "And ((qrySitesAndSamplePoints.ApplicationGroupID) = [Forms]![frmSelectSample]![cboApplicationGroup])) " & vbCrLf
    strSQLAppGroup = strSQLAppGroup & "ORDER BY qrySitesAndSamplePoints.SampleSort, tblSamples.SampledDateTime"

    'build the Select query statement for filtering by Sample Group
    strSQLSamGroup = ""
    strSQLSamGroup = strSQLSamGroup & "SELECT tblSamples.SampleID, qrySitesAndSamplePoints.EquipmentName AS Equipment, " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "qrySitesAndSamplePoints.SamplePointName AS [Sample Point], tblSamples.SampledDateTime AS [Date Time], " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "tblSamples.LIMSSampleID AS [LIMS Sample ID], tblSamples.LIMSProjectID " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "FROM(qrySitesAndSamplePoints INNER JOIN tblSamples ON qrySitesAndSamplePoints.SamplePointID = tblSamples.SamplePointID) " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "INNER JOIN tblSampleGroups_SamplePoints ON tblSamples.SamplePointID = tblSampleGroups_SamplePoints.SamplePointID " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "WHERE (((qrySitesAndSamplePoints.SiteID) = [Forms]![frmSelectSample]![cboSite]) " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "And ((tblSampleGroups_SamplePoints.SampleGroupID) = [Forms]![frmSelectSample]![cboSampleGroup])) " & vbCrLf
    strSQLSamGroup = strSQLSamGroup & "ORDER BY qrySitesAndSamplePoints.SampleSort, tblSamples.SampledDateTime"

    Debug.Print "optApplicationOrSample = " & Me.optApplicationOrSample.Value
    If optApplicationOrSample = 1 Then
        Me.lstSamples.RowSource = ""
        Me.lstSampleResults.Requery
        Me.cboSampleGroup = ""
        Me.cboSampleGroup.Enabled = False
        Me.cboApplicationGroup.Enabled = True
        Me.cboApplicationGroup.SetFocus
        Me.lstSamples.RowSource = strSQLAppGroup
        Me.lstSamples.Requery
    Else
        Me.lstSamples.RowSource = ""
        Me.lstSampleResults.Requery
        Me.cboApplicationGroup = ""
        Me.cboApplicationGroup.Enabled = False
        Me.cboSampleGroup.Enabled = True
        Me.cboSampleGroup.SetFocus
        Me.lstSamples.RowSource = strSQLSamGroup
        Me.lstSamples.Requery
    End If
    Debug.Print "lstSamples.rowsource = " & Me.lstSamples.RowSource
End Sub

And then here is my event code for the after update of the combo which is enabled depending on the selection in the Option group.  In this case, I am including only the event code for the After update of the cboSampleGroup.  It is the same event code that is in the After update for the cboApplicationGroup.

Private Sub cboSampleGroup_AfterUpdate()
    Me.lstSamples = Null
    Me.lstSampleResults.Requery
    Me.lstSamples.Requery
    Me.lstSamples.SetFocus
    Me.lstSampleResults.Requery
    Debug.Print "cboSite value is: " & Me.cboSite.Value
    Debug.Print "cboSampleGroup after update value is: " & Me.cboSampleGroup.Value
    Debug.Print " row source for lstSamples is: " & Me.lstSamples.RowSource
End Sub

If anyone can help please?

Keep in mind that I have verified that the SQL will produce the correct results when pasted into a query design window.

Thank you.

__._,_.___

---

Posted by: david.pratt@outlook.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___