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

[Cudaboy]

Hey all!

I have created an Access 2016 database with a table that has a column set as a value list. This allows me to create a checkbox-style input for the user, but I am having difficulty retrieving the values using SQL in VBA to create a recordset. I want to pull the record that corresponds to a specific ClientID and Date (can do that OK) then populate a subform with the data just like it would if the subform were using a query as its record source. I have:

Dim strSQL As String
Dim rstIncen 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

And this is where I cannot figure out how to select the values from the column that has the listbox:

Field Name: Gift_Card_Place
Data Type: Short Text

Lookup -
Display Control: List Box
Row Source Type: Value List
Row Source: Harkins;Walmart;Subway;Starbucks;Other

The record in the recordset has Starbucks checked. I assume the record column has all five possibilities and a Boolean Yes/No associated with them.

This produces an "Type Mismatch" error - rstIncen!Gift_Card_Place

I can successfully get any value from the recordset that is not associated with a list, but I cannot figure out how to get the list values from the two columns bound to a list (as above Gift_Card_Place).

Thank you for any help!

 

[Norie]

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.

 

[Cudaboy]

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<expression context="" in="" defined="" not="">


Changing to:

Set objGiftCard = rstIncen.Fields("Gift_Card_Place").Value<strike></strike>

Does not error but produces nothing in the watch window. Not "Empty", just blank space.
</expression>

 

[Norie]

What does the message box show with this code?

Set objGiftCard = rstIncen.Fields("Gift_Card_Place")

MsgBox TypeName(objGiftCard.Value)

 

[Cudaboy]

Found it! Here: http://www.utteraccess.com/forum/Access-Multiple-Values-Fi-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.

 

[Norie]

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.

 

[Cudaboy]

This thread can be considered closed.

My solution for this databse was to stop allowing the user to select multiple values (gift cards). This was really no issue and the original requirement was not specified well.

However, thanks to Norie, I was able to understand that a multi-value field is a unique challenge and I was able to use it successfully in a different Access database. So thanks to Norie!