SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    durham
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Dynamic Query Fields

    Hello!

    I am using MS Access 2000

    Is there any way to update a query field based on a combo box selection?

    The combo box row source is the field list for "Table1"

    Table1 is a storage table with >50 fields, basically:
    "WorkerInitials" and then their aptitude in a variety of skills, such as "Task1" "Task2", etc.

    I would like to be able to have a user select a skill on which to base the query such that if I selected Task33, the query (and subsequent report) would spit out

    Worker Task33
    ABC not trained
    BCD trained
    CDE in training
    DEF trained
    ... ...

    is there some SQL code or vb script that can do this?

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It's built into Access. Look at the help under "query by form". It's a while since I used it so I can't remember the steps, but it is very straightforward indeed.

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    durham
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy dynamic query field

    I think the QBF would give me select records for whichever fields I chose to base my query (presuming multiple text boxes, each representative of the different fields), but all the other fields would show up as well, and/or I would have to set up >50 text boxes for the fields.

    I am trying to have only two columns displayed in the Query design view (and ultimately a report): the WorkerInitials field, and then the other field, which would be based on the field selected in a combo box populated by a field list, and show all records

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Now I see the problem. My mistake.

    This probably isn't what you want, but you could write all the required scripts and use the drop down list to chose which one run via a switch case in vba. Which of course is a lot of work and not a nice compact answer.

    An alternative would be to pass the name of the field selected to a vba script which then used concatenation to construct the desired sql from existing bits and then ran it.

  5. #5
    SitePoint Member
    Join Date
    Jun 2004
    Location
    durham
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation dqf

    Thanks for your help. I was afraid there wouldn't be a "quick-fix". Oh well, on to scripting!!

  6. #6
    SitePoint Member
    Join Date
    Jun 2004
    Location
    durham
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've actually started the database over to make it normalized. hopefully that will lessen the complexity.

  7. #7
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dmhfflwjl
    I've actually started the database over to make it normalized. hopefully that will lessen the complexity.
    Yes, normalization is the answer here. Once you've done that it's easy-peasy!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

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