Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2015
    Posts
    51

    Unanswered: Select List box item

    I have two list boxes, when you select a record from one you can move it to the other.

    I would like to be able to select a record move it, and then have then next available record selected instead of having to click the record each time I want to move it.

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "updateQ_UNassignpass", acViewNormal, acEdit
    DoCmd.Requery "AssignedPasses"
    DoCmd.Requery "UnassignedPasses"
    Me.AssignedPasses.Selected(0) = True
    DoCmd.SetWarnings True


    This code highlights the record, but when I click my command button again it does nothing ( or it runs the update query, but the query doesn't see a record has been selected)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you "know" what is the next record

    requerying two list boxes may be quite resource intensive.. its probably fine on a limited number of rows and on a small system/network, but could well become a problem with large amounts of data over a corporate network already near capacity

    what you could do is use unbound list boxes (you stuff and remove data under your control).

    as to helping you with your current code there is near feck all anybody can do.
    we don't know what the query: updateQ_UNassignpass is/does/affects which columns
    we can guess that AssignedPasses and UnassignedPasses are the list/combo boxes, buyt we don't know what the row/datasources are

    what you could do with your exisiting approach is:-
    take note of the SELECTED row in the source list box (find its index), then usign code retrieve the "next" row however you have defined that... (SELECTED index +1), store the value of that next row 's id in a temporary variable.
    do your processing
    requery your list boxes
    set the value of the source list box to be the value from the temporary variable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2015
    Posts
    51
    The first list is based on a query that has criteria from the previous form, maximum the list will only ever have 10-20 records. The second list is only populated when you add from the first list, after that they are subject to an update query and will never be available in this form again. The selecting which record is next is easy to address.

    I need to know how to address the problem that when it does "select" a record all it is doing is highlighting and not actually selecting. If I click on the already highlighted record it works but that what I don't want to have to do.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use code to store the 'next' value. look at the properties exposed as part of the list/combo box object
    OR
    populate the list boxes manually through code as opposed to using a query


    An Access form uses event hooks to which you can attach code
    so you need to palce some code itn he source list boxes on click event that does "something". frotm eh sound of it that soemthign is:-
    1) mark that row as assigned
    2) remove that row from the unassigned list box
    3) add that row to the assigned list box
    4) reposition the unassigned list box to the next record in sequence.

    there's plenty of code examples on t'net on how to find what item has been selected in a list box. there's lenty of code on how to populate a list box manually as opposed to using a query

    this sort of thing is virtually impossible to talk through... it is all in the detail and that detail required that you have a good knowledge of the list box object and a good attempt at writing the code yourself
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2015
    Posts
    51
    So that is exactly how my code works, I get the indexvalue, move the record and then set my focus on the "next value" based on the fist value. It works perfect and All I had to do was re-write it all out. Funny how it bugs out for no apparent re

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by andromeda00 View Post
    Funny how it bugs out for no apparent reason

    ...highly unlikely that 'it bugs out for no apparent reason'
    but then again seeing as you explicitly turned warning off you hindered any attempts Access makes to tell you what went wrong. and then turn warnings back on again after completing whatever processing you are trying to debug. Once you've proven the code works then its fine and dandy to turn of warnings where appropriate

    again without knowing what the query & table designs are its impossible to know why your code doesn't do what you think it may. My guess would be that when you designed the query you didn't actually use any input from the combo box in a where clause or similar.

    one of the biggest traps to fall into in development is 'knowing' what you intended to happen when you wrote a piece code in your mind, believing that what your code does is what your minds eye 'knows' it does and therefore glossing over sources of errors. you need to prove each fragment of code / query works as intended not just with good data but also satisfactorily handles bad data.

    Access has a very good integrated debugger, better than many. learn how to use so you can prove your assumptions about your code. it also helps if you document what the code should be doing if its not immediately obvious, or you are doing some off the wall manipulation that isn't immediately obvious from the column / variable / function names (ferinstance if it were me I'd be adding a comment to the code to identify what those queries do, what the parameters are (ie how does query x work, what the where clause is). there is no point putting in numpty comments if the code itself is clear enough
    Code:
    sales_value = unit_price * qty_ordered 'doesnt need a comment
    whereas
    Code:
    DoCmd.SetWarnings False 'dont want to scare users in live code with stuff going on in background
    'query updates blah where the blah-di-blah = selected value in listbox xyz
    DoCmd.OpenQuery "updateQ_UNassignpass", acViewNormal, acEdit
    ....
    A prime example of how not to comment is the plethora of documentation in Java and so on that doesn't actually explain what a function/property/method is or does but repeats the same words already used in the function/method/property name.

    The biggest bugbear in Access debugging is the absence of a good unit testing framework, although http://sourceforge.net/projects/vbaunit/ suggests others are working to fix that... unti testing has the fundamental advantage that you can modify code, then run a series of baseline tests that prove or disprove whether tce code does what it should. the real beauty is that if your baseline tests are written well enough and comprehensive enough you don't have to remember which bits of code you've tinkered with and therefore need testing... just run the test suite and verify the code passes QA
    Last edited by healdem; 12-02-15 at 09:46.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2015
    Posts
    51
    I wrote the code the same way, I guess what I meant is that It wasn't apparent to me, it all looks the same.

    Here is what I came up with to determine the next suggested item in the list:

    Dim Lst_as_selected As Long
    Lst_as_selected = UnassignedPasses.ListIndex 'Get list index number of selected item
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "updateQ_assignpass", acViewNormal, acEdit 'run update query to mark pass as assigned
    DoCmd.Requery "AssignedPasses" 'requery list
    DoCmd.Requery "UnassignedPasses" 'requery list

    With UnassignedPasses
    On Error GoTo Error1
    If .ListIndex <> .ListCount - 1 Then
    .SetFocus
    .ListIndex = Lst_as_selected
    Else
    .SetFocus
    .ListIndex = .ListIndex + 1
    End If
    End With
    DoCmd.SetWarnings True
    Exit Sub

    Error1: MsgBox "End of List", vbokayonly
    Resume Next


    It works perfectly this time.

Posting Permissions

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