ADODB.Recordset - referring to a field

[Simone]

Hello

I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.

Function fxEIDAssgn(plng EID As Long) As Boolean

Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON
U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

I would like to get the user name from the strSelectUser to show up in
a message box in my form.
I get the error "object required".

Please help.
Thanks
Simone

[Fletcher Arnold]

"Simone" <oi****@hotmail .com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

Hello

I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.

Function fxEIDAssgn(plng EID As Long) As Boolean

Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON
U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

I would like to get the user name from the strSelectUser to show up in
a message box in my form.
I get the error "object required".

Please help.
Thanks
Simone

Presumably you want this to test the first half of your function? But we
don't know what the function will do when it is complete. If you wanted
just wanted to get the UserName then you would probably just use DLookUp.

So assuming the function does something else (since it returns boolean) your
problem is probably that cnnThisDB is not an open connection. If you
replace cnnThisDB with CurrentProject. Connection the function may work.

But there are other improvements to be made:

What happens if plngEID cannot be found in the field 'tblAssignedTo. EID'?
Your code will cause an error and you have no error handling.

I would write varUser = rsAssignedUser( "UserName") instead of varUser =
rsAssignedUser. Fields("UserNam e").Value It's the same thing - but less work
for you!

In the error handling you could close the recordset and set it to nothing.

There are times when you may not want to use CurrentProject. Connection. So
if you function had:
Function fxEIDAssgn(plng EID As Long, cnn As ADODB.Connectio n) As Boolean
Then you could pass an open connection to it (possibly
CurrentProject. Connection or possibly a specifically created connection)

Fletcher

[Terry Kreft]

You don't appear to have declared/set cnnThisDB

Try
Function fxEIDAssgn(plng EID As Long) As Boolean
Dim cnnThisDB as ADODB.Connectio n
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String

Set cnnThisDB = Access.CurrentP roject.Connecti on
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN " _
& "tblAssigne dTo A ON U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

Terry

"Simone" <oi****@hotmail .com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

Hello

I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.

Function fxEIDAssgn(plng EID As Long) As Boolean

Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON
U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

I would like to get the user name from the strSelectUser to show up in
a message box in my form.
I get the error "object required".

Please help.
Thanks
Simone

[Randy Harris]

I thought I might toss a comment in here in addition to the replies you've
already received. As Fletcher suggested it isn't very clear what you are
trying to accomplish with that function. My guessing here might miss the
mark badly.

It looks to me as though you are attempting to retrieve a single value from
the query (UserName). If that is the case there is no need to create a
recordset. Also, you mentioned that you wish to fill the TextBox based on
the click of a command button. If that is the case you could simply add
some code to the Click Event for the button.

