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
Solved

runtime error

Posted on 2000-04-14
10
1,029 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 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

861 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