?
Solved

Interactive Combo boxes

Posted on 1999-11-04
7
Medium Priority
?
369 Views
Last Modified: 2008-02-26
I have a form called FindLocationsF.  On this form are six (6) combo boxes titled PLATFORM, TIMEZONE,DIVISION, SERVERTYPE, STATE, COUNTRY.  Each of these combo boxes gets it's list using a SELECT DISTINCT statemnt from a query called DropDownListQ.

I want to make the 6 combo boxes interactive.  If I select Eastern Standard Time I only want the other boxes to display material that is available in Eastern Standard Time.  If I then select the state of New York I want all the remaining combo boxes to reflect material that is in EST and in New York.  I want to use the combo boxes in any order.
0
Comment
Question by:1wmike
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 2184316
You'd probably need to write a generic function, like:

Public Function FillCombos ()

   dim strCriteria as string
   strCriteria = "Platform like '" & cboPlatform & "*' and TimeZone like '" & cboTimeZone & "*' and Division like '" & cboDivision & "*' and ServerType like '" & cboServerType & "*' and State like '" & cboState & "*' and Country like '" & cboCountry & "*'"

   cboPlatform.rowsource = "Select distinct Platform from YourTable where " & strCriteria
   cboTimeZone.rowsource = "Select distinct TimeZone from YourTable where " & strCriteria
   cboDivision.rowsource = "Select distinct Division from YourTable where " & strCriteria
   cboServerType.rowsource = "Select distinct ServerType from YourTable where " & strCriteria
   cboState.rowsource = "Select distinct State from YourTable where " & strCriteria
   cboCountry.rowsource = "Select distinct Country from YourTable where " & strCriteria

End Function

then call this function on each combo box's AfterUpdate event.

Hope that helps . . .

brewdog
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2184684
1wmike:  Welcome to EE.

Before I run off and answer you questions, I'd like to ask you some questions to make it a bit clearer what you are trying to do.

You have one query which all combo boxes are pulling their lists.  So they have some key field in common for all of them.  As you select from the list of any combo box, you want the others to change to match the one you selected.

There are a couple of ways to do this without redoing the rowsource for each combo box.

1)  Create a hidden text box on the form which will hold the key field for the query.  In the criteria row for that field in the query, put the location of the hidden box, i.e.  [Forms]![frmWhatever]![HiddenKeyField].

In the AfterUpdate event for each combo box, you would make the hidden box = the combo box value.  Then requery the other boxes, use the function call below.  This will force them to all sync together.

2)  If the data source for the form can be made the query which all the combo boxes trigger off, then bind each combo box to the key field.  On the AfterUpdate event for any of the combo boxes, you only need to requery the other combo boxes.  This will resync the boxes.

An easy function which can be called to requery the other combo boxes could be:

     Function RequeryCombos(frmActive as Form, ctlActive as Control) as Byte
      With frmActive
         '  If you are using option 1, then add a line here to update the hidden field
         '  !HiddenKeyField = ctlActive
         Select Case ctlActive.Name
           Case "Platform"
                !Timezone.Requery
                !Division.Requery
                !Servertype.Requery
                !State.Requery
                !Country.Requery
           Case "Timezone"
                !Platform.Requery
                !Division.Requery
                !Servertype.Requery
                !State.Requery
                !Country.Requery
           Case "Division"
                !Timezone.Requery
                !Platform.Requery
                !Servertype.Requery
                !State.Requery
                !Country.Requery
           Case "Servertype"
                !Timezone.Requery
                !Division.Requery
                !Platform.Requery
                !State.Requery
                !Country.Requery
           Case "State"
                !Timezone.Requery
                !Division.Requery
                !Servertype.Requery
                !Platform.Requery
                !Country.Requery
           Case "Country"
                !Timezone.Requery
                !Division.Requery
                !Servertype.Requery
                !State.Requery
                !Platform.Requery
       End Select
    End Function

Jim
0
 

Author Comment

by:1wmike
ID: 2187247
Neither solution is working yet.  For an example of what I am trying to do, the form I have created is based on an Access 2.0 SOLUTIONS Database form titled FIND CUSTOMERS.  I have made the selection boxes into Combo Boxes (and it works just fine).

Now I am trying to sink those boxes.  Both of the solutions suggested make sense(I think) but in each case there seems to be one or more components missing.

brewdog:  I think your solution is very close but I can't figure out how to pass a value to "cboxxx"
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 7

Expert Comment

by:JimMorgan
ID: 2187325
Can you send me a copy of the offending DB?  I can check it out and either tell you what to do or fix it.

My email is in my profile.   Click my name to see the profile.