(I'm not clear on where plngEID comes from)
(varUser is a peculiar name for a String variable)

Something like this aircode:

Private Sub MyCommand_Click ()

Dim varUser

varUser = CurrentProject. Connection.Exec ute ( _
"SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON U.UID = A.UID " _
& "WHERE a.EID = " & plngEID).GetStr ing

' At this point varUser should contain the name
' I like to throw away the CR GetString adds to the value
varUser = Replace(varUser , vbCR, "")

' Now push the value into your textbox
Me!MyTextBox = varUser

End Sub

Again, I'm just guessing at your requirements.
HTH
Randy Harris

"Simone" <oi****@hotmail .com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

Hello

I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.

Function fxEIDAssgn(plng EID As Long) As Boolean

Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON
U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

I would like to get the user name from the strSelectUser to show up in
a message box in my form.
I get the error "object required".

Please help.
Thanks
Simone

[Simone]

Thanks guys.

Randy you exactly right. That is exactly what I want to do. I did
declare my cnnThisDB and all the other variables.

My function is doing other things as well, my goal is to have that
code in my function working since I have 6 forms that will be using
this code.
This is an Inventory db, the user removes equipment so the function is
checking if equipment is assigned to a user, if it is a message box
pops up and let them know. My goal is to get the user name also in the
box so user know who it is assigned to.

Here is my function:

***Function fxEIDAssgn(plng EID As Long) As Boolean

Dim cnnThisDB As New ADODB.Connectio n
Dim rsAssignedEquip As ADODB.Recordset
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectSQL As String

'set the ado connection
Set cnnThisDB = New ADODB.Connectio n

'open the ado connection to this database
cnnThisDB.Open "Driver={Micros oft Access Driver (*.mdb)};" & _
"Dbq= " & gconstrThisDb & " " & _
"Uid=admin; " & _
"Pwd=;"

'set the ado recordset to the variable
Set rsAssignedEquip = New ADODB.Recordset
Set rsAssignedUser = New ADODB.Recordset

strSelectSQL = "SELECT EID " _
& "FROM tblAssignedTo " _
& "WHERE EID = " & plngEID
'open the ado recordset
rsAssignedEquip .Open strSelectSQL, cnnThisDB
'determine if BOF and EOF are both TRUE, if so, no records exist _
for EID in tblAssignedTo. Return appropriate value
If rsAssignedEquip .BOF And rsAssignedEquip .EOF Then
fxEIDAssgn = False 'no records found matching EID
Else
fxEIDAssgn = True 'records found matching EID
End If
End Function****
I want to have the varUser variable show up in a Msgbox instead of a
text box. But I did tried using as a text box and I get the error
below same as well the Msgbox:
***Either BOF or EOF is true, or the current record has been deleted.
Requested operation requires current record.***
It works sometimes but most of the times I get the error above, BTW
the record is not deleted is there.

I have a question for you "CurrentProject .Connection.Exe cute" is that
an ADO connection?
Please let me know if you have an idea why I am getting the error
above.

Thanks a bunch.
Simone
"Randy Harris" <ra***@SpamFree .com> wrote in message news:<dz******* *************@n ewssvr28.news.p rodigy.com>...

I thought I might toss a comment in here in addition to the replies you've
already received. As Fletcher suggested it isn't very clear what you are
trying to accomplish with that function. My guessing here might miss the
mark badly.

It looks to me as though you are attempting to retrieve a single value from
the query (UserName). If that is the case there is no need to create a
recordset. Also, you mentioned that you wish to fill the TextBox based on
the click of a command button. If that is the case you could simply add
some code to the Click Event for the button.

(I'm not clear on where plngEID comes from)
(varUser is a peculiar name for a String variable)

Something like this aircode:

Private Sub MyCommand_Click ()

Dim varUser

varUser = CurrentProject. Connection.Exec ute ( _
"SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON U.UID = A.UID " _
& "WHERE a.EID = " & plngEID).GetStr ing

' At this point varUser should contain the name
' I like to throw away the CR GetString adds to the value
varUser = Replace(varUser , vbCR, "")

' Now push the value into your textbox
Me!MyTextBox = varUser

End Sub

Again, I'm just guessing at your requirements.
HTH
Randy Harris

"Simone" <oi****@hotmail .com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

Hello

I hope you guys can help me. I am very new to ADO...
I am creating a ADODB connection in a module and trying to access it
from a command button in a form.

Function fxEIDAssgn(plng EID As Long) As Boolean

Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String
Set rsAssignedUser = New ADODB.Recordset
strSelectUser = "SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON
U.UID = A.UID " _
& "WHERE a.EID = " & plngEID
'open the ado recordset
rsAssignedUser. Open strSelectUser, cnnThisDB

varUser = rsAssignedUser. Fields("UserNam e").Value
End Function

I would like to get the user name from the strSelectUser to show up in
a message box in my form.
I get the error "object required".

Please help.
Thanks
Simone

[Randy Harris]

Simone, I'm not certain, but I think the reason you are getting the error is
that you are not closing the recordset. See some comments in line.

Also, to answer your other question, yes, CurrentProject. Connection is ADO.

Hoping this helps,
Randy

"Simone" <oi****@hotmail .com> wrote in message
news:fc******** *************** ***@posting.goo gle.com...

Thanks guys.

Randy you exactly right. That is exactly what I want to do. I did
declare my cnnThisDB and all the other variables.

My function is doing other things as well, my goal is to have that
code in my function working since I have 6 forms that will be using
this code.
This is an Inventory db, the user removes equipment so the function is
checking if equipment is assigned to a user, if it is a message box
pops up and let them know. My goal is to get the user name also in the
box so user know who it is assigned to.

Here is my function:

***Function fxEIDAssgn(plng EID As Long) As Boolean

Dim cnnThisDB As New ADODB.Connectio n
Dim rsAssignedEquip As ADODB.Recordset
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectSQL As String

*************** ********* 'set the ado connection
Set cnnThisDB = New ADODB.Connectio n

'open the ado connection to this database
cnnThisDB.Open "Driver={Micros oft Access Driver (*.mdb)};" & _
"Dbq= " & gconstrThisDb & " " & _
"Uid=admin; " & _
"Pwd=;" *************** **********

If you are working within the current project, you could substitute this
single line for the above.

Set cnnThisDB = CurrentProject. Connection


'set the ado recordset to the variable
Set rsAssignedEquip = New ADODB.Recordset
Set rsAssignedUser = New ADODB.Recordset

strSelectSQL = "SELECT EID " _
& "FROM tblAssignedTo " _
& "WHERE EID = " & plngEID
'open the ado recordset
rsAssignedEquip .Open strSelectSQL, cnnThisDB
'determine if BOF and EOF are both TRUE, if so, no records exist _
for EID in tblAssignedTo. Return appropriate value
If rsAssignedEquip .BOF And rsAssignedEquip .EOF Then
fxEIDAssgn = False 'no records found matching EID
Else
fxEIDAssgn = True 'records found matching EID
End If

Add these lines here:
rsAssignedEquip .Close
Set rsAssignedEquip = Nothing
End Function****
I want to have the varUser variable show up in a Msgbox instead of a
text box. But I did tried using as a text box and I get the error
below same as well the Msgbox:
***Either BOF or EOF is true, or the current record has been deleted.
Requested operation requires current record.***
It works sometimes but most of the times I get the error above, BTW
the record is not deleted is there.

I have a question for you "CurrentProject .Connection.Exe cute" is that
an ADO connection?
Please let me know if you have an idea why I am getting the error
above.

Thanks a bunch.
Simone