?
Solved

Run Time Error 3704 Operation is not allowed when the object is closed

Posted on 2004-11-23
13
Medium Priority
?
5,986 Views
Last Modified: 2012-06-21
Hi im trying to connect a database to my form although im recieving the following error:

Run Time Error 3704:
Operation is not allowed when the object is closed

when debugging it highlights the following:

mrsSalesPerson.MovePrevious

Can someone give me a hand in resolving the error by glancing at the code, and identify exactly what im missing? Thanks :)


Here is the form frmSalesPerson
----------------------------------------------
Option Explicit
'** Declare module-level variables
Private mrsSalesPerson As New ADODB.Recordset
Private mstMode As String
Private mcoControl As Control
Private mstSalesPersonNo As String
Private pstSalesPersonSQL As Control

Private Sub Form_Load()

'Declare the recordset. Note that pstStudents is only used in this procedure,
'whereas mrsStudents is going to be used by a number of procedures.
Dim pstSalesPersonSQL As String
frmSalesPerson.WindowState = 2
pstSalesPersonSQL = "SELECT * FROM tblSalesPerson WHERE SalesPersonStatus = True ORDER BY SalesPersonNo"
mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText

'Display the first record, if there is one. Show nothing if there isn't one.
If mrsSalesPerson.RecordCount > 0 Then
    Call DisplayData
Else
    'Call ClearData 'The "'" will be removed later.
   
End If

fraSalesPersonSearch.Visible = False

End Sub

Private Sub Form_Activate()
If gstFromWhere = "frmStudentSearch" Then
    txtSalesPersonNo.Text = gstSalesPersonNo
   
   
   With mrsSalesPerson
    .MoveFirst
    .Find "SalesPersonNo = '" & txtSalesPersonNo.Text & "'"
    Call DisplayData
    End With
    gstFromWhere = ""
    txtSalesPersonSearch.Visible = False
   
    End If
   
'Set the focus to the exit button - this can't be done on Form_Load.

Call SetInactiveControls
cmdMainMenu.SetFocus
End Sub

Private Sub cmdAdd_Click()

mstMode = "Add"
lblHeading.Caption = "Add Sales Person"
Call ClearData
Call HideViewButtons
mstSalesPersonNo = txtSalesPersonNo.Text
Call SetActiveControls
cmdSave.Visible = True
cmdCancel.Visible = True

End Sub

Private Sub cmdEdit_Click()

mstMode = "Edit"
lblHeading.Caption = "Edit Sales Person"
Call SetActiveControls
Call HideViewButtons
mstSalesPersonNo = txtSalesPersonNo.Text

End Sub

Private Sub cmdDelete_Click()

Dim pstDeleteMsg As String
Dim pinResponse As Integer
Dim pstDeleteSalesPersonSQL As String
Dim prsDeleteSalesPerson As New ADODB.Recordset
pstDeleteMsg = "Are you sure that you want to remove this Sales Person?"
pinResponse = MsgBox(pstDeleteMsg, vbCritical + vbYesNo, "Delete Sales Person?")
If pinResponse = vbYes Then

With mrsSalesPerson

    !SalesPersonStatus = False
   
    .Update
   
    .Requery
   
End With
Call DisplayData
End If

End Sub

Private Sub cmdFirst_Click()
mrsSalesPerson.MoveFirst
Call DisplayData
End Sub

Private Sub cmdLast_Click()
mrsSalesPerson.MoveLast
Call DisplayData
End Sub

Private Sub cmdClose_Click()
frmMainMenu.Show 1
End Sub

Private Sub cmdPrevious_Click()
'Move the pointer to the previous record if not already at the Begining of the File.
mrsSalesPerson.MovePrevious
If mrsSalesPerson.BOF Then
    MsgBox "You are at the Start of the  Sales Persons records.", vbCritical, "Begining of table"
    mrsSalesPerson.MoveFirst
Else
    Call DisplayData
End If
End Sub

Private Sub cmdSearch_Click()
'Makes the  Sales Person search frame visible on the screen
Call HideViewButtons
fraSalesPersonSearch.Visible = True
cmdSave.Visible = False
cmdCancel.Visible = False
Call ClearData

End Sub

