?
Solved

runtime error

Posted on 2000-04-14
10
Medium Priority
?
1,034 Views
Last Modified: 2012-06-21
My program is a question database. It lets users choose how many questions they want and about what subject.
The program runs a query on subject, which creates a recordset rstVragen.
Then  a piece of code chooses at random the number of questions the user wants out of rstvragen and puts the result in a new recorset called rstRandom.
The questions in rstRandom are then presented to the user, who can answer the question and the question is than checked if it is correct.
My problem is that the program generates the same error

Runtime error (-2147467259(8004005)
Insufficient or incorrect column information, too many rows affected by update

But never at the same time, sometimes it gives the error after 10 question, sometimes after 1 question etc.
It gives the error at
rstRandom.MoveNext in sub volgendevraag()

This is most of the code. I now it isn’t high standard programming but I started programming in VB only 3 months ago. So don’t bite my head off.

I'm using the dataenvironment with an access database


Public rstVragen As ADODB.Recordset
Public rstRandom As ADODB.Recordset
Public boAntwoordGegeven As Boolean
Public bCorrect As Byte
Public strVraag As String
Public strAntwoord1 As String
Public strMogelijkheid1 As String
Public strMogelijkheid2 As String
Public strMogelijkheid3 As String
Public strMogelijkheid4 As String
Public strUitleg As String
Public bTypevraag As Byte
Public iOnderwerp As Integer

*****************************
Sub Initialize()

DataEnvironment1.Connection1.Open
Set rstVragen = New ADODB.Recordset
Set rstRandom = New ADODB.Recordset

End Sub

*****************************

Sub RandomVragen()
           
Dim x() As Long
Dim c As Long, d As Long, irec As Long
Dim iNo As Long

Form6.Show
Form6.Refresh

DataEnvironment1.Command2

Randomize

If Form5.txtAantalvragen.Text > rstVragen.RecordCount Then
    iNo = rstVragen.RecordCount
Else
    iNo = Form5.txtAantalvragen.Text
End If

ReDim x(iNo)

rstVragen.MoveLast
rstVragen.MoveFirst

If rstRandom.State = adStateOpen Then
    rstRandom.Close
End If

rstRandom.CursorType = adOpenKeyset
rstRandom.LockType = adLockOptimistic
rstRandom.Open "randomvragen", DataEnvironment1.Connection1

For c = 1 To iNo
retry:
    irec = Int(Rnd * rstVragen.RecordCount) + 1
    For d = 1 To c - 1
        If irec = x(d) Then GoTo retry
    Next
   
    rstVragen.AbsolutePosition = irec
    x(c) = irec
       
    If Len(rstVragen!correct) >= 1 Then
        bCorrect = rstVragen!correct
    Else
        bCorrect = 0
    End If
    If Len(rstVragen!vraag) >= 1 Then
        strVraag = rstVragen!vraag
    Else
        strVraag = " "
    End If
    If Len(rstVragen!antwoord1) >= 1 Then
        strAntwoord1 = rstVragen!antwoord1
    Else
        strAntwoord1 = " "
    End If
    If Len(rstVragen!mogelijkheid1) >= 1 Then
        strMogelijkheid1 = rstVragen!mogelijkheid1
    Else
        strMogelijkheid1 = " "
    End If
    If Len(rstVragen!mogelijkheid2) >= 1 Then
        strMogelijkheid2 = rstVragen!mogelijkheid2
    Else
        strMogelijkheid2 = " "
    End If
    If Len(rstVragen!mogelijkheid3) >= 1 Then
        strMogelijkheid3 = rstVragen!mogelijkheid3
    Else
        strMogelijkheid3 = " "
    End If
    If Len(rstVragen!mogelijkheid4) >= 1 Then
        strMogelijkheid4 = rstVragen!mogelijkheid4
    Else
        strMogelijkheid4 = " "
    End If
    If Len(rstVragen!uitleg) >= 1 Then
        strUitleg = rstVragen!uitleg
    Else
        strUitleg = " "
    End If
    If Len(rstVragen!typevraag) >= 1 Then
        bTypevraag = rstVragen!typevraag
    End If
    If Len(rstVragen!onderwerp) >= 1 Then
        iOnderwerp = rstVragen!onderwerp
    End If
    With rstRandom
    .AddNew
    !correct = bCorrect
    !vraag = strVraag
    !antwoord1 = strAntwoord1
    !mogelijkheid1 = strMogelijkheid1
    !mogelijkheid2 = strMogelijkheid2
    !mogelijkheid3 = strMogelijkheid3
    !mogelijkheid4 = strMogelijkheid4
    !uitleg = strUitleg
    !typevraag = bTypevraag
    !onderwerp = iOnderwerp
    .Update
    End With
Next

rstRandom.MoveFirst
If rstRandom.RecordCount < 1 Then
   MsgBox "Geen vragen gevonden"
Else
    Form3.cmdTest.Enabled = True
End If

Unload Form6
Unload Form5
End Sub

*****************************

Sub Volgendevraag()
boAntwoordGegeven = False
rstRandom.MoveNext
If rstRandom.EOF = True Then
    MsgBox "Dit was de laatste vraag. U kunt u score zien onder de uitslag button.", vbOKOnly, "Einde"
    Form3.cmdUitslag.Enabled = True
    Unload Form1
    Unload Form2
Else
    If rstRandom![typevraag] = 2 Then
        Form2.Show
        Form2.txtUitleg = rstRandom![uitleg]
        Form2.TxtVraag = rstRandom![vraag]
        Form2.Option1.Caption = rstRandom![mogelijkheid1]
        Form2.Option2.Caption = rstRandom![mogelijkheid2]
        Form2.Option3.Caption = rstRandom![mogelijkheid3]
        Form2.Option4.Caption = rstRandom![mogelijkheid4]
        Form2.Option1.Value = False
        Form2.Option2.Value = False
        Form2.Option3.Value = False
        Form2.Option4.Value = False
        Form2.ImOk.Visible = False
        Form2.txtUitleg.Visible = False
        Form2.imExcla.Visible = False
        Form2.cmdNext.SetFocus
    Else
        Form1.Show
        Form1.ImOk.Visible = False
        Form1.imExcla.Visible = False
        Form1.txtUitleg.Visible = False
        Form1.txtAntwoord.Text = ""
        Form1.txtAntwoord.SetFocus
        Form1.txtUitleg = rstRandom![uitleg]
        Form1.TxtVraag = rstRandom![vraag]
        Form1.txtMogelijkheid1 = rstRandom![mogelijkheid1]
    End If
End If
0
Comment
Question by:RonaldBiemans
[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
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2716279
Which DB Engine are you using?

Try setting client side cursor:
rstRandom.CursorType = adOpenKeyset
rstRandom.LockType = adLockOptimistic
rstRandom.CursorLocation = asUseClient
rstRandom.Open "randomvragen", DataEnvironment1.Connection1

Do this fof rstVragen too!
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 2716336
emoreau,

the line rstRandom.CursorLocation = asUseClient gives me another runtime error 3001

Using MSjet oledb4.0
with an access databases

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2716356
Oups. Typo error!!!

rstRandom.CursorLocation = adUseClient
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Author Comment

by:RonaldBiemans
ID: 2716445
Unfortunately that didn't make a difference
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2716494
Try this:

Sub Volgendevraag()
boAntwoordGegeven = False
if not rstrandom.eof then rstRandom.MoveNext
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 2716572
Sorry, didn't work.
I think if it was something like that,he would give me an error everytime.
But that is just the problem, sometimes he gives the error after 10 or 15 questions.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2716814
Check this section of code.. to make sure that .RecordCount > 0. Doing .MoveFirst / .MoveLast on an empty recordset is not good.

---------------------------------------
DataEnvironment1.Command2

Randomize

If Form5.txtAantalvragen.Text > rstVragen.RecordCount Then
    iNo = rstVragen.RecordCount
Else
    iNo = Form5.txtAantalvragen.Text
End If

ReDim x(iNo)

rstVragen.MoveLast
rstVragen.MoveFirst

---------------------------------------
0
 
LVL 28

Accepted Solution

by:
AzraSound earned 1200 total points
ID: 2716929
HOWTO: Update More Than 40 fields in an Access (Jet) Database

http://support.microsoft.com/support/kb/articles/q192/7/16.asp?FR=0
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 2717150
Do rstVragen and rstRandom have identical file structures?  If not, is it possible that two or more records, inserted at random into rstRandom, are effectively identical?

Try this:  Change the structure of rstRandom to include an extra integer, and put the value from irec in it while you are loading the DB.  This will gauarntee that each record is unique (since you are filtering out duplicates).
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 2718996
Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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
Course of the Month12 days, 5 hours left to enroll

752 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