ADODB.Recordset - referring to a field

**Fletcher Arnold** · Nov 12 '05, 03:44 PM

Re: ADODB.Recordset - referring to a field

"Simone" <oimone@hotmail .com> wrote in message
news:fca7e30a.0 310270751.6d2c2 d92@posting.goo gle.com...[color=blue]
> 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[/color]

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** · Nov 12 '05, 03:44 PM

Re: ADODB.Recordset - referring to a field

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" <oimone@hotmail .com> wrote in message
news:fca7e30a.0 310270751.6d2c2 d92@posting.goo gle.com...[color=blue]
> 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[/color]

**Randy Harris** · Nov 12 '05, 03:45 PM

Re: ADODB.Recordset - referring to a field

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" <oimone@hotmail .com> wrote in message
news:fca7e30a.0 310270751.6d2c2 d92@posting.goo gle.com...[color=blue]
> 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[/color]

**Simone** · Nov 12 '05, 03:46 PM

Re: ADODB.Recordset - referring to a field

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" <randy@SpamFree .com> wrote in message news:<dzgnb.535 9$P%1.4396894@n ewssvr28.news.p rodigy.com>...[color=blue]
> 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" <oimone@hotmail .com> wrote in message
> news:fca7e30a.0 310270751.6d2c2 d92@posting.goo gle.com...[color=green]
> > 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[/color][/color]

**Randy Harris** · Nov 12 '05, 03:47 PM

Re: ADODB.Recordset - referring to a field

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" <oimone@hotmail .com> wrote in message
news:fca7e30a.0 310280538.54dde daa@posting.goo gle.com...[color=blue]
> 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
>[/color]

*************** *********[color=blue]
> '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=;"[/color]
*************** **********

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

Set cnnThisDB = CurrentProject. Connection

[color=blue]
>
> '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
>[/color]

Add these lines here:
rsAssignedEquip .Close
Set rsAssignedEquip = Nothing[color=blue]
>
> 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
>
>[/color]

ADODB.Recordset - referring to a field

ADODB.Recordset - referring to a field

Comment

Comment

Comment

Comment

Comment