Results 1 to 4 of 4
  1. #1
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14

    Change value in query from command button based on selection in combo box

    I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.



    I have another query that will only show the line item selected from the list box. This means the query will only have one line item.

    I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.

    How can I accomplish this?

    Also, how can I make a button that deletes the selected line item?

    If you need any more information please let me know! Any help is appreciated!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. you dont need all these buttons, but you can. To delete an item in the list box, use the listBox_KeyDown() event.
    The user selects an item, pressed delete ,then the event runs a delete query to remove the item selected

    Code:
    sub lstBox_KeyDown(KeyCode As Integer, Shift As Integer)
    if KeyCode = vbKeyDelete then
        docmd.setwarnings false
        docmd.openquery "qdDel1Item"    'delete query uses the users pick in criteria
        lstBox.requery
    end if
    2. You can have the user dbl-click the item in the list box to change it from yes to no (or visa versa)
    Code:
    sub lstBox_doubleclick()
        docmd.setwarnings false
        docmd.openquery "quUpd1Item"    'this update query uses the users pick in criteria , and set the field to:  NOT [field]
        lstBox.requery
    end if

  3. #3
    FALAGELOLOGIST is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    14
    Quote Originally Posted by ranman256 View Post
    1. you dont need all these buttons, but you can. To delete an item in the list box, use the listBox_KeyDown() event.
    The user selects an item, pressed delete ,then the event runs a delete query to remove the item selected

    Code:
    sub lstBox_KeyDown(KeyCode As Integer, Shift As Integer)
    if KeyCode = vbKeyDelete then
        docmd.setwarnings false
        docmd.openquery "qdDel1Item"    'delete query uses the users pick in criteria
        lstBox.requery
    end if
    2. You can have the user dbl-click the item in the list box to change it from yes to no (or visa versa)
    Code:
    sub lstBox_doubleclick()
        docmd.setwarnings false
        docmd.openquery "quUpd1Item"    'this update query uses the users pick in criteria , and set the field to:  NOT [field]
        lstBox.requery
    end if
    I am having trouble getting the second code to work (haven't tried the first). I am pretty novice at this stuff.

    I selected the list box on the form, selected "on Dbl Click" from the property sheet and then pasted your code. It did not work. I also tried changing "quUpd1Item" to the name of my query. Wasn't sure if I was supposed to do that. Again, I am very beginner. How do I make this work?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, it is supposed to be YOUR query. I just gave an example name.
    Also you only need these lines...and lstBox is the name of YOUR list box.

    docmd.setwarnings false
    docmd.openquery "quUpd1Item"
    lstBox.requery

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

Similar Threads

  1. change image based on combo box selection
    By cpotter in forum Database Design
    Replies: 11
    Last Post: 10-05-2013, 09:51 AM
  2. Replies: 3
    Last Post: 12-11-2012, 09:12 AM
  3. More Info" button based on Combo Box selection
    By kriskeven in forum Access
    Replies: 1
    Last Post: 05-21-2012, 02:23 PM
  4. Replies: 6
    Last Post: 03-10-2011, 11:31 AM
  5. Replies: 1
    Last Post: 10-19-2009, 02:37 AM

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