Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Stop listbox remembering previous selection.

    On a form I have two listboxes. Each uses a query to show certain "parts". A part is a purchasable item. When a part is selected and the button is hit from the left listbox, It will append into a junction table and refresh both lists.



    The one on the left only shows parts not listed in the right.

    The problem I have :

    All the above works fine. However, If I click the button again after I have moved a part from the first list, the listbox remembers the previous selection and will append it for a second time. Even though that item is not in the first list for selection, unless I click on another part it will assume that's the current selection.

    I've tried multiple ways to stop this and I believe the following would work.... If I could get it working.

    The error I get is 3075: syntax error missing operator.

    here is the code:

    Code:
     Dim lngVal As Long
         lngVal = Forms!Quote_Main!Materials.Form!LstParts
         Forms!Quote_Main!Materials.Form!LstParts.Requery
         Forms!Quote_Main!Materials.Form!LstParts = Nz(DLookup("Mat_ID2", "LSTMaterial_Updated", "[Mat_ID2] = " & lngValue), Forms!Quote_Main!Materials.Form!LstParts.ItemData(0))
    The error is on the last line here. Any advice or other solutions appreciated.#

    Edit: just noticed it shows this "[Mat_ID2] = " as where the error is .

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    refresh the 1st box in the dbl-click,

    lstbox1.requery

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Materials2.zipHi Ranman.

    I can requery the listbox no problem. The data it shows is always correct.

    But it remembers the last selection. Even when that is no longer an option.

    I've attached the DB. I don't know if there will be an issue because it does have two linked tables. If it does let me know and ill sort it out.

    The form I'm working on is Quote_Main. If you first select a category from the drop down it will show all available options for that category. Then, select a "part" from the list and click, "select Part"

    This is now removed from the list. If you press select part again, It will add that same part again. I don't want it to do that

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then it sounds like the query for list 1 is wrong.
    it should look at list2 and remove them from list1 using an outer query.

    or if you key list2, you CANT add it twice.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The TOP two lines in EVERY module should be
    Code:
    Option Compare Database
    Option Explicit
    This would show you the error:
    Code:
    Private Sub Form_Current()
    
        Dim lngVal As Long
    
        lngVal = Forms!Quote_Main!Materials.Form!LstParts
        Forms!Quote_Main!Materials.Form!LstParts.Requery
        Forms!Quote_Main!Materials.Form!LstParts = Nz(DLookup("Mat_ID", "LSTMaterial_Updated", "[Mat_ID] = " & lngValue), Forms!Quote_Main!Materials.Form!LstParts.ItemData(0))
    
    End Sub
    Notice anything??? Like the names????



    If you press select part again, It will add that same part again.
    Once you select an option in the list box, the value stays until you change it even if the list box is refreshed

    Try this: Open the Materials" form in Design view.
    Just below the "Remove Part" button, add an unbound text box. Set the CONTROL SOURCE to "=[LstParts]" (no quotes)
    Save and close. Open the form "Quote_Main". Select a category, then click on an option. The text box should show a number.
    Click on the "SELECT PART" button. Did the number in the text box go away?

    Somehow the value for the list box needs to be cleared.

    I NEVER use macros, so I don't know to clear the value.
    In VBA it is very simple......

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe this will work (macros ...)

    In the macro design for the button "SELECT PART" click event:

    Add action "SetProperty"
    set "ControlName" = LstParts
    set "Property" = Value
    set "Value" = 0 (or maybe NULL)

    Save and close.

    Select a category and click the button "SELECT PART". Now click the button again......

    If it works (part not added again), go into design view for form "Materials" and delete the text box you added. Save and close.

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Hey both,

    Ranman I agree it should not be like that, That's why I was confused!.

    Steve, Thanks a lot mate. Look at you go! you will be using macros all the time at this rate!.

    That worked perfect, appreciate it.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Steve, Thanks a lot mate. Look at you go! you will be using macros all the time at this rate!.
    Don't hold your breath
    Doing that little bit gave me a major headache! I had to go to forget about macros......


    Glad it worked for you...

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Methinks the query is stuck on the ListIndex value of the control. Not sure if that's an issue with the control or the query itself since I would have thought the index would be rebuilt upon the requery action. Then again, those of us who don't use macros can be confounded by their differences vs vba code. The Requery Action is different from the Requery Method in that one remains loaded, the other not, but I don't recall which is which. This may not be the issue. I tried to invoke a Set Value action on the target listbox (to set its value to Null, but your Materials form is not compatible with my version). As noted below, I couldn't set the value property either.

    Since you are appending a record on each button click, then capturing that list with the target listbox query, I suppose you could have set the target query property to Unique Values = Yes. That will eliminate duplicate rows from the target listbox, but I don't know if that helps with whatever comes afterwards in the process.

    Steve, I tried to set the property as well, before I saw your note on this (honest!). Maybe it's a version thing, but .Value was not an option in my property list, and when I tried to insert it myself, I got an error message about it not being in the list.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Quote Originally Posted by Micron View Post
    I suppose you could have set the target query property to Unique Values = Yes
    The way I'm thinking of using the data, this wouldn't be useful. But thanks for the suggestion. At least I'm aware I can do this now. Or possibly have another route to go down. These two list boxes cause me a lot of headaches too! haha. Glad they are doing what I want now.

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

Similar Threads

  1. Replies: 10
    Last Post: 06-18-2013, 02:00 PM
  2. Replies: 2
    Last Post: 02-01-2013, 05:23 PM
  3. Replies: 1
    Last Post: 09-10-2012, 11:21 PM
  4. Deleting a ListBox Selection
    By dreamnauta in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 03:14 PM
  5. Cancel listbox selection
    By vba-dev in forum Access
    Replies: 0
    Last Post: 10-26-2009, 12:18 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