Retrieving Data from Recordset

  • Thread starter Thread starter TIML
  • Start date Start date
T

TIML

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

.......................
 
TIML said:
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

......................
Click to expand...

Change that line from
rs.Open sql, ActiveConnection, adOpenDynamic, adLockOptimistic
Click to expand...

to:

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

Also, bear in mind that a line like this:
Set myform = [Form_Project Issue Log]
Click to expand...

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]
 
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


Dirk Goldgar said:
TIML said:
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

......................
Click to expand...

Change that line from
rs.Open sql, ActiveConnection, adOpenDynamic, adLockOptimistic
Click to expand...

to:

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

Also, bear in mind that a line like this:
Set myform = [Form_Project Issue Log]
Click to expand...

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]

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top