Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

vba

ms-access

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
like image 682
Appernicus Avatar asked Jun 02 '14 10:06

Appernicus


People also ask

How do I store SQL results in a variable?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

How do I assign a SQL query result to a variable?

To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

Can you save variables in SQL?

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another. User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ .

Can you declare a variable in a select statement?

Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared.


1 Answers

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
like image 174
Gord Thompson Avatar answered Oct 07 '22 14:10

Gord Thompson