Getting Query Value With VBA (1 Viewer)

J

Jonny

Registered User.
Local time
Today, 04:27
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?
 
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.
 
Jonny said:
Yes, thanks, that is the direction. Will try shortly..
Click to expand...
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!
 
theDBguy said:
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!
Click to expand...
I will certainly do, thank you, Sir!
 
theDBguy said:
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.
Click to expand...
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
 
Jonny said:
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
Click to expand...
Congratulations! Glad to hear you got it working. Good luck with your project.
 
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.
 
isladogs said:
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.
Click to expand...
Thank you!
 
You must log in or register to reply here.

Similar threads

brucemc777
Insert SQL generated by Access Fails when i try to run it in VBA
Replies
9
Views
264
Gasman
Gasman
S
Solved Select Into Query with varible issue
Replies
18
Views
582
silentwolf
S
A
Run SQL based on SQL result
Replies
2
Views
129
arnelgp
arnelgp
M
Looking for Faster way to update Table - boolean field
2 3
Replies
49
Views
1,133
cheekybuddha
cheekybuddha
D
Insert entry in table with only default values
Replies
11
Views
313
Pat Hartman
P

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom