How do I store the results of my select statement in a variable?

Question

My code so far is this. The last line gives me a compile error: "expected end of statement".

Dim strSql As String
Dim groupId As String

strSql = "Select ID from RevenueGroup where description = '" & ListO.Value & "'"
groupId = CurrentProject.Connection.Execute strSql

Answer 1

You are looking at something kinda like this

Dim strSql As String
Dim groupId As String

strSql = "Select ID from RevenueGroup where description = '" & ListO.Value & "'"

Dim rec As Recordset
set rec= CurrentProject.Connection.Execute strSql

groupId = rec(0)

You need to set the results of the query to a recordset and then pull the first value from its results. Without all the defined variable, I cannot get this to fully compile but this should be a good template to start from.