Results 1 to 10 of 10
  1. #1
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14

    Find/Compare/Select from Textbox to Multivalue Listbox *Unselected*

    Hi all, I new and still learning/venturing in the field of programming. Access is fun as it's easy for me to play around with for learning.



    Currently I am stumped in searching for a solution that I have been trying to search for but no luck... probably due to me not doing the correct search. My problem right now is with multivalue (simple/extended) listbox and hope to find a solution to this.

    There are 2 forms involved in this process, both are unbound.

    FrmA - contain simple listbox for all records viewing. (The tables are stored in a SQL server)

    With FrmA listbox on doubleclick a record > go to > FrmB (unbound) to view the record in detail [let's say there are 4 fields total: ReferenceNum, TaskSubject, CaseDocket, FiledByDocket]

    All are using unbound textbox fields. In addition, FiledByDocket unbound textbox field is dependent on a listbox with multivalue (in order to select multiple names separated by ", " when inserting into the textbox)

    Right now there is no issue with FrmB, the issue is when FrmA opening the record and trying to reference the textbox of FiledByDocket to the listbox.

    If the value in FiledByDocket textbox field contains when the form is loaded with selected record from FrmA: NameA, NameC, NameE, I want the listbox to automatically select the same values that is from the FiledByDocket textbox field.

    - The listbox values are: 'NameA','NameB','NameC','NameD','NameE'

    I created a module for FrmA to call to in order to split the values in the textbox once FrmB is loaded with the delimiter ", " and got that part working.

    Code:
    ' for FrmA to call on
    public sub valListName()
    dim strName, strArray as string dim varArray as variant strName = [forms]![mainform]![child1]![FiledByDocket].value strArray = split(strName, ", ") For each varArray in strArray ' so here is what I'm looking for for the code to search/compare the varArray to the ' unselected listbox and go through it so that way it ends up with valid listbox.selected(index) where it equals to the value from that array. next ' if I do debug.print varArray within the For...Next I get the text values which is what I wanted to use to compare with the listbox without having to select it.
    End Sub
    I hope my question is valid and thank you so much for your help. Please let me know if I need to provide any additional information.

    Again, please excuse my poor use of vocabularies.
    Last edited by 123noob; 06-16-2017 at 09:12 PM. Reason: Missing part of the information

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You are saving comma separated values into a textbox? You want to loop through the listbox items and select the items that match the names in the CSV string? Never heard of anything like this.

    I don't see how a Debug.Print could produce any output from that code. An array is not declared and referenced that way in VBA. In this case because the dimensions of the array are unknown until populated by the Split() function, it must be declared as a variant variable. Example:
    Code:
    Dim varArray As Variant, x As Integer, z As Integer
    varArray = Split("ABC, GHI, MNO", ", ")
    For x = 0 To UBound(varArray)
        For z = 0 To Listbox.ListCount - 1
            If Listbox.Column(0, z) = varArray(x) Then Listbox.Selected(z) = True
        Next
    Next
    This version clears and reselects at the same time.
    Code:
    Dim varArray As Variant, x As Integer, z As Integer, booMatch As Boolean
    varArray = Split("ABC, GHI, MNO", ", ")
    For z = 0 To Listbox.ListCount - 1
        For x = 0 To UBound(varArray)
            If Listbox.Column(0, z) = varArray(x) Then booMatch = True
        Next
        Listbox.Selected(z) = booMatch 
        booMatch = True
    Next
    A version without array:
    Code:
    Dim z As Integer
    For z = 0 To Listbox.ListCount - 1
        Listbox.Selected(z) = InStr("ABC, GHI, MNO", Listbox.Column(0, z)) > 0
    Next
    Instead of the text string in my example, reference your textbox.
    Last edited by June7; 06-17-2017 at 04:42 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.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if strArray is an array of string, it is declared like this

    Dim strArray() as string

    then to loop through you would use

    dim I as integer
    for I-0 to ubound(strArray)-1
    msgbox strArray(I)
    next i

  4. #4
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    Quote Originally Posted by June7 View Post
    You are saving comma separated values into a textbox? You want to loop through the listbox items and select the items that match the names in the CSV string? Never heard of anything like this.

    I don't see how a Debug.Print could produce any output from that code. An array is not declared and referenced that way in VBA. In this case because the dimensions of the array are unknown until populated by the Split() function, it must be declared as a variant variable. Example:
    Code:
    Dim varArray As Variant, x As Integer, z As Integer
    varArray = Split("ABC, GHI, MNO", ", ")
    For x = 0 To UBound(varArray)
        For z = 0 To Listbox.ListCount - 1
            If Listbox.Column(0, z) = varArray(x) Then Listbox.Selected(z) = True
        Next
    Next
    This version clears and reselects at the same time.
    Code:
    Dim varArray As Variant, x As Integer, z As Integer, booMatch As Boolean
    varArray = Split("ABC, GHI, MNO", ", ")
    For z = 0 To Listbox.ListCount - 1
        For x = 0 To UBound(varArray)
            If Listbox.Column(0, z) = varArray(x) Then booMatch = True
        Next
        Listbox.Selected(z) = booMatch 
        booMatch = True
    Next
    A version without array:
    Code:
    Dim z As Integer
    For z = 0 To Listbox.ListCount - 1
        Listbox.Selected(z) = InStr("ABC, GHI, MNO", Listbox.Column(0, z)) > 0
    Next
    Instead of the text string in my example, reference your textbox.
    Thank you so much June! the first one worked and i added exit for once it found what it's comparing to.

    Good thing that the listbox isn't a huge list, but is this the only way? would there be a quicker way to go through without going back validating each value in the listbox, assuming the list contains large values.

    It's weird that I could not find the syntax ListCount in the available syntax list that pops up (not sure what to call it as) so I thought I had to use "listbox.ItemSelected.Count - 1" which prompted me with a question of... "Wait doesn't that means at least a value has to be selected already?"

    Then I went and used ListCount and voila... problem solved. I guess I should not be too dependent on the available syntax popup list.

    But once again, thank you so much!

    Quote Originally Posted by Ajax View Post
    if strArray is an array of string, it is declared like this

    Dim strArray() as string

    then to loop through you would use

    dim I as integer
    for I-0 to ubound(strArray)-1
    msgbox strArray(I)
    next i
    Thank you Ajax , I have never dealt with arrays yet but every single time I work on this I learn something everyday, it's very exciting for me!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    would there be a quicker way to go through without going back validating each value in the listbox, assuming the list contains large values.
    The other versions go through the listbox once. Add an Exit to the second one. The last version without array would be my preference.

    Think Ajax has a typo in example, need = sign.

    for I = 0 to ubound(strArray)-1
    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.

  6. #6
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    Awesome, I ended up going with your last version since that go through quicker and looped only once.

    Thank you again for the quick assistance June!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It's called IntelliSense popup help. And I do see ListCount in the popup. What you see depends on what you are referencing and how and where.
    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
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    Quote Originally Posted by June7 View Post
    It's called IntelliSense popup help. And I do see ListCount in the popup. What you see depends on what you are referencing and how and where.
    This was very educational for me , learned more than I thought I would from this topic!

    I was had a control dimmed for the listbox in the module...

    Code:
    Dim lstCtl As Control: lstCtl = [forms]![formmain]![child1]![FiledByDocket]
    So I guess since it's being referenced from somewhere else other than itself on the form, the popup did not show everything for that control.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I thought FiledByDocket was a textbox?

    If you referenced listbox directly in code behind the form it is on, like Me.Listbox1.ListCount, you should see ListCount in IntelliSense. The bang (!) character does not provoke IntelliSense in VBA.

    Does not provoke IntelliSense: [forms]![formmain]![child1]![FiledByDocket]

    Will provoke IntelliSense: Form_formname.child1.Form.Controls("controlname"). However, as you already found out, there are limitations. Referencing through subform/subreport containers is trickier. This won't show the control names that are on the embedded subform nor the control properties although they can be typed out.

    Here is another syntax: Form_formname.child1!fieldname

    I always give controls a name different from fields or objects that are connected to. So textbox bound to FiledByDocket would be like tbxDocket. I would use something more meaningful than Child1 for subform container, like ctrOrders.


    Access also has IntelliSense help in object designers and the bang will work, as in queries.
    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
    123noob is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2017
    Posts
    14
    Gahh... sorry I was using example name different from what I have on my actual form and messed up myself.

    FiledByDocket = textbox
    lstFiledBy = listbox

    On my actual form I have it as txtFiledByCtl and the listbox as lstFiledByCtl, the naming convention is still hard for me as I keep trying to be short and concise but still make sense.

    Sorry for the confusion, but thank you for your patient and guidance! Your explanation is very easy to understand.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-21-2015, 06:55 PM
  2. MultiValue Select Box
    By eagle670 in forum Forms
    Replies: 6
    Last Post: 06-18-2014, 10:37 AM
  3. Replies: 2
    Last Post: 03-23-2014, 06:50 AM
  4. Replies: 3
    Last Post: 01-06-2012, 01:20 AM
  5. SELECT WHERE compare two fields
    By bulb763 in forum Access
    Replies: 5
    Last Post: 03-22-2011, 09:56 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