Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Access VBA -- Need to run SQL Select statement

Hi Everyone,

I am trying to run an SQL Statement and have the result display in an unbounded text box.
The DoCmd.Runsql does not work with Select Statements.

All I want is for the result of the SQL Statement to be placed in a variable so I can work with it.

Do I have to use some ADODB commands at the beginning.

Here is the statment in my code:

Dim InfoQueried As String

InfoQueried = "Select distinct dbo_events.[event_name] from dbo_events where dbo_events.[EventID_Outreach] = 'Me.cmb_EventID';"

-----
Note: Me.cmb_EventID is a comboBox which provides me an integer that I would use in the query.
0
jonsuns7
Asked:
jonsuns7
  • 3
1 Solution
 
mbizupCommented:
Use DLookup instead:

InfoQueried = DLookup("event_name", "dbo_events", "EventID_Outreach  = '" & Me.cmb_EventID & "'")

0
 
mbizupCommented:
That assumes that EventID_Outreach is text. If it is numeric, the syntax is a little different:

InfoQueried = DLookup("event_name", "dbo_events", "EventID_Outreach  = " & Me.cmb_EventID)
0
 
mbizupCommented:
Also since InfoQueried is declared as a string, you should concatenate an empty string to the DLookup to avoid 'invalid use of null' errors if no event names are found for the given criteria:

For Text EventID_Outreach:

InfoQueried = "" &  DLookup("event_name", "dbo_events", "EventID_Outreach  = '" & Me.cmb_EventID & "'")

Open in new window

For Numeric EventID_Outreach:

InfoQueried = "" &  DLookup("event_name", "dbo_events", "EventID_Outreach  = " & Me.cmb_EventID )

Open in new window

This is a good related article by Jim Dettman:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

(Please vote 'yes' on the article if you find it helpful)
0
 
Gary ColtharpSr. Systems EngineerCommented:
In the Change event for your combobox you could have something like:

Dim InfoQueried As String

InfoQueried = "Select distinct dbo_events.[event_name] from dbo_events where dbo_events.[EventID_Outreach] = " & Me.cmb_EventID & ""

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset(InfoQueried)
 If Not rst.EOF Then
  Me.Text4 = rst![event_name]
 End If
 
 rst.Close
 
 Set rst = Nothing
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now