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
    Logicwurks, LLC
     
    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 - Microsoft MVP. Perth, Western Australia

    Reply to group, rather than allenbrowne at mvps dot org.


    "Rich Locus" <> wrote in message
    news:...
    > 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
    > Logicwurks, LLC
     
    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
    Logicwurks, LLC


    "Allen Browne" wrote:

    > 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 - Microsoft MVP. Perth, Western Australia
    >
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    >
    > "Rich Locus" <> wrote in message
    > news:...
    > > 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
    > > Logicwurks, LLC

    >
    > .
    >
     
    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.