Solved

VB: runtime error 3705 (Urgent)

Posted on 2004-04-07
14
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding to a VBA? 6 90
Microsoft Access combo box help 2 57
Add a task in Outlook from access 11 43
VB 6 error 5 in windows 10 but not in XP 7 71
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…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…

738 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