Solved

runtime error

Posted on 2000-04-14
10
1,030 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
Technology Partners: 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 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 300 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:bhess1
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

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.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

749 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