Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2019, 07:02 AM   #1
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Getting Query Value With VBA

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?

Jonny is offline   Reply With Quote
Old 08-13-2019, 07:11 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Getting Query Value With VBA

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 07:59 AM   #3
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Getting Query Value With VBA

I think that is for action query statements only.?

I seem to recall falling foul to that a good while back.?

https://docs.microsoft.com/en-us/off...s.docmd.runsql

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 08:14 AM   #4
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Getting Query Value With VBA

Quote:
Originally Posted by Gasman View Post
I think that is for action query statements only.?

I seem to recall falling foul to that a good while back.?

https://docs.microsoft.com/en-us/off...s.docmd.runsql
This one works for UPDATE but not for SELECT statement.
Jonny is offline   Reply With Quote
Old 08-13-2019, 08:28 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Getting Query Value With VBA

Quote:
Originally Posted by Jonny View Post
This one works for UPDATE but not for SELECT statement.
Hi Jonny. Did you see my post above? Do you have any questions about it?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 08:30 AM   #6
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Getting Query Value With VBA

Quote:
Originally Posted by theDBguy View Post
Hi Jonny. Did you see my post above? Do you have any questions about it?
Yes, thanks, that is the direction. Will try shortly..
Jonny is offline   Reply With Quote
Old 08-13-2019, 08:33 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Getting Query Value With VBA

Quote:
Originally Posted by Jonny View Post
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!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 08:45 AM   #8
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Getting Query Value With VBA

Quote:
Originally Posted by theDBguy View Post
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 is offline   Reply With Quote
Old 08-13-2019, 11:23 AM   #9
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Re: Getting Query Value With VBA

Quote:
Originally Posted by theDBguy View Post
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
Jonny is offline   Reply With Quote
Old 08-13-2019, 11:34 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,963
Thanks: 57
Thanked 1,297 Times in 1,278 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Getting Query Value With VBA

Quote:
Originally Posted by Jonny View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 11:35 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,064
Thanks: 115
Thanked 3,021 Times in 2,748 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Getting Query Value With VBA

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Jonny (08-13-2019)
Old 08-13-2019, 11:48 AM   #12
Jonny
Newly Registered User
 
Join Date: Aug 2005
Posts: 136
Thanks: 76
Thanked 0 Times in 0 Posts
Jonny is on a distinguished road
Thumbs up Re: Getting Query Value With VBA

Quote:
Originally Posted by isladogs View Post
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!
Jonny is offline   Reply With Quote
Old 08-13-2019, 02:11 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,064
Thanks: 115
Thanked 3,021 Times in 2,748 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Getting Query Value With VBA

You're welcome

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Jonny (08-13-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 01:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World