Results 1 to 8 of 8
  1. #1
    andy.101 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    7

    Access Database - Multiple Choice selection varying based on last choice

    Hi,

    I am fairly new to Access Databases but I am designing a Helpdesk/Job system for our IT department.

    I have created a table to hold the information and then created a form for the user to complete with the job/issue details. Is this the most efficient way to do it?

    One thing I would like to do is make the multiple choice selection change depending on the previous choice (see below)

    The first column is 'Category' so the user selects Laptop/Mobile/Software etc

    The 2nd column is 'issue category'. I want the choices to vary depending on the answer in the 'Category' column. (i.e. if they choose mobile phone I want the 'issue category to display mobile phone issues).

    is this possible? any help is welcome.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    display the user choices to pick in the combo box, the query would bring 2 columns: issue, category
    [item], [cata]
    cell, mobile phone
    laptop, PC

    on the form, the user picks CELL, then the afterupdate event can fill in a combo (or textbox) for CATAGORY by grabbing the 2nd column via:
    Code:
    sub cboIssue_afterupdate()
       cboCata = cboIssue.column(1)     'NOTE in VB, columns start with zero, so column 2 of the combo box is 1 in vb.
    end sub
    be sure to set the combo to 2 columns in the property.

  3. #3
    andy.101 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    7
    Thank you for the help Ranman, but I probably need a little more detail on how to action this.

    At the moment I have a table with two columns (one called category and one called sub category), at the moment neither of these tables are linked to anywhere (they are both just short text) but I will be changing them to multiple choice.

    Category - Laptop, Mobile Phone, Software etc

    Sub Category - This will differ depending on the choice in the 'category'. (for example if they choose mobile phone - battery dead, smashed screen, lost phone etc will appear on a drop down) I'm not sure if this is possible?

    I have created a form which populates the table also.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What you describe is cascading comboboxes, a common topic. Search forum or Google.

    Would need a table of all possible category/issue combinations.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Another possible solution, I prefer:

    It's better you have table tblCategories: CategoryID, CategoryText;
    In Issues table you have a field CategoryID, p.e. tblIssues: IssueID, CategoryID, IssueText, ... ;

    Design an unbound Main form, p.e. fMain;
    On top of Main form insert an unbound combobox p.e. cbbSelectCategory (RowSourceType = 'Table/Query', Rowsource = 'SELECT CategoryID, CategoryText FROM tblCategories ORDER BY CategoryText', BoundColumn = 1, ColumnWidth = 0,2.5, DefaultValue = <any CatergoryID from tblCategories, you want to be displayed when fMain is opened at start>);

    Design a form for managing issues, p.e. fIssues. NB! Hide CategoryID (and IssueID too in case you use autonumber to generate IssueID). NB! Rename all controls so they differ from field names - like txtIssueID, txtCategoryID, txtIssueText, ... - this will make your life much simpler later;

    Open fMain, and drag from Objects window fIssues into it - place fIssues so, that cbbSelectCategory is placed above it;
    Inserting fIssues into fMain creates a subform object with fIssues a source. By default subform name is also fIssues - to avoid confusion later, rename it p.e. sfIssues;
    In subform properties set LinkMasterFields = 'cbbSelectCategory', LinkChildFields = '[CategoryID]'. NB! Don't use wizard for this - MS doesn't know that subforms can be linked to unbound controls ;

    In fIssues, create an OnCurrent event, which checks for existence of parent form (fMain), and when it exists, sets cbbSelectCategory in Main form egual to current CategoryID in fIssues;

    Now, when you open main form, in subform issues from category selected as default one are displayed. When you select different category from cbbSelectCategory, the subform changes and issues from selected category are displayed. Whenever you enter a new issue, the CategoryID from cbbSelectCategory is inserted into according control (txtCategoryID) in fIssues automatically - and is saved into tblIssues when the active record from fIssues is saved.
    Last edited by ArviLaanemets; 09-06-2017 at 02:15 PM.

  6. #6
    andy.101 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    7
    Thank you for all the help. I managed to get this to work using combo boxes.

  7. #7
    andy.101 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    7
    The form is displaying perfectly but the information that I record in the combo boxes isn't going onto the table with the rest of the record?

    I.e. (my combo boxes record the category and sub category of an issue)

    Date
    User
    Category
    Sub Category
    Details

    The category and sb category are blank when I go to my table after filling in the form. How do I link the answers from the combo boxes to the table?

    Thanks

  8. #8
    andy.101 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Posts
    7
    Managed to fix this one, thank you for all the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-19-2017, 01:29 PM
  2. Replies: 3
    Last Post: 12-29-2015, 04:06 PM
  3. multiple choice items
    By vientito in forum Access
    Replies: 1
    Last Post: 10-20-2014, 09:28 AM
  4. Multiple choice selection
    By Moridan in forum Access
    Replies: 35
    Last Post: 04-26-2013, 01:38 PM
  5. Replies: 3
    Last Post: 03-23-2013, 03:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums