Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB: runtime error 3705 (Urgent)

Posted on 2004-04-07
14
Medium Priority
?
566 Views
Last Modified: 2013-12-25
I am getting this error:
runtime error 3705: operation is not allowed when the object is open


at this pointin my prog:
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn

before this, rs.open is executed in some other event as:
rs.Open "select [Issue Date],[Return Date] from [transaction] where [Roll no]='" & List31.Text & "'", cn, adOpenDynamic, adLockBatchOptimistic

I need this answer urgently. if any other detail is required, please tell
0
Comment
Question by:hag1
  • 8
  • 4
  • 2
14 Comments
 
LVL 6

Accepted Solution

by:
mmusante earned 600 total points
ID: 10775033
You cannot open a recordset if it is already open ... close if before re-opening
0
 
LVL 6

Assisted Solution

by:mmusante
mmusante earned 600 total points
ID: 10775050
try this ...

if rs.state <> adStateClosed then rs.close
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn
0
 

Author Comment

by:hag1
ID: 10775080
ok, solved.
I will keep the thread open in case i have to ask another silly question.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10775100
try this:

if rs.State = 1 then
    rs.close
end if
rs.Open "insert into [transaction] values '" & List31.Text & "', '" & Adodc1.Recordset.Fields(0) & "'", cn

and in the other location:

if rs.state = 1 then
    rs.close
end if
rs.Open "select [Issue Date],[Return Date] from [transaction] where [Roll no]='" & List31.Text & "'", cn, adOpenDynamic, adLockBatchOptimistic


That will check if the Recordset is already open, and if so, will close it before opening it again.

AW
0
 

Author Comment

by:hag1
ID: 10775614
When I use :
adodc4.recordsource " select [copies present] from books where[book id]='" & Adodc1.Recordset.Fields(0) & "' "

i get the error:
Invalid use of property and .fields is selected in the above statement

I read the corresponding help, but could not figure it out.

What mistake am I making?
0
 
LVL 6

Assisted Solution

by:mmusante
mmusante earned 600 total points
ID: 10775636
May be
adodc4.recordsource = " select [copies present] from books where[book id]='" & Adodc1.Recordset.Fields(0) & "' "
0
 

Author Comment

by:hag1
ID: 10775704
That was indeed the error.

gr8 for pointing it out.
0
 

Author Comment

by:hag1
ID: 10780464
This code was working without any problem

With Adodc1
.RecordSource = "select * from books where id='" & Adodc1.Recordset.Fields(0) & "'"

.Recordset.Fields(8).Value = .Recordset.Fields(8).Value + 1
.Refresh
.RecordSource = "select * from books where [copies present]>0"
.Refresh
End With


Now i deleted a column of books table thru enterprise manager.
therefore, in the above code i changed fields(8) to fields(7) and now it doesnt work
ie: It does not show any error, but no change is made in the field value
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10780503
which field did you delete form the Table, before or after what WAS field 8 before you deleted the field?

Can you list ALL of the fields in the original table, and ALL of the fields in the current table?

AW
0
 

Author Comment

by:hag1
ID: 10780533
old table:
id,title,author,publication,subject,year,cost,total copies, copies present

current table:
id,title,author,publication,subject,cost,total copies, copies present

All the columns above are in order
0
 

Author Comment

by:hag1
ID: 10780651
I was using the debugger.
for ;
.Recordset.Fields(8).Value = .Recordset.Fields(8).Value + 1
The lhs value of recordset.fields(8).value gets modified as required
When .refresh is executed, the above modification is undone
0
 

Author Comment

by:hag1
ID: 10780783
I think u must ignore the comment i made that it was working right previously. i think that the problem was there from the beginning, so it has mothing to do with deleting a column.
0
 
LVL 6

Expert Comment

by:mmusante
ID: 10781265
Can't you refer to the columns by name?

.Recordset.Fields("copies present").Value

or

.Recordset.Fields("[copies present]").Value

if spaces in column names give you any problem change your query to this:

SELECT id,title,author,publication,subject,cost,[total copies] AS total_copies, [copies present] AS copies_present FROM Books ...
0
 

Author Comment

by:hag1
ID: 10783814
After the select query, I added adodc4.recordset.update which solved the problem.

I am grateful to u people at EE for helping when I needed it the most. I have now learnt quite a few things in VB.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

879 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