Solved

VB: runtime error 3705 (Urgent)

Posted on 2004-04-07
14
554 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 150 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 150 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
Industry Leaders: 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 150 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

756 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