Solved

runtime error

Posted on 2000-04-14
10
1,026 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
10 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
emoreau,

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

Using MSjet oledb4.0
with an access databases

0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Oups. Typo error!!!

rstRandom.CursorLocation = adUseClient
 
0
 
LVL 25

Author Comment

by:RonaldBiemans
Comment Utility
Unfortunately that didn't make a difference
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
Try this:

Sub Volgendevraag()
boAntwoordGegeven = False
if not rstrandom.eof then rstRandom.MoveNext
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Author Comment

by:RonaldBiemans
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now