Jim
0
 
LVL 7

Accepted Solution

by:
JimMorgan earned 400 total points
ID: 2190584
Mike:

I seen and modified your DB and now know what you wanted to do.  To inform the rest of the viewers, this is the situation and the initital thought was quite a good one.  What Mike wanted to do was have the capability to select a rough grouping of records and then fine tune the selection until he had the results that he wanted.  Each cut of the records would further restrict the values available in the combo boxes.

A lot of people shy away from using combo boxes as selection criteria for finding a group of records.  The users can become confused when combo boxes are used in one spot to select from a group of predetermined values to input one of those values in a record and then they are used in another to select a group of records.

To differentiate the selection for data entry vs selection for pulling records, the drop-down combo box is normally used for the first and a list box for the second.  However, because of the number of possible fields to choose from and the limitation of space, the combo box was an obvious choice here.

The trick was getting the recordset behind the combo boxes to change when any one combo box value was selected.  I wish that it were possible to show an image of the query builder as it is so much clearer than a SQL statement.  Since we don't have that luxury, this is the main select query which all of these combo boxes work off.  (This query would also not be as large if he didn't have to go out and get the states list and countries from another tables.  Non use of spaces in field names would have made it simplier also.)


SELECT se_nodedetails.[Index Number], se_nodedetails.Division, se_nodedetails.Timezone, se_nodedetails.Platform, se_nodedetails.ServerType, [Country Abreviations].Country, States.State
FROM States
RIGHT JOIN (([Country Abreviations]
RIGHT JOIN [State List] ON [Country Abreviations].Abreviation = [State List].Country)
INNER JOIN se_nodedetails
ON [State List].Location = se_nodedetails.Location)
ON States.Abbreviation = [State List].State
WHERE (((se_nodedetails.Division) Like [forms]![frmFindLocations]![cboDivision] & "*")
AND ((se_nodedetails.Timezone) Like [forms]![frmFindLocations]![cboTimezone] & "*")
AND ((se_nodedetails.Platform) Like [forms]![frmFindLocations]![cboPlatform] & "*")
AND ((se_nodedetails.ServerType) Like [forms]![frmFindLocations]![cboServerType] & "*") AND
(([Country Abreviations].Country) Like [forms]![frmFindLocations]![cboCountry] & "*")
AND ((States.State) Like [forms]![frmFindLocations]![cboState] & "*"));

This query looks back at the form with the combo boxes for its criteria.  Since some combo boxes could be empty (no value selected yet), I had to make the criteria a 'Like' condition and add an '*' to each value to make it work.  I tried using an IIF function but it was more trouble than it was worth from a troubleshooting view and this is so much more simple and elegant.  Since each value can only come from a valid combo box entry, the only problem that would ever occur is if another value started with exactly the value of another entry.  It would select both values.

In the form module, I added this little function:

Function RequeryCombo() As Byte
    With Me
        !cboPlatform.Requery
        !cboTimezone.Requery
        !cboDivision.Requery
        !cboServerType.Requery
        !cboState.Requery
        !cboCountry.Requery
    End With
   
End Function

This is called on the AfterUpdate property line for each combo box

    = RequeryCombo()

and it is called also from a command button which is used to reset the form.

The results of these combo boxes are displayed in a subform when the users clicks a "Show Customers" button.  Intitally the subform was having its recordsource changed by rebuilding the SQL statement based on the values in the combo boxes.  Since the main select query has all the valid criteria needed to fill the subforms, I made the query behing the subform link to the main select query.

All that was left was to show the results.  Initially the subform is invisible.  When the user pushes "Show Customers", only two lines are run:

    sfrFindLocations.Form.Requery
    sfrFindLocations.Visible = True

This makes the form visible and shows all the customers.

Even when the subform is visible, if there are too many or the wrong customers, the combo boxes can be changed and Show Customers clicked again.  Lets say that a certain state was selected.  To change to another state, which apparently doesn't show in the combo dropdown, clear that combo box, tab to another combo box and go back.  Now all the original values are back for another shot (based on how the other combo boxes values).

To reset the form back to no selections and no customers showing, the reset button contains this code:

    Dim ctlThis As Control
   
    For Each ctlThis In Me.Controls
        With ctlThis
            If .ControlType = acTextBox Or .ControlType = acComboBox Then
                .Value = Null
            End If
        End With
    Next
   
    sfrFindLocations.Visible = False
    RequeryCombo

It can't get any simplier than this.

Jim
0
 

Author Comment

by:1wmike
ID: 2191470
Jim, I appreciae the effort you put into this.  
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2192069
Your welcome.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question