Getting Query Value With VBA (1 Viewer)

Jonny

Registered User
Joined
Aug 12, 2005
Messages
140
Something does not work When trying to get a value from the table, getting an error "A runsql action requires an argument consisting of an sql statement"
Code:
Private Sub cboEmpID_AfterUpdate()
    Dim SQL As String

     SQL = "SELECT ID, Class" & _
             " From Employee " & _
             " WHERE ID = " & cboEmpID.Value & ";"

    DoCmd.RunSQL SQL 
End Sub
As I found "DoCmd.RunSQL" statement is not used with select statement.
Then I've changed DoCmd to:
Code:
DoCmd.OpenQuery SQL
Still getting an error , but "Microsoft Access can't find the object 'Select.."
What's the right way to get a value?
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,691
Hi. There are a few ways to get what you want. If you actually have a saved query with a name, you can use DLookup() or OpenQuery against that query's name. If you only have a SQL statement, then you can use a Recordset object to get the data from it.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,691
Yes, thanks, that is the direction. Will try shortly..
Okay, I was just curious. Sometimes, my posts may not make sense right away. Let us know how it goes or if you get stuck. Cheers!
 

Jonny

Registered User
Joined
Aug 12, 2005
Messages
140
Hi. There are a few ways to get what you want. If you actually have a saved query with a name, you can use DLookup() or OpenQuery against that query's name. If you only have a SQL statement, then you can use a Recordset object to get the data from it.
Here we go, working version implemented with recordset. Short and sexy!
Code:
Private Sub cboEmpID_AfterUpdate()
    Dim SQL, strClass As String
    Dim rstEmployees As DAO.Recordset

    SQL = "SELECT ID, Class, FirstName" & _
             " From Employee " & _
             " WHERE ID = " & cboEmpID.Value & ";"
 
    Set rstEmployees = CurrentDb.OpenRecordset(SQL)
    strClass = rstEmployees!Class
    
    MsgBox strClass
End Sub
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,691
Here we go, working version implemented with recordset. Short and sexy!
Code:
Private Sub cboEmpID_AfterUpdate()
    Dim SQL, strClass As String
    Dim rstEmployees As DAO.Recordset

    SQL = "SELECT ID, Class, FirstName" & _
             " From Employee " & _
             " WHERE ID = " & cboEmpID.Value & ";"
 
    Set rstEmployees = CurrentDb.OpenRecordset(SQL)
    strClass = rstEmployees!Class
    
    MsgBox strClass
End Sub
Congratulations! Glad to hear you got it working. Good luck with your project.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,337
Congratulations. Just for completeness, here's how you could do it without a recordset

Code:
Private Sub cboEmpID_AfterUpdate()
    Dim strClass As String
    strClass =DLookup("Class","tblEmployee","ID = " & Me.cboEmpID)
    
    MsgBox strClass
End Sub
To my mind, shorter and simpler.
 

Jonny

Registered User
Joined
Aug 12, 2005
Messages
140
Congratulations. Just for completeness, here's how you could do it without a recordset

Code:
Private Sub cboEmpID_AfterUpdate()
    Dim strClass As String
    strClass =DLookup("Class","tblEmployee","ID = " & Me.cboEmpID)
    
    MsgBox strClass
End Sub
To my mind, shorter and simpler.
Thank you!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom