How to save the result of a SQL query into a variable in VBA?

ms-access, sql, vba

Solution

Simply have your Function return the value from the Recordset:

Public Function rubrieknaamSQL(Child As Integer)
   Dim rst As DAO.Recordset
   Dim strSQL As String
   strSQL = "SELECT rubrieknaam FROM dbo_tbl_rubriek where rubrieknummer = " & Child & ""
   Set rst = CurrentDb.OpenRecordset(strSQL)
   ' new code:
   rubrieknaamSQL = rst!rubrieknaam
   rst.Close
   Set rst = Nothing
End Function

Problem

I want to execute a select statement and put the result of it (which is only 1 record with 1 value) in a variable. This is in VBA code in access. ``` Private Sub Child_Click() Dim Childnummer As Integer Dim childnaam As String Childnummer = Me.Keuzelijst21.Value DoCmd.Close DoCmd.OpenForm "submenurubrieken", acNormal, , " rubrieknummer = " & Childnummer & "" childnaam = rubrieknaamSQL(Childnummer) Forms!submenurubrieken.Tv_rubrieknaam.Value = childnaam End Sub Public Function rubrieknaamSQL(Child As Integer) Dim rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT rubrieknaam FROM dbo_tbl_rubriek where rubrieknummer = " & Child & "" Set rst = CurrentDb.OpenRecordset(strSQL) End Function ```

Original source

Related problems