Retrieving Data from Recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TIML - 20 Sep 2005 15:31 GMT

I am trying to use the following code to grab data from a table to create a
variable, but it keeps blowing up. Any suggestions would be appreciated.

Thanks!!
Tim

Sub EmailComplete()
'On Error GoTo exitapp
Dim myform As Form, myProject As String, myIssue As String, myManager As
String, sqlEmail As String, myEmail As String
Dim rs As ADODB.Recordset, sql As String

Set myform = [Form_Project Issue Log]
sql = "SELECT email FROM tblManager WHERE Name = '" & myform.[Opened By] &
"'"
Set rs = New ADODB.Recordset

***It blows up right here*** rs.Open sql, ActiveConnection, adOpenDynamic,
adLockOptimistic

myEmail = rs.Fields(0).Value

......................

Reply to this Message

Dirk Goldgar - 20 Sep 2005 16:00 GMT

> I am trying to use the following code to grab data from a table to
> create a variable, but it keeps blowing up. Any suggestions would be
[quoted text clipped - 20 lines]
>
> ......................

Change that line from

> rs.Open sql, ActiveConnection, adOpenDynamic, adLockOptimistic

to:

rs.Open sql, CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

Also, bear in mind that a line like this:

> Set myform = [Form_Project Issue Log]

will only work if the form "Project Issue Log" has a code module, and it
will open the form if it isn't open. I prefer to make form references
via the Forms collection, like this:

Set myform = Forms![Project Issue Log]

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

TIML - 20 Sep 2005 16:35 GMT

Thank you very much Dirk !!!!

It worked like a charm. It is nice to know that people will give up their
own time to help others that they have never even met before :)

Tim

>> I am trying to use the following code to grab data from a table to
>> create a variable, but it keeps blowing up. Any suggestions would be
[quoted text clipped - 39 lines]
>
> Set myform = Forms![Project Issue Log]

Reply to this Message