Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: Populate drop down box based on another selection

    Hi all,

    New to databases and am working on a project in Access 2003 where applicants can register and be called for jobs in various universities. My problem is this:

    I have a table where the dbase admin can list the people who have been called for interview. This table's fields are:

    InterviewID; InterviewDate; Interviewee(linked to applicant table); University(linked to university table); and job.

    My issue is with the job field. I want to populate that box based on the university that is selected in the university field.

    I have another table in the dbase called jobs which has the fields:

    jobId; university(linked to university table); jobname(text field);skills required(linked to skills table).

    Do I need to run a query to get this to work? Can't figure it out at all!

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The concept is calles "cascading comboboxes" (the actual name of what you refer to as "drop-down box" is called a ComboBox) and there are tens (if not hundreds) of examples, here on dBforums as well as on many other sites.

    Basically, it consists in changing the RowSource property of the second combobox according to the value selected in the first one. This is usually done by assembling a dynamic SQL SELECT statement that comprises a WHERE clause using the value of the first combo, and use this SQL statement as the RowSource of the second combo. This is normally done in the AfterUpdate event handler of the first combobox.

    Example:
    "ComboA" has a list of values: "ValA1", "ValA2", "ValA3", ...etc. coming from "TableA".
    "ComboB" has a list of values coming from "TableB", while "tableB" has a column "ValA" that can contains values such as "ValA1", "ValA2", "ValA3", ...etc.

    To retrieve "Field1" and "Field2" from TableB for all rows that have the value "VaAl2" in their "ValA" column, I use:
    Code:
    SELECT TableB.Field1, TableB.Field2 FROM TableB WHERE TableB.ValA = 'ValA2';
    So in the Form:
    Code:
    Sub ComboA_AfterUpdate()
    
        Dim strSQL As String
    
        strSQL = "SELECT TableB.Field1, TableB.Field2 FROM TableB WHERE TableB.ValA = '" & Me.ComboA.Value & "';"
        Me.ComboB.RowSource = strSQL
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Is there an easier way to do that? Or else a viable alternative to the jobs field?

    This project is meant to be quite basic. SQL statements etc are not expected or required. This cascading drop box is more effort than its worth for me to be honest.

    Thanks for the input though very much appreciated.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This actually is quite basic. Access has several "wizards" and other "assistants" that can help you (event builder, query builder...). Unfortunately, the available versions of Access do not provide any magic wand, yet.
    Have a nice day!

Posting Permissions

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