Solved

VB: runtime error 3705 (Urgent)

Posted on 2004-04-07
14
549 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 55
Copy a row 12 62
vb6, excel Run-time error 1004 method 'close' of object 'workbooks' failed 7 53
which modules are active in VB6 project? 6 12
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

777 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