Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Continuous Form / Combobox Filter

User Interface Techniques

Continuous Form / Combobox Filter

by  redapples  Posted    (Edited  )
I had noticed a number of posts where people had used a combo box to filter the content of more combo boxes on a continuous forms. I spotted these posts because I had a problem with this process myself. The problem was as follows. The filter would work so that the second combo box would indeed show the correct filtered list but once that filter changes entry for previous records would start to disappear. The underlying records would be correct but the display would not.

None of the posts about this had, in my opinion, a good explanantion of why the problem occured nor a solution.

That is what I hope to do here.

On my very simple continuous form I had 2 combo boxes. The first presented a criteria. When the criteria was selected it looked for indicators in a table comprising of the indicatorID (primary key); indicatorName; criteriaID (foriegnkey).

I used the code below in an after update and on current event to update the rowsource of the second combobox
Code: 
Private Sub GetRowSource()
Dim strCriteria
    
    strCriteria = Me.Criterion
    Me.Text2.RowSource = "SELECT DISTINCT tblIndicators.Indicator _
 , tblIndicators.Id FROM tblIndicators WHERE _
(((tblIndicators.CriteriaID)=" & strCriteria & "));"

End Sub

No problem there. the second combo box therefore had two columns the first displaying the InticatorName as text and the second the IndicatorID (ID) as a number. Column 2 was the bound column so the record of indicator was a number and therefore easier for queries and eaiser to update.

The combobox had the limit to list property set to yes because this is the requirement when more than one column is visible. Here in lay the problem;

because the record was a number not text and because limit to list was set to yes when the rowsource changed the number was not on the list and could not be displayed.

I played around with not having the text indicator name as a bound control but then when I changed a value in it on one record it changed all the records on my continuous form.

Here is what I plumped for in the end and indeed what works for me. It may be a bit ugly but it does do the job.

I have modified the GetRowSource() routine as follows;

Code: 
Private Sub GetRowSource()
Dim strCriteria
    
    strCriteria = Me.Criterion
    Me.Text2.RowSource = "SELECT DISTINCT _ tblIndicators.Indicator FROM tblIndicators WHERE  _(((tblIndicators.CriteriaID)=" & strCriteria & "));"

End Sub
Note this time I have only specified one column in the SQL the Indicator field (the text name value).

I added a new field to the record table that the continuous form is bound this previously contained Key; ClientIdentifier; Date; Critierion; IndicatorNumber; Occurrance; Week.
My new field was IndicatorName.

I next bound the combobox containing Indicators (the one that is filtered) to have only 1 column which is bound to the new field IndicatorName. I set the limit to list property to No.

In the afterupdate event I added the following
Code: 
Private Sub cmbIndicator_AfterUpdate()
    Dim cnn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strfind As String
    
    Set cnn = CurrentProject.Connection
    rs.Open "tblIndicators", cnn, 3, 3
    
    strfind = "IndicatorName= '" & cmbIndicator & "'"
    
    rs.Find (strfind)
    txtIndicator = rs!id
    rs.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

I then added a hidden field to the form txtIndicator that holds the number value and is bound to the IndicatorNumber field in the table. This preserves the numbers for use in queries.


Problems:
As a text value for the indicators is stored in the table for client intervention any change to the text of an indicator (in the indicator table) will not automatically update the intervention record.
This breaks normal form and is bad, bad, bad!
However I have tried and tried and can't find a better solution.


Now when the record changes the rowsource for cmbIndicator is updated but the value (not on the list) is displayed differently for each record.

Hope this helps. If one person benefits then it is a success.

Red4pples
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top