VBA To Access Multi-Value Lookup Field

Discussion in 'Access VBA Modules' started by Rich Locus, Jun 1, 2010.

  1. Rich Locus

    Rich Locus Guest

    Hello:
    Using the new feature in Access 2007, I created a table field using the
    Lookup Wizard to allow multiple values for the State. For example, a user
    can select one or more States (CA, NY, AZ) from a drop down in the form.

    I get an error when trying to reference it using VBA as a type string:

    For example,
    Public Function ReadBusinessInfo()
    ....
    Dim strBusinessInTheseStates As String
    ....
    strBusinessInTheseStates = recIn!States

    Is a multi-value field not a string? If I assign it as a Variant, then
    there is no error, but I'm not sure how to reference the data.

    Ideas?
     
    Rich Locus, Jun 1, 2010
    #1
    1. Advertisements

  2. Rich Locus

    Allen Browne Guest

    Take a look at the ELookup() function on this page:
    http://allenbrowne.com/ser-42.html

    You will see how it tests whether the fields is multi-valued, and if so
    treats the contents as a recordset.
     
    Allen Browne, Jun 1, 2010
    #2
    1. Advertisements

  3. Rich Locus

    Rich Locus Guest

    Allen:
    Your example will "get me going" again.
    Thanks
     
    Rich Locus, Jun 1, 2010
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.