igotnosmoke
asked on
Run Time Error 3704 Operation is not allowed when the object is closed
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.MovePreviou s
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.Visib le = 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.Visib le = 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.MovePreviou s
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.Visib le = 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.Locke d = False
txtSalesPersonSearch.BackC olor = vbWhite
txtSalesPersonSearch.ForeC olor = &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
Run Time Error 3704:
Operation is not allowed when the object is closed
when debugging it highlights the following:
mrsSalesPerson.MovePreviou
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
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
Call DisplayData
Else
'Call ClearData 'The "'" will be removed later.
End If
fraSalesPersonSearch.Visib
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.Visib
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.MovePreviou
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.Visib
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.Locke
txtSalesPersonSearch.BackC
txtSalesPersonSearch.ForeC
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
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.
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.MovePreviou s
endif
If yest then try
changing your code as below
if not mrsSalesPerson.bof then
mrsSalesPerson.MovePreviou
endif
ASKER
no i dont get any of the forms loading up...
ill increase the points if someone can help me out
ill increase the points if someone can help me out
ASKER
As soon as I try load the form it directly takes me to the error
You said as soon as the form loading, it jump to the line
mrsSalesPerson.MovePreviou s
on cmdPrevious_Click routine?
mrsSalesPerson.MovePreviou
on cmdPrevious_Click routine?
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.MovePreviou s change
if not mrsSalesPerson.bof then
mrsSalesPerson.MovePreviou s
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.Visib le = 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.Visib le = False
Just try it out and let me know what is hapenning now
wherever there is mrsSalesPerson.MovePreviou
if not mrsSalesPerson.bof then
mrsSalesPerson.MovePreviou
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.Visib
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.Visib
Just try it out and let me know what is hapenning now
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.
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.
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.OL EDB.4.0; Data Source=C:\TestData.mdb" 'Replace with your database path & Filename
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.OL
ASKER
Ive tried what youve told me and still has the same error,
what code would i use to activate the connection string?
what code would i use to activate the connection string?
Did you exactly as stated above and if you could copy the code in the declarations and the form load event it would help.
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.OL EDB.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.Visib le = 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.Visib le = False
End If
'Set the focus to the exit button - this can't be done on Form_Load.
Call SetInactiveControls
cmdMainMenu.SetFocus
End Sub
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
mFileName = "C:\TestData" ' Replace with your database path & filename
'Open the connection to the database
gcnMIRO.Open "Provider=Microsoft.Jet.OL
'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
Call DisplayData
Else
'Call ClearData 'The "'" will be removed later.
End If
fraSalesPersonSearch.Visib
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.Visib
End If
'Set the focus to the exit button - this can't be done on Form_Load.
Call SetInactiveControls
cmdMainMenu.SetFocus
End Sub
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.