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

Cudaboy

New Member
Joined
Jan 18, 2017
Messages
22
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What do you see if you look at the variable objGiftCard in the Watch Window when you add this code?
Code:
Set objGiftCard = rstIncen.Fields("Gift_Card_Place")
 
Upvote 0
What do you see if you look at the variable objGiftCard in the Watch Window when you add this code?
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)
 
Upvote 0
Can you post the rest of your code?
 
Upvote 0
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.
 
Upvote 0
I meant to put:

objGiftCard = rstIncen!Fields("Gift_Card_Place")

I was playing with it when you replied. My bad!
 
Upvote 0
Either version should work but you are missing Set.
Code:
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.

Code:
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top