Select statement to run Access Query from VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MikeV06 - 21 Apr 2008 02:19 GMT

I need to run an Access query (2003) from VBA and put the result (one row,
one column) into a local variable. I have tried a public defining
pstcorpname as a string, acresult, acdataobject, and so on. I have tried

:pstrcorpname. It puts it into an Access table (pstrcorpname or
:pstrcorpname) with delete and store 1 record confirmation messages. I have

been working on this all day without getting any closer.

Any ideas?

Private Sub getcorpname()
Dim SQL As String
SQL = "SELECT [tblCorp Name].CorpName INTO pstrcorpname " & _
"FROM [tblCorp Name] " & _
"WHERE ((([tblCorp Name].Corp)=" & pstrco & "))"
MsgBox ("SQL = " & SQL)
DoCmd.RunSQL SQL
MsgBox ("pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname)
End Sub

Thanks

Mike

Reply to this Message

Dirk Goldgar - 21 Apr 2008 03:12 GMT

>I need to run an Access query (2003) from VBA and put the result (one row,
> one column) into a local variable. I have tried a public defining
[quoted text clipped - 15 lines]
> MsgBox ("pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname)
> End Sub

You can't "SELECT INTO" a local variable. You can open a recordset on a
SELECT query, and get the value from the recordset:

Dim SQL As String
Dim pstrcorpname As String

SQL = "SELECT CorpName FROM [tblCorp Name] " & _
"WHERE Corp=" & pstrco

With CurrentDb.OpenRecordset(SQL)
If Not .EOF Then
pstrcorpname = !CorpName
End If
.Close
End With

MsgBox "pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname

Or you can use the domain aggregate function DLookup to do this query
"behind the scenes" for you:

pstrcorpname = _
Nz(DLookup("CorpName", "[tblCorp Name]", "Corp=" & strco), "")

MsgBox "pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname

Note that I wrapped the DLookup call in a call to Nz(), to convert the
possible Null result into a zero-length string. That's just in case there
might not be a record on file with Corp = strco.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

MikeV06 - 21 Apr 2008 19:24 GMT

>>I need to run an Access query (2003) from VBA and put the result (one row,
>> one column) into a local variable. I have tried a public defining
[quoted text clipped - 45 lines]
> possible Null result into a zero-length string. That's just in case there
> might not be a record on file with Corp = strco.

You, sir, are a genius. Thank you very much. Both approaches worked right
out of the box (strco to pstrco in the 2nd example). The aggregate function
is really neat!

During this process I started to learn ADO recordsets as well. I have a
little problem with my test version, but will post it in a new thread.

Thank you again,

Mike

Reply to this Message