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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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>
 
Last edited:
Upvote 0
What does the message box show with this code?
Code:
Set objGiftCard = rstIncen.Fields("Gift_Card_Place")

MsgBox TypeName(objGiftCard.Value)
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.:)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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