Store a sql select statement, ran from VBA, in a numeric variable

Question

I'm working on creating a dynamic pass-through query and in order to do so I first need to query my local db and get an ID. This ID is what I will put into my pass-through query for my WHERE clause of the query.

My string:

getCorpID = "SELECT corpID " & _
            "FROM dbo_corp " & _
            "WHERE name = " & Forms.frmMain.Combo4.Value

I'm then trying to do something akin to:

CorpID (integer) = docmd.runsql (getCorpID)

I realize, however that docmd runsql doesn't work with select statements, or return a value even. What can I use to run my string

getCorpId

as sql and store the result (It will only be one result, every time.. one number) in my variable CorpID

Thank you.

Note that constructing a query this way leaves you open to SQL injection. If you're going to do database development, you should be familiar with this subject. — user565869
– user565869, Commented Jan 21, 2014 at 18:26
You can include quite a lot in a combo so why not just refer to a combo column? — Fionnuala
– Fionnuala, Commented Jan 21, 2014 at 18:29
Jon, I'm just an intern and not even in my senior year of undergrad. Just learning it all as I go. — HVMP
– HVMP, Commented Jan 21, 2014 at 18:59
@user3191081: No problem, everyone starts at the beginning. Just please do cover that before releasing production code! If you bring it up before your boss does, he or she may be impressed. — user565869
– user565869, Commented Jan 21, 2014 at 19:47
@JonofAllTrades I ended up talking to a co-worker about the method I was attemptiong and he introduced me a bit to ADO, we made sure to close the connection at the end of the sub, I think this is what you were referring to. Thanks for the heads up, good to know people are trying to keep us amateurs from too many major mistakes. — HVMP
– HVMP, Commented Jan 21, 2014 at 19:57

Dmitry Pavliv · Accepted Answer · 2014-01-21 18:22:56Z

1

Consider about using Recordset :

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim getCorpID As String
Dim CorpID 

Set dbs = CurrentDb


getCorpID = "SELECT corpID " & _
        "FROM dbo_corp " & _
        "WHERE name = " & Forms.frmMain.Combo4.Value
Set rsSQL = dbs.OpenRecordset(getCorpID , dbOpenSnapshot)
rsSQL.MoveFirst
CorpID = rsSQL.Fields("corpID")

answered Jan 21, 2014 at 18:22

Dmitry Pavliv

35.9k13 gold badges85 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HVMP Over a year ago

Thanks, this definitely got me moving in the right direction.

Collectives™ on Stack Overflow

Store a sql select statement, ran from VBA, in a numeric variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related