Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: combo box experiment, where did i go wrong?

    Hi guys,

    Ive been trying to get my teeth into the cascading combo box, so i created a little table to see if i can do it.

    In the form i have 2 combo boxes the 1st combo box row source is to the table and the 2nd combo rowsouce i have put this statement

    SELECT TblName.* FROM TblName WHERE Area='" & cmb1 & "';"

    im trying to filter out so the 2nd combo only shows the names from a a certain area.

    but the box is comming out blank.. ive attached the database. any ideas guys?

    thanks

    Aboo
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Aboo

    In your first combo box you need to add the word DISTINCT for your row source of this control so that only 1 display is shown for each value of the Area currently you have many 1s and 2s displayed the row source should be
    SELECT distinct TblName.Area FROM TblName ORDER BY TblName.Area;

    For your second combo box you need to reference the control of cmb1 e.g. forms!form1!cmb1. The following code will concatenate the first last name into one field and only display the records that have the selected Area.
    SELECT [CLName] & " " & [cfname] AS emp_name FROM TblName WHERE (((TblName.Area)=[forms]![form1]![cmb1])) ORDER BY [CLName] & " " & [cfname];

    You will need to make changes to the layout as required.

    Various contributors have sites that have examples of cascading combo boxes.

    If have attached an updated version of your example using the above code.
    Attached Files Attached Files

  3. #3
    Join Date
    May 2006
    Posts
    178
    Thanks Poppa,

    I have noticed though when i change the area in cmb1 the values still remain the same in cmb2

    do i need to somehow requery?

    Thanks

    Aboo

  4. #4
    Join Date
    May 2006
    Posts
    178
    Poppa,

    Did you create a variable called emp_name ? if it isnt can you please explain what it is please...

    regards

    Aboo

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Put the following code in the GOT FOCUS of cmb2, thiswill requery cmb2 when it gets the focus.

    Private Sub cmb2_GotFocus()
    DoCmd.Requery "cmb2"
    End Sub


    Have a look at the query for the record source of cmb2, emp_name is the alias for the concatenation of the person's name.

  6. #6
    Join Date
    May 2006
    Posts
    178
    Tanksyou Popps, your a star!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •