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: 120
Thanks: 64
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: 4,068
Thanks: 45
Thanked 962 Times in 944 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   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
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,966
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
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.
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: 120
Thanks: 64
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: 4,068
Thanks: 45
Thanked 962 Times in 944 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   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: 120
Thanks: 64
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: 4,068
Thanks: 45
Thanked 962 Times in 944 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   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: 120
Thanks: 64
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: 120
Thanks: 64
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: 4,068
Thanks: 45
Thanked 962 Times in 944 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
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 offline   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
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,066
Thanks: 110
Thanked 2,732 Times in 2,494 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
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.

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
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: 120
Thanks: 64
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
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,066
Thanks: 110
Thanked 2,732 Times in 2,494 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
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.

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
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 02:47 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