Private Sub cmdMainMenu_Click()
'Close frmSalesPerson before returning to frmMainMenu, which has remained open.
Unload frmSalesPerson
Set frmSalesPerson = Nothing
End Sub

Sub DisplayData()

With mrsSalesPerson
txtSalesPersonNo.Text = "" & !SalesPersonNo
txtSalesPersonName.Text = "" & !SalesPersonName
Call SetInactiveControls
End With

End Sub

Sub SetInactiveControls()

For Each mcoControl In Me.Controls
    If TypeOf mcoControl Is TextBox Then
        mcoControl.BackColor = &H8000000D
        mcoControl.ForeColor = vbWhite
        mcoControl.Locked = True
    End If
Next mcoControl
txtSalesPersonSearch.Locked = False
txtSalesPersonSearch.BackColor = vbWhite
txtSalesPersonSearch.ForeColor = &H8000000D

End Sub

Sub ShowViewButtons()

fraNavigation.Visible = True
fraFunctions.Visible = True
cmdMainMenu.SetFocus

End Sub

Sub HideViewButtons()

fraNavigation.Visible = False
fraFunctions.Visible = False
fraStudentSearch.Visible = False

End Sub

Sub ClearData()
For Each mcoControl In frmSalesPerson.Controls
  If TypeOf mcoControl Is TextBox Then
  mcoControl.Text = ""
  End If
Next
End Sub

Sub SetActiveControls()

For Each mcoControl In Me.Controls
    If TypeOf mcoControl Is TextBox Then
        If mstMode = "Add" Then
            mcoControl.Text = ""
        End If
        mcoControl.Locked = False
        mcoControl.BackColor = vbWhite
        mcoControl.ForeColor = vbBlack
    End If
Next mcoControl

txtSalesPersonNo.SetFocus

End Sub
-----------------------------------------------

Here is the Module Form i got:

------------------------------------------------

Option Explicit
'**Declare a New ADODB connection object variable
Public gcnMIRO As New ADODB.Connection

Sub Main()
'**Set the connection object to the database once only in a project
If Left(App.Path, 2) <> "\\" Then
    ChDrive App.Path
End If
ChDir App.Path
'**Open the connection to the database
gcnMIRO.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = MIRO.mdb"
frmSalesPerson.Show
End Sub

Sub CloseConnection()
gcnMIRO.Close
Set gcnMIRO = Nothing
End Sub
0
Comment
Question by:igotnosmoke
13 Comments
 
LVL 18

Expert Comment

by:Sethi
ID: 12653799
The error is because at the time of using the MovePrevious method either the recordset named mrsSalesPerson was not opened or it was closed by some other line that executed before this line.
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12653817
Firstly do you get any data displayed when the form loads up in the text boxes txtSalesPersonNo & txtSalesPersonName

If yest then try

changing your code as below
if not mrsSalesPerson.bof then
      mrsSalesPerson.MovePrevious
endif
0
 

Author Comment

by:igotnosmoke
ID: 12653933
no i dont get any of the forms loading up...
ill increase the points if someone can help me out
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:igotnosmoke
ID: 12653984
As soon as I try load the form it directly takes me to the error
0
 
LVL 26

Expert Comment

by:EDDYKT
ID: 12654161
You said as soon as the form loading, it jump to the line

mrsSalesPerson.MovePrevious


on cmdPrevious_Click routine?
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12654285
On going through you code more carefully, I have noticed that a few bugs. Just try these and let me know what happens

wherever there is  mrsSalesPerson.MovePrevious change
if not mrsSalesPerson.bof then
      mrsSalesPerson.MovePrevious
endif
a

and likewise for .MoveFirst, .MoveLast, MoveNext.

as such there is a code in Form Activate which is as below

If gstFromWhere = "frmStudentSearch" Then
    txtSalesPersonNo.Text = gstSalesPersonNo
   
   
   With mrsSalesPerson
    .MoveFirst
    .Find "SalesPersonNo = '" & txtSalesPersonNo.Text & "'"
    Call DisplayData
    End With
    gstFromWhere = ""
    txtSalesPersonSearch.Visible = False
   
    End If

