INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

User Interface Techniques

Continuous Form / Combobox Filter by redapples
Posted: 3 Jun 03

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


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;


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
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

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close