Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: VBA coding in Access - Problem getting the text string

    Hello

    I was using a former guide in this forum to adopt my code for visual basic but I keep getting the index values of the selected items, not the text. If someone can help me that would be great!

    I have several multi-choice boxes that i read from:

    For Each varItem In Forms![Ønskede referanser - Standardformat].List2.ItemsSelected
    strMateriale = strMateriale & "," & Forms![Ønskede referanser - Standardformat].List2.ItemData(varItem) & ""
    Next varItem

    This then returns the index value in the list for the selected items, for example 1 and 3. Does anyone know how to make it return the value, i.e. the text, instead of the number?

    Best regards
    Knut

  2. #2
    Join Date
    Mar 2013
    Posts
    1
    Quote Originally Posted by knutta View Post
    Hello

    I was using a former guide in this forum to adopt my code for visual basic but I keep getting the index values of the selected items, not the text. If someone can help me that would be great!

    I have several multi-choice boxes that i read from:

    For Each varItem In Forms![Ønskede referanser - Standardformat].List2.ItemsSelected
    strMateriale = strMateriale & "," & Forms![Ønskede referanser - Standardformat].List2.ItemData(varItem) & ""
    Next varItem

    This then returns the index value in the list for the selected items, for example 1 and 3. Does anyone know how to make it return the value, i.e. the text, instead of the number?

    Best regards
    Knut
    You can always use Dlookup() function together with NZ() for each strMateriale in order to get back the value that you want or some value if the returned value is null.
    i.e.: Lets say that your your table/query named "qry" is this: [ID, Name, Lastname, Phone, email] and your strMateriale equals to field ID.
    Your code will be this:
    Code:
     Nz(Dlookup("Name", "qry", "[ID]=" & strMateriale),"0")
    .
    Have in mind that it is crucial to use ' sign when you are searching with strings in your criteria ("[ID]=" & strMateriale).
    I.e. if your dlookup was searching based on field "Name", then your dlookup will be like this: Nz(Dlookup("Name", "qry", "[Name]='" & strMateriale & "'"),"0").

    Your complete code will be this:
    Code:
    For Each varItem In Forms![Ønskede referanser - Standardformat].List2.ItemsSelected
                 strMateriale = strMateriale & "," & Forms![Ønskede referanser - Standardformat].List2.ItemData(varItem) & ""
                 SomeVar = Nz(Dlookup function will be: Dlookup("Name", "qry", "[ID]=" & strMateriale),"0")
                 Next varItem
    hope this helps!

Posting Permissions

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