Results 1 to 4 of 4

Thread: If Then...

  1. #1
    Join Date
    Jul 2011
    Posts
    30

    Unanswered: If Then...

    Hopefully this is an easy(ish) one for someone... for me it certainly is not.

    I've been tasked to make an access (2007) database that will, among other things, allow for a user to choose a single item from an option group on a form. That was easy enough. Then the user will be given a drop down list that is specific to the option selected. For example, the option list has two selections, "fire" and "ice". If the user selects "fire", the adjacent dropdown list will show two options to select from: "match" and "torch". If the user selects "ice", the dropdown list will show: "cube"; "chip"; and "block". Any one of these answers will be filled into the corresponding field in the table. Is this even possible?

    I hope I’m making myself clear. Please let me know if not. I’ve searched the forms about IF THEN but am simply too much of a novice just yet (especially with VBA). Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:


    1. Create a table: Tbl_Items (or run the code hereafter):
    SysCounter: AutoNumber Primary Key
    Item: Text(50)
    Category: Long Integer

    Code:
    SysCounter	Item	Caterory
    --------------------------------
    1	        match	 1
    2	        torch	 1
    3	        cube	 2
    4	        chip	 2
    5	        block	 2
    Code:
    Sub Create_Tbl_Items()
    
    '
    ' This will create the table Tbl_Items and populate it.
    '
        CurrentDb.Execute "CREATE TABLE Tbl_Items ( [SysCounter] COUNTER (1,1), [Item] TEXT(50), [Caterory] LONG );"
        CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON Tbl_Items ( SysCounter ) WITH PRIMARY;"
        CurrentDb.Execute "INSERT INTO Tbl_Items ( [Item], [Caterory] ) VALUES ( 'match', 1 );"
        CurrentDb.Execute "INSERT INTO Tbl_Items ( [Item], [Caterory] ) VALUES ( 'torch', 1 );"
        CurrentDb.Execute "INSERT INTO Tbl_Items ( [Item], [Caterory] ) VALUES ( 'cube', 2 );"
        CurrentDb.Execute "INSERT INTO Tbl_Items ( [Item], [Caterory] ) VALUES ( 'chip', 2 );"
        CurrentDb.Execute "INSERT INTO Tbl_Items ( [Item], [Caterory] ) VALUES ( 'block', 2 );"
        
    End Sub
    2. On the form:

    a) Option Group: Frame_Category
    - After Update [Event Procedure]

    b) 2 option buttons (bound to Frame_Category):
    - Option_Fire: Option Value = 1
    - Option_Ice: Option Value = 2

    c) ComboBox: Combo_Items
    Row Source Type: Table/Query
    Limit to List: Yes
    Column Count: 2
    Column Width: 0cm;2,54cm (transpose in inches if necessary )

    3) In the module of the form:
    Code:
    Private Sub Frame_Categoy_AfterUpdate()
    
        Me.Combo_Items.RowSource = "SELECT Tbl_Items.SysCounter, Tbl_Items.Item FROM Tbl_Items WHERE Tbl_Items.Caterory=" & Me.Frame_Categoy.Value
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    30

    If Then...

    Thanks! I'll give it a shot. Not being an expert with VBA I don't know if this is relevant (and I probably should have mentioned it before): In my test database I have a table called “elements” (Fire, Ice, etc.) and four tables called “responses”. I want to pull from those established response tables when a selection from the elements table is made.

    Again... thanks!
    Attached Thumbnails Attached Thumbnails screenshot.JPG  

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Note: You don't need 4 tables for the responses. A single table with an extra column (Category in my example) is enough and helps you normalize the database.
    Have a nice day!

Tags for this Thread

Posting Permissions

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