SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Creating drop-down boxes in Access forms

    Well, well. I really need help on this one. I have hardly worked with Access before AT ALL, so please bear with me on this one.

    Tables:

    user
    ---------
    id
    name
    category

    user_category
    (this table is needed, since one user might be in several categories)
    ------------------
    user
    category

    category
    ------------------
    id
    name

    I'm designing a form (in access, that is) to edit the users, and I want a box (the kind where you can slect multiple choices) to select the category. Getting the box to just fetch names from the category table is not hard, but how the heck do I make sure it has the correct categories selected, and make sure that the changes get correctly inserted back int the database?

    If anyone can help me with this, I'll declare my undying love for you and send you cookies (the tasty kind).
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I recommend a form with two list boxes; one with all categories except the ones that the user belongs to, one with the user's categories

    Row source for first category listbox:
    SELECT id, name
    FROM category
    WHERE id NOT IN (SELECT category FROM user_category WHERE user = userdropdown.Value)

    Row source for second category listbox:
    SELECT id, name
    FROM category
    WHERE id IN (SELECT category FROM user_category WHERE user = userdropdown.Value)

    userdropdown event procedure:
    [vbs]
    Private Sub userdropdown_AfterUpdate()
    Me.categorylist1.Requery
    Me.categorylist2.Requery
    End Sub
    [/vbs]

    To move categories from list1 to list2:
    [vbs]
    Dim userid As Long
    Dim catids As String
    Dim item As Variant
    Dim sql As String
    userid = Me.userdropdown.Value
    catids = ""
    For Each item In Me.categorylist1.ItemsSelected
    catids = categorylist1.ItemData(item) & ", " & catids
    Next
    If catids <> "" Then
    catids = Left(catids, Len(catids) - 2)
    sql = "INSERT INTO user_category " & _
    "(user, category) " & _
    "SELECT " & userid & ", id " & _
    "FROM category " & _
    "WHERE id IN (" & catids & " )"
    CurrentProject.Connection.Execute sql
    userdropdown_AfterUpdate
    End If
    [/vbs]
    NB! The sql statement will not be "SELECT userid, id FROM category...", it will be "SELECT 123, id FROM category..." (if userid is 123)

    To do:
    1. Error handling
    2. The "remove category from user" function

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An image to illustrate what I mean with "a form with two list boxes"
    Attached Images Attached Images

  4. #4
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm gonna try this now. If it works, I love you so much, and will fedex cookies right away.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  5. #5
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Okay. I'm stuck!

    Like I said, I have virtually no experience with Access - or VB, for that matter, though I can figure out the code somewhat.

    My first problem of the evening is....

    THIS LINE!
    [VBS]Me.categorylist1.Requery[/VBS]

    Now, I assume that "Me" is kinda like "this". But where the heck does "categorylist1" come from? Is it the first of the dropdown boxes? Then what is "userdropdown"?
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    categorylist1 = the name of the listbox with categories

    Me = "this form", and it's optional to write in the code(or, more correct, the default form is the current form), I usually write Me., because then a list with the form's properties/methods/controls pops up in the VBA editor (intellisense, lo and behold!)

    Mattias, do you want me to send you a complete example?
    Or: Me.Send acExample, "M. Johansson"

  7. #7
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa
    Mattias, do you want me to send you a complete example?
    Or: Me.Send acExample, "M. Johansson"
    Wow - could you? That would be SO great, and you would be my personal VB god. I'd print your avatar out and place it on my monitor. My email addy is mattias@REMOVE_THIS_SO_THAT_SPAMBOTS_CANNOT_EAT_ITjohansson.com.

    edit - Also, Moomin rocks.
    Last edited by M. Johansson; Aug 31, 2002 at 14:57.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here's the example (I have mailed it to you, Mattias)

    First I created three tables:
    *** tb_character
    id autonumber pk
    name text 50
    *** tb_character_attribute
    id autonumber
    character_id number
    attribute_id number
    *** tb_attribute
    id autonumber
    description text 50

    I think the relationships are obvious

    Sample data:
    tb_character.id, name, tb_character_attribute.id, character_id, attribute_id, tb_attribute.id, description
    7, Little My, 5, 7, 5, 5, Bun
    2, Moominmamma, 12, 2, 3, 3, Apron
    2, Moominmamma, 11, 2, 2, 2, Bag
    ...

    Then I created a form with the following controls (labels omitted):
    Combo Box cboCharacter
    Button cmdDelete
    List Box lstAttributesOfCharacter
    Button cmdAdd
    Button cmdRemove
    List Box lstAttributesAvailable
    (see attached image)

    Finally, the event procedures:
    Code:
    Option Compare Database
    
    Private Sub cboCharacter_AfterUpdate()
        Me.lstAttributesOfCharacter.Requery
        Me.lstAttributesAvailable.Requery
    End Sub
    
    Private Sub cboCharacter_NotInList(NewData As String, Response As Integer)
        If MsgBox("This character is not in the list" & vbNewLine & _
        "Do you want to add it?", _
        vbYesNo + vbQuestion, _
        "Add character?") = vbYes Then
            Dim strSql As String
            strSql = "INSERT INTO tb_character " & _
                "(name) " & _
                "VALUES ('" & NewData & "')"
            CurrentProject.Connection.Execute strSql
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
            Me.cboCharacter.Undo
        End If
    End Sub
    
    Private Sub cmdAdd_Click()
        Dim varItem As Variant
        Dim lngCharacterId As Long
        Dim strAttributeIds As String
        Dim strSql As String
        lngCharacterId = Me.cboCharacter.Value
        strAttributeIds = ""
        For Each varItem In Me.lstAttributesAvailable.ItemsSelected
            strAttributeIds = lstAttributesAvailable.ItemData(varItem) & ", " & strAttributeIds
        Next
        If strAttributeIds <> "" Then
            strAttributeIds = Left(strAttributeIds, Len(strAttributeIds) - 2)
        Else
            Exit Sub
        End If
        strSql = "INSERT INTO tb_character_attribute " & _
            "(character_id, attribute_id) " & _
            "SELECT " & lngCharacterId & ", id " & _
            "FROM tb_attribute " & _
            "WHERE id IN (" & strAttributeIds & " )"
        On Error GoTo ErrHand
        CurrentProject.Connection.Execute strSql
        cboCharacter_AfterUpdate
        Exit Sub
    ErrHand:
        MsgBox Err.Description, vbCritical, Err.Source
    End Sub
    
    Private Sub cmdDelete_Click()
        If MsgBox("Do you really want to delete" & vbNewLine & _
        Me.cboCharacter.Column(1) & " ?", _
        vbYesNo + vbQuestion, _
        "Delete character?") = vbYes Then
            Dim strSql As String
            strSql = "DELETE FROM tb_character " & _
                "WHERE id = " & Me.cboCharacter.Value & ";"
            On Error GoTo ErrHand
            CurrentProject.Connection.Execute strSql
            Me.cboCharacter.Requery
            Me.cboCharacter.Value = Me.cboCharacter.Column(0, 0)
        End If
        Exit Sub
    ErrHand:
        MsgBox Err.Description, vbCritical, Err.Source
    End Sub
    
    Private Sub cmdRemove_Click()
        Dim varItem As Variant
        Dim lngCharacterId As Long
        Dim strAttributeIds As String
        Dim strSql As String
        lngCharacterId = Me.cboCharacter.Value
        strAttributeIds = ""
        For Each varItem In Me.lstAttributesOfCharacter.ItemsSelected
            strAttributeIds = lstAttributesOfCharacter.ItemData(varItem) & ", " & strAttributeIds
        Next
        If strAttributeIds <> "" Then
            strAttributeIds = Left(strAttributeIds, Len(strAttributeIds) - 2)
        Else
            Exit Sub
        End If
        strSql = "DELETE FROM tb_character_attribute " & _
            "WHERE character_id = " & lngCharacterId & " " & _
            "AND attribute_id IN(" & strAttributeIds & ");"
        On Error GoTo ErrHand
        CurrentProject.Connection.Execute strSql
        cboCharacter_AfterUpdate
        Exit Sub
    ErrHand:
        MsgBox Err.Description, vbCritical, Err.Source
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        With Me.cboCharacter
            .RowSource = "SELECT id, name " & _
                "FROM tb_character " & _
                "ORDER BY name;"
            .BoundColumn = 1
            .ColumnCount = 2
            .ColumnWidths = "0cm; 3cm"
            .Value = Me.cboCharacter.Column(0, 0)
        End With
        With Me.lstAttributesOfCharacter
            .RowSource = "SELECT id, description " & _
                "FROM tb_attribute " & _
                "WHERE id IN" & _
                    "(SELECT attribute_id " & _
                    "FROM tb_character_attribute " & _
                    "WHERE character_id = [cboCharacter].Value) " & _
                "ORDER BY description;"
            .BoundColumn = 1
            .ColumnCount = 2
            .ColumnWidths = "0cm; 6cm"
        End With
        With Me.lstAttributesAvailable
            .RowSource = "SELECT id, description " & _
                "FROM tb_attribute " & _
                "WHERE id NOT IN" & _
                    "(SELECT attribute_id " & _
                    "FROM tb_character_attribute " & _
                    "WHERE character_id = [cboCharacter].Value) " & _
                "ORDER BY description;"
            .BoundColumn = 1
            .ColumnCount = 2
            .ColumnWidths = "0cm; 6cm"
        End With
    End Sub
    Yes, Moomin rocks
    Attached Images Attached Images
    Last edited by jofa; Aug 31, 2002 at 17:56.

  9. #9
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Creating drop-down boxes in Access forms

    Originally posted by M. Johansson
    If anyone can help me with this, I'll declare my undying love for you and send you cookies (the tasty kind).
    Now what starts with the letter C?
    Cookie starts with C
    Let's think of other things that starts with C
    Uh ahh who cares about the other things

  10. #10
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Re: Re: Creating drop-down boxes in Access forms

    Originally posted by jofa


    Now what starts with the letter C?
    Cookie starts with C
    Let's think of other things that starts with C
    Uh ahh who cares about the other things
    HAhahaahahaha! Keep this up, and I might nominate you for mentorship!
    Last edited by M. Johansson; Sep 2, 2002 at 05:02.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having two database queries to pull the entire table is not good.

    Use a LEFT OUTER JOIN instead, and any row with a NULL for the user stuff into the other listbox thingy.

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    Having two database queries to pull the entire table is not good. Use a LEFT OUTER JOIN instead, and any row with a NULL for the user stuff into the other listbox thingy.
    I could agree to that, if it wasn't Ms Access we were dealing with

    Yes, you could execute this query...
    SELECT a.id, a.description, ca.character_id
    FROM tb_attribute AS a LEFT JOIN
    [SELECT attribute_id, character_id
    FROM tb_character_attribute
    WHERE character_id = [cboCharacter].Value]. AS ca
    ON a.id = ca.attribute_id
    ORDER BY a.description;
    ...and get all attributes and null for character_id on the rows where the current character doesn't have the attribute

    But - how would you get the result into the listboxes?
    Insert "WHERE ca.character_id Is Not Null" or "WHERE ca.character_id Is Null" into the query before using it as RowSource property for lstAttributesOfCharacter or lstAttributesAvailable?
    OK, then you are back to two database queries...

  13. #13
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could agree to that, if it wasn't Ms Access we were dealing with
    Meaning: In ASP, I would execute one query and then use the Filter property of the RecordSet before adding <option>s to the two <select> lists

  14. #14
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would do something like this (I haven't used VB in a while but the jist should be there):

    [vbs]
    ' Pseudocode
    Dim cboHas As ComboBox
    Dim cboNot As ComboBox

    ' Again making this up
    Dim conn As ADODB.ConnectionString
    Dim sSQL As String

    ' Blah
    sSQL = "SELECT ... LEFT OUTER JOIN ... "
    conn.execute sSQL

    While whatever Not Eof

    If whatever.field Is Empty Then ' It is the "has not" part
    cboNot.add whatever.attribute.name
    Else
    cboHas.add whatever.attribute.name
    EndIf
    whatever.movenext
    Next

    [/vbs]

  15. #15
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you could do that, clear the listboxes, loop through all rows in the recordsets, add new items...

    Or you could just set the RowSource and then call Requery


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
  •