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]

What do you see if you look at the variable objGiftCard in the Watch Window when you add this code?

Set objGiftCard = rstIncen.Fields("Gift_Card_Place")

 

[Cudaboy]

What do you see if you look at the variable objGiftCard in the Watch Window when you add this code?

Set objGiftCard = rstIncen.Fields("Gift_Card_Place")

Hi Norie,

I see Expression: objGiftCard Value: the field is blank

(Type: Variant/Object/Field2 Context: Form_subfrmIncentivesCourtEdit)

 

[Norie]

Can you post the rest of your code?

 

[Cudaboy]

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

objGiftCard = rstIncen![Gift_Card_Place]

End Sub

Variables "TheDate" and "CID" are Public variables loaded in earlier code. The recordset is not empty so I am getting the one record from the table. I just now added code to get the count of records retrieved and it is 1 (one) as expected and desired.

 

[Cudaboy]

I meant to put:

objGiftCard = rstIncen!Fields("Gift_Card_Place")

I was playing with it when you replied. My bad!

 

[Norie]

Either version should work but you are missing Set.

Set objGiftCard = rstIncen![Gift_Card_Place]

By the way, I don't suppose you've set the Gift_Card_Place field to allow multiple Values?

If I set that property to No I have no problem getting the value from the field, but if I set it to Yes the field value is actually a recordset.

Dim strSQL As String
Dim rstIncen As DAO.Recordset

    strSQL = "SELECT Incentives.* FROM Incentives"

    Set rstIncen = CurrentDb.OpenRecordset(strSQL)

    rstIncen.MoveFirst

    Debug.Print TypeName(rstIncen.Fields(0).Value)
    Debug.Print TypeName(rstIncen.Fields(1).Value) ' if Allow Multiple Values is Yes returns RecordSet2.

 

[Cudaboy]

Current Code:

Private Sub LoadData()
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.MoveLast

rstIncen.MoveFirst

objCount = rstIncen.RecordCount

objGiftCard = rstIncen!Fields("Gift_Card_Place")

End Sub


objCount = 1 Type: Variant/Long
objGiftCard = Empty Type:Variant/Empty Not blank, it actually says Empty in the Watch window. I know the filed is not empty by looking at the table directly.

I also get a runtime error: 3265 - Item not found in this collection. I double checked and Gift_Card_Place is a column in the table Incentives.

The error happens on the line objGiftCard = rstIncen!Fields("Gift_Card_Place") which is likely why it is empty!

 

[Cudaboy]

Added SET:

Set objGiftCard = rstIncen!Fields("Gift_Card_Place")

Get same runtime error 3265

 

[Cudaboy]

Yes - the field is set to allow multiple values. Sorry I forgot to mention that earlier!

Added -

rstIncen.MoveFirst

Debug.Print TypeName(rstIncen.Fields(0).Value)
Debug.Print TypeName(rstIncen.Fields(1).Value) ' if Allow Multiple Values is Yes returns RecordSet2.

And in the Immediate window I see

LONG
LONG

so it appears to have identified something without producing an error, but the filed has a data type of Short Text so I don't know where LONG came from. A step forward maybe!