Select statement to run Access Query from VBA

[MikeV06]

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
strcorpname. It puts it into an Access table (pstrcorpname or
strcorpname) 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

 

[Dirk Goldgar]

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
strcorpname. It puts it into an Access table (pstrcorpname or
strcorpname) 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

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.

 

[MikeV06]

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
strcorpname. It puts it into an Access table (pstrcorpname or
strcorpname) 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

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.

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