Firstly I do not see a declaration for gstFromWhere and secondly you will have to change the code as below
If gstFromWhere = "frmStudentSearch" Then
    txtSalesPersonNo.Text = gstSalesPersonNo
   
   
   With mrsSalesPerson
    If not .bof then
          .MoveFirst
    endif
    .Find "SalesPersonNo = '" & txtSalesPersonNo.Text & "'"
    if not .eof then
         Call DisplayData
    endif
    End With
    gstFromWhere = ""
    txtSalesPersonSearch.Visible = False


Just try it out and let me know what is hapenning now
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12654312
Just realised that I do not see a connection string anywhere in the declarations.

You will have to provide the connectionstring for gcnMIRO and open the connection before you can open the recordset. This would be main reason you will be getting the error 'Operation is not allowed when the object is closed' as the connection to the database has not been set in the first place.
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12654374
Just add these lines in your declarations
private gcnMIRO As New ADODB.Connection

presuming that you are using an access database add this code in your form load event at the top

gcnMIRO.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\TestData.mdb" 'Replace with your database path & Filename
0
 

Author Comment

by:igotnosmoke
ID: 12654461
Ive tried what youve told me and still has the same error,
what code would i use to activate the connection string?
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12654523
Did you exactly as stated above and if you could copy the code in the declarations and the form load event it would help.
0
 
LVL 1

Expert Comment

by:JasonRodrigues
ID: 12654657
I have included you code with the changes. Moreover I have tried it out and it work fine with Access 2000 database. In case you have a different version or have set a password for the database you will have to change the connection string accordingly. Also if you have a different user name for your database change the connection string accordingly


Option Explicit
'** Declare module-level variables
private gcnMIRO As New ADODB.Connection
Private mrsSalesPerson As New ADODB.Recordset
Private mstMode As String
Private mcoControl As Control
Private mstSalesPersonNo As String
Private pstSalesPersonSQL As Control

Private Sub Form_Load()

'Declare the recordset. Note that pstStudents is only used in this procedure,
'whereas mrsStudents is going to be used by a number of procedures.
Dim pstSalesPersonSQL As String
Dim mFileName As String

frmSalesPerson.WindowState = 2
mFileName = "C:\TestData" ' Replace with your database path & filename

'Open the connection to the database
gcnMIRO.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & FileName

'Set the SQL query
pstSalesPersonSQL = "SELECT * FROM tblSalesPerson WHERE SalesPersonStatus = True ORDER BY SalesPersonNo"

'Open the recordset
mrsSalesPerson.Open pstSalesPersonSQL, gcnMIRO, adOpenStatic, adLockOptimistic, adCmdText

'Display the first record, if there is one. Show nothing if there isn't one.
If mrsSalesPerson.RecordCount > 0 Then
    Call DisplayData
Else
    'Call ClearData 'The "'" will be removed later.
   
End If

fraSalesPersonSearch.Visible = False

End Sub

Private Sub Form_Activate()
If gstFromWhere = "frmStudentSearch" Then
    txtSalesPersonNo.Text = gstSalesPersonNo
   
   
   With mrsSalesPerson
   If not .BOF then
        .MoveFirst
   Endif
    .Find "SalesPersonNo = '" & txtSalesPersonNo.Text & "'"
   if not .EOF then
        Call DisplayData
   Endif
    End With
    gstFromWhere = ""
    txtSalesPersonSearch.Visible = False
   
    End If
   
'Set the focus to the exit button - this can't be done on Form_Load.

Call SetInactiveControls
cmdMainMenu.SetFocus
End Sub
0
 

Author Comment

by:igotnosmoke
ID: 12654686
Ok i replaced the
line
public gcnMIRO As New ADODB.Connection
to
private gcnMIRO As New ADODB.Connection
in the module form

and added
private gcnMIRO As New ADODB.Connection
in the salesperson form

the error is not coming up although i assume ive gotten that to work... cause its now pointing to a different error

its now a Compile error and points to the "gstFromWhere"... saying that it has not being defined... what should i define that as?

would you like to help me with the remainding problems and keep adding to the point value? or would u like me to give you the 110 points?
0
 
LVL 1

Accepted Solution

by:
JasonRodrigues earned 440 total points
ID: 12662127

I had pointed it out earlier that you had not declared gstFromWhere earlier but I could not understand for what purpose you are using it. I presumed that it might be a global variable and you are checking the name of the form from where you are calling this particular form.

If you could explain a little more on this then I could perhaps help you out better.

About the points I leave it up to you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question