INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

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.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This forum is the most helpful site I've ever used. I used to use Deja.com; but, this site is better - hands down!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Forms > FAQs

Microsoft: Access Forms FAQ

User Interface Techniques

Continuous Form / Combobox Filter

faq702-3679

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

My FAQ Archive