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

Question

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

Why can't you just store the value of the record with myVar = rst!nameOfValueField ? — Roland
– Roland, Commented Jun 2, 2014 at 10:32

Gord Thompson · Accepted Answer · 2014-06-02 11:17:50Z

28

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

answered Jun 2, 2014 at 11:17

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Appernicus Over a year ago

was my first time making such a query in vba. thanks anyways!

Jorge Over a year ago

what does the ! in rst!rubrieknaam do?

Gord Thompson Over a year ago

@Jorge - See the related question here.

Patrick Conwell Over a year ago

This only returns the first matching result. How would you return ALL results that match the query?

Gord Thompson Over a year ago

@PatrickConwell - You process each row by looping through the recordset with Do Until rst.EOF and rst.MoveNext

Hightower · Accepted Answer · 2021-05-04 22:43:19Z

0

You can do this in pretty much one line by using the "DLookup" Function

rubrieknaam = Nz(DLookup("rubrieknaam ", "dbo_tbl_rubriek ", rubrieknummer & " =[Child]"), 0)

where Child is the ID of the record you are looking for.

answered May 4, 2021 at 22:43

Hightower

1,0085 gold badges27 silver badges60 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related