Getting Query Value With VBA (2 Viewers)

Jonny

Registered User.
Local time
Today, 14:25
Joined
Aug 12, 2005
Messages
144
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
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
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
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
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.
Local time
Today, 14:25
Joined
Aug 12, 2005
Messages
144
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!
I will certainly do, thank you, Sir!
 

Jonny

Registered User.
Local time
Today, 14:25
Joined
Aug 12, 2005
Messages
144
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
Staff member
Local time
Today, 05:25
Joined
Oct 29, 2018
Messages
21,357
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

MVP / VIP
Local time
Today, 12:25
Joined
Jan 14, 2017
Messages
18,186
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.
Local time
Today, 14:25
Joined
Aug 12, 2005
Messages
144
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

Top Bottom