473,232 Members | 1,586 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,232 software developers and data experts.

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(plngEID 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("UserName").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 29722
"Simone" <oi****@hotmail.com> wrote in message
news:fc**************************@posting.google.c om...
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(plngEID 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("UserName").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("UserName").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(plngEID As Long, cnn As ADODB.Connection) 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(plngEID As Long) As Boolean
Dim cnnThisDB as ADODB.Connection
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectUser As String
Dim varUser As String

Set cnnThisDB = Access.CurrentProject.Connection
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("UserName").Value
End Function

Terry

"Simone" <oi****@hotmail.com> wrote in message
news:fc**************************@posting.google.c om...
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(plngEID 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("UserName").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.Execute ( _
"SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON U.UID = A.UID " _
& "WHERE a.EID = " & plngEID).GetString

' 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.google.c om...
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(plngEID 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("UserName").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(plngEID As Long) As Boolean

Dim cnnThisDB As New ADODB.Connection
Dim rsAssignedEquip As ADODB.Recordset
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectSQL As String

'set the ado connection
Set cnnThisDB = New ADODB.Connection

'open the ado connection to this database
cnnThisDB.Open "Driver={Microsoft 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.Execute" 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********************@newssvr28.news.prodig y.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.Execute ( _
"SELECT U.UserName " _
& "FROM tblUsers U INNER JOIN tblAssignedTo A ON U.UID = A.UID " _
& "WHERE a.EID = " & plngEID).GetString

' 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.google.c om...
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(plngEID 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("UserName").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.google.c om...
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(plngEID As Long) As Boolean

Dim cnnThisDB As New ADODB.Connection
Dim rsAssignedEquip As ADODB.Recordset
Dim rsAssignedUser As ADODB.Recordset
Dim strSelectSQL As String

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

'open the ado connection to this database
cnnThisDB.Open "Driver={Microsoft 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.Execute" 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
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 =...
0
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...
0
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...
0
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...
3
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...
0
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...
1
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...
6
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...
6
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)...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.