Results 1 to 10 of 10
  1. #1
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    30

    Question Set Listbox value when opening a form from another form.


    Hi, I'm not 100% sure what to look for on this one, so I'm hoping for some guidance.

    I have a search form that has a listbox on it, filled by a query. Currently you can double click on one of the entries, and it will open another form to view the details about the item you selected. When I'm opening the new form, I'm also using OpenArgs to pass a couple pieces of data that I need on the details form to correctly specify which exact record I want to see.

    In the details form, I have two listboxes that the users can click to show different records, related to the main record. I'm passing to the form the data I would like to have selected on the listboxes when the form opens.

    This is my OnLoad for the details form:

    Code:
    Private Sub Form_Load()
    Dim varSplitString As Variant
    Dim stDash As String
    Dim stVariant As String
    
    If Not IsNull(Me.OpenArgs) Then
       varSplitString = Split(Me.OpenArgs, "|")
       stDash = varSplitString(0)
       stVariant = varSplitString(1)
    End If
    Looking at the debug, my data comes through fine. My stumbling block is the listboxes. They are ListDash and ListVariation, and they fill based on a query. How do I have the listboxes selected based on what stDash and stVariant are when the form opens? I've tried setting the value and some other tricks, but everything just errors out. I'm thinking I need to loop through the data in each listbox, compare it to the variables, and have it use the Selected property to choose which one matches? Or is there a simpler way of doing things?

    Thanks!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could create two hidden text box controls and set their values in the forms On Open event. The queries could be set to reference these text boxes.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    AlexHedley's Avatar
    AlexHedley is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    England
    Posts
    180

    Set Listbox value when opening a form from another form.

    Example:
    lstListBox = {value}

    Do something like:
    ListDash = arg(0)
    ListVariation = arg(1)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is the details form bound? Are those listboxes bound?

    If the listbox is unbound, yes, code could cycle through listbox items until match found. Row and Column indexes begin with 0.

    I've never done this but see if this helps http://www.tek-tips.com/viewthread.cfm?qid=956464

    EDIT: Just saw previous postings. I think Bob's suggestion would result in a filtered list, not actually selecting an item. I think Alex's might work with bound listboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by June7 View Post
    EDIT: Just saw previous postings. I think Bob's suggestion would result in a filtered list, not actually selecting an item. I think Alex's might work with bound listboxes.
    Yes, I think you're right. I should have read the OP more carefully.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    30
    Quote Originally Posted by June7 View Post
    Is the details form bound? Are those listboxes bound?

    If the listbox is unbound, yes, code could cycle through listbox items until match found. Row and Column indexes begin with 0.

    I've never done this but see if this helps http://www.tek-tips.com/viewthread.cfm?qid=956464

    EDIT: Just saw previous postings. I think Bob's suggestion would result in a filtered list, not actually selecting an item. I think Alex's might work with bound listboxes.
    I know the listboxes are unbound, but I can't remember on the form. The variable passed is something like 101A, and the listbox on the form has maybe 6 entries (101A, 129B, 139A, etc.) so I know that the variable will be in the listbox.

    Looking at your link, I'm thinking something along the lines of:

    Code:
    Dim lst As listbox
    Dim lngCount As long
    set lst = Me!ListDash
    for lngCount=0 to lst.listcount-1
      if lst.column(0,lngCount) = stDash then
        lst.selected(lngCount)=true
        exit for
      end if
    next lngCount
    
    I'll have to try that first thing in the AM.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is purpose for the unbound listboxes?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    30
    They are helping select which record to show. The data I am retrieving has 3 different "keys" that I can have a user select. DrawingNumber, DashNumber, and Variation. The form is defaulting to cycling through Drawing Numbers. Each DrawingNumber may have 5 or 6 DashNumbers, and combining those, you may have 5 or 6 Variations of each set.

    So the user opens the form and can select which DrawingNumber to view, then there is a listbox with the various DashNumbers, and clicking that fills the next listbox with the Variations available, for the user to select a specific Variation. Each step displays some more information on the form related to each of the three "keys".

    When a user gets to this form from the search form, I'm wanting to have everything selected as it opens, so its showing only the exact info they wanted.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It might be easier to open the form filtered to just the record(s) that match the 3 parameters. But if you want to allow users to change the listbox selections on the second form and view other records, filtered recordset might prevent that.
    Last edited by June7; 11-13-2013 at 11:58 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    SaskiFX is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    30
    Success! A little tweaking and its operating just how I wanted it to. The details form opens up with the listboxes selected automatically and shows me all the details.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-10-2013, 08:57 AM
  2. Replies: 15
    Last Post: 04-17-2012, 01:42 PM
  3. Replies: 17
    Last Post: 11-24-2011, 03:37 AM
  4. Replies: 2
    Last Post: 07-26-2011, 08:26 AM
  5. Replies: 1
    Last Post: 11-09-2010, 03:02 PM

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
  •  
Other Forums: Microsoft Office Forums