473,836 Members | 1,429 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ADODB.Recordset - referring to a field

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
Nov 12 '05 #1
5 29857
"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

Nov 12 '05 #2
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

Nov 12 '05 #3
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

Nov 12 '05 #4
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

Nov 12 '05 #5
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

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
8826
by: Joe | last post by:
I want to add a new record to database, and then retrives the @@IDENTITY value for this ne added record, and the fields of this record. However, it has the following error on line varNewID = objRS("NewMsgID" ADODB.Recordset (0x800A0CC1 Item cannot be found in the collection corresponding to the requested name or ordinal sqlStmt= "INSERT INTO AddressValidation " & "(RequestXML, ResponseXML) " & "VALUES ("
0
2882
by: elcc1958 | last post by:
I need to support a VB6 application that will be receiving disconnected ADODB.Recordset from out DotNet solution. Our dotnet solution deals with System.Data.DataTable. I need to populate a disconnected ADODB.Recordset from System.Data.DataTable data. Below is the source code I am implementing to test the process. I do not get any error, that I can see. The problem I have is that at the end, the recordset seems to be empty. Any...
0
2949
by: Phill | last post by:
Is there a simple way to pass a parameter to an ADO recordset instead of using a hard coded where statement like below(something like forms!aaa!fieldname). I've done a fair bit of searching but can not seem to come up with a way to provide this in a simple manner. I have done this using pass through queries and stored procedures (as a form based search method)but I guess I am just experimenting to see if the below is a more efficient way...
0
2691
by: CFW | last post by:
I thought this was going to be easy but I'm missing something . . . I need to open an ADODB recordset using the recordset source for a list box on my for. When my form opens, the list box ADODB recordset is established and set during On Open. Next I want to populate a recordset from that list box so I can filter it on a single field using the value of a combo box for the filter string. I have a second combo box that i woul like to use...
3
6633
by: | last post by:
Hello ppl, I have snippet that works fine udner ADODB with VB6, but something wrong with it in Vb.NET. Anyone can help? Recordset1 (ADODB.Recordset) Error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. Error Source: SELECT * FROM Accounts
0
1553
by: PCroser | last post by:
I have encountered a problem when querying a table. The query passed data into a recordset which should have resulted in many records but has returned EOF. After debugging the code the only solution i have found is that i have set the recordset to NEW ADODB.RECORDSET two times before actually perfoming the rs.open (once in the main program and once in the dll which contains the loadrecords function). MAIN PROGRAM Dim rs as NEW...
1
10161
by: sphinney | last post by:
All, I have a ADODB.Recordset in my Access 2002 project. I've been able to successfully add fields to the record set. According the the MS Access help files, I now must update the recordset to get the field additions to "stick." For example, the code below shows how the Access help files say to append fields to a ADO recordset. dim ADO_RS as ADODB.Recordset set ADO_RS = (some other ADODB recordset) ADO_RS.Close...
6
5179
by: Oko | last post by:
I'm currently developing an MS Access Data Project (.adp) in MS Access 2002. One of the reports within the DB uses data that is Dynamic and cannot be stored on the SQL Server. To resolve this, I have created an ADODB.Recordset in the reports OPEN event, built the necessary records inside of it, and then bound the report to this newly created recordset. Here's the rub:
6
4922
by: avcitamer | last post by:
We upgraded our system and problem below occured, pleas help me... Windows 2003 server SP1 When I set a decimal DB field value to "123,32" (using ADODB.recordset ) updated value was ok (123.32) Windows 2003 server SP2 Now when I set a decimal DB field value to "123,32" (using ADODB.recordset ) updated value is (123.32)
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10854
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10558
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10600
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6981
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4459
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.