Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Creating a recordset via VBA SQL and selecting the vales of a value list column

  1. #11
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,584
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a recordset via VBA SQL and selecting the vales of a value list column

    Fields(0) and Fields(1) refer to the first 2 fields in the recordset, I used them rather than the field names because I'm testing with a table with only 2 fields - Incen_Date and Gift_Card_Place.

    You probably have a lot more fields in your table so Fields(0) and Fields(2) are unlikely to refer to those 2 fields.

    Try replacing 0 and 1 with Incen_Date and Gift_Card_Place.
    Last edited by Norie; Jun 13th, 2018 at 04:25 PM.
    If posting code please use code tags.

  2. #12
    New Member
    Join Date
    Jan 2017
    Location
    Prescott, Az, USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a recordset via VBA SQL and selecting the vales of a value list column

    I changed the line to: objGiftCard = rstIncen.Fields(2).Value

    and got the date used in the query. rstIncen.Fields(1).Value returned the CID used in the query

    rstIncen.Fields(0).Value returned the CID again

    rstIncen.Fields(3).Value gave an empty result


    Changing to:

    Set objGiftCard = rstIncen.Fields("Gift_Card_Place").Value

    Does not error but produces nothing in the watch window. Not "Empty", just blank space.
    Last edited by Cudaboy; Jun 13th, 2018 at 04:34 PM.

  3. #13
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,584
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a recordset via VBA SQL and selecting the vales of a value list column

    What does the message box show with this code?
    Code:
    Set objGiftCard = rstIncen.Fields("Gift_Card_Place")
    
    MsgBox TypeName(objGiftCard.Value)
    If posting code please use code tags.

  4. #14
    New Member
    Join Date
    Jan 2017
    Location
    Prescott, Az, USA
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a recordset via VBA SQL and selecting the vales of a value list column

    Found it! Here: http://www.utteraccess.com/forum/Acc...-t1944468.html

    First I create a recordset consisting of the single record retrieved via the SQL string. That recordset has the values of all fields, but does not have the details of those fields that contain multiple values. So I create a second recordset from those fields that I have set to store multiple values thus:

    Dim rstDetails As DAO.Recordset

    Set rstDetails = rstIncen!Gift_Card_Place.Value

    THAT recordset (rstDetails) contains the values as a set of Field(0) values like it was a single-column table. Looping through it produces the values in the multi-value field.

    CODE:

    Dim strSQL As String
    Dim rstIncen As DAO.Recordset
    Dim rstDetails As DAO.Recordset
    strSQL = "SELECT Incentives.* FROM Incentives WHERE (((Incentives.Incen_Date)=#" & TheDate & "#) AND ((Incentives.Client_ID)=" & CID & "));"

    Set rstIncen = CurrentDb.OpenRecordset(strSQL)

    rstIncen.MoveFirst

    Set rstDetails = rstIncen!Gift_Card_Place.Value

    While rstDetails.EOF = False 'Loop through all values in the multi-value field

    Debug.Print rstDetails.Fields(0).Value 'Print each value of the multi-value field
    rstDetails.MoveNext 'Moves to next item in the multi-value field

    Wend

    That code properly listed all the values in the multi-value field. Now all I have to do work with those values as I get them. Another whole issue!

    Thank you, Norie, for your help! It gave me more clarity and a good direction to look for more information.
    Last edited by Cudaboy; Jun 13th, 2018 at 05:26 PM.

  5. #15
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,584
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating a recordset via VBA SQL and selecting the vales of a value list column

    That's basically what I've been trying to say, a multivalue (lookup) field is a recordset in itself and needs to be treated as such.
    If posting code please use code tags.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com