URGENT help needed with VB6 database

BrianWren · April 13th, 2005, 01:55 PM

Rob,

To supplement what Gonzalo just said (and to amplify his earlier post):

If you have set

Code:

    On Error GoTo 0

or you have not run an On Error line in the procedure, an error will terminate the current routine, and go back to the calling routine in an error state.

If you have set

Code:

    On Error Resume Next

if an error transpires your code will continue to run as if nothing has happened, with the exception that the Error object will have its properties set to reflect the error that has happened. This is generally a really bad idea. A lot of people do it, but it is rife with potential bombs.

If you set

Code:

    On Error GoTo <Label_Within_Routine>

and an error transpires, code execution will move immediately to the first executable line following the named label.

Once there, you can re-raise an error, causing program flow to leave the routine, returning to the calling routine in an error state, clear the error to just ignore it (Err.Clear), or Resume in 1 of 3 ways.

Resume Next will clear the error, and send the program flow to the first executable line following the line that caused the error. You can use this to âstep overâ the offending line. Often I will have a line that might raise an error, followed by a line that tests a Boolean that the error handler sets to indicate what the error was:

Code:

    On Error GoTo Er

    Dim a()     As Integer
    Dim NoElems As Boolean ' Initializes to False

    a(0) = 0

    If NoElems = True Then
        NoElems = False
        ReDim a(0)
        a(0) = 0
    End If

    ...

Rs:
    Exit Sub/Function

Er:
    If Err.Number = <Whatever 'subscript out of range is> Then
        NoElems = True
        Resume Next
    End If

    ' Handle other errors here

Resume (with no argument) clears the error, then re-runs the line that caused the error in the 1st place. You can use this for cases where you can correct the cause of the error and try again. One example might be that you tried to .Open a recordset that has not yet been instantiated. So you can add Set RecSet = New ADODB.RecordSet to the error handler, then Resume. Since the recordset now is instantiated, the line should run without error.

Finally, you can Resume <Label>, such as

Code:

    Resume Rs

for my example above.

I usually have Resume Rs as the last line to run in my error handlers, but I follow that with a Resume:

Code:

    ...

    Resume Rs

Resume
End Sub

The last Resume never gets run, because the Resume Rs before it shifts the program flow before the Resume is ever reached.
But when debugging, if I make the code stop in the error handler, I can set the Resume as the next statement to run, then single step, and the execution point will jump to the line that caused the error, allowing me to examine the situation at that point. (Hover over variables to see what their value is, try running statements in the immediate window, etc.)

As Gonzalo has suggested, you can use this to find just exactly which line is raising the error.

Before single-stepping on the Resume, you can type

Code:

?Err.Description

in the immediate window to see what the problem is. (Once you run Resume, the Err object will be cleared.)
Or you can

Code:

    errors:
    Stop
    Debug.Print Err.Description
    Resume

bassace · April 13th, 2005, 02:37 PM

Brian have you thought about writing a book on this stuff?

I have followed Gonzalo's instruction to step into with F8 and the line that is highlighted is the .update instruction in the With rsAchievedTable loop. This is where I thought the problem would be, but what IS the problem?

You know, it's a funny thing. I've been programming in various languages for about 20 years, but databases have always been a sticking point with me!

Thanks again guys.

gbianchi · April 13th, 2005, 02:45 PM

are you sure that table is empty???

could you trace the code from the begining to see if you are not trying to add records that are already there????

HTH

Gonzalo

bassace · April 13th, 2005, 04:27 PM

Right Gonzalo,

I am now totally confused.

I had already got 46 pupils in the database just to test being able to add, edit and delete the personal details in tblPupils. When I was ready to test the code for using with tblAchieved I created the records in tblAchieved for each of the 46 existing pupils mentioned above. I did this using simple For Next loops to fill the 4 fields in tblAchieved for each child with the same content that I want the Add new child routine to enter:

child_ID
steppingstone_ID
Not achieved
None

So for each child there were 383 records as I showed you in an earlier post.

Now, having read your last question - I deleted ALL the records in tblAchieved, but still kept tblPupils with its 46 records. When I add a new pupil, things work, sort of!!

A new pupil is added in tblPupils with Child_ID 47, as you would expect, but the 383 records which have been created in tblAchieved all have Child_ID 1.

I'm going to empty tblPupils and try with a clean sheet, as it were. I'll let you know how I get on.

Rob

bassace · April 13th, 2005, 04:34 PM

OK Gonzalo,

I emptied tblPupils and started again.

The first set of 383 records in tblAchieved is created without any problem, but when I try to add aother record the same error comes back again, so I presume that my code is trying to enter Child_ID 1 each time.

What do you reckon?

Rob

pgtips · April 14th, 2005, 03:25 AM

I think your problem is here (in red):
If (iCurrentState = NOW_ADDING) Then
    With rsChildTable
        .MoveLast
        .Index = "PrimaryKey"
        lNewChild_ID = !Child_ID
    End With
    With rsAchievedTable
        For iCount = 1 To 383
        .AddNew
        !Child_ID = lNewChild_ID
        !SteppingStone_ID = iCount
        !Achieved = cmbAchieved(iCount - 1).Text
        !Evidence = cmbEvidence(iCount - 1).Text
        .Update
        Next iCount
    End With
...

when you change the Index property, the position changes to the the first record in that index, hence you always get the id of child 1.

I'm assuming the db is Access and your ChildID an autonumber field? If so you can get the new autonumber by just reading the ChildID field immediately after the update.

hth
Phil

bassace · April 14th, 2005, 08:01 AM

Thanks Phil,

I'll try that when I get home.

Rob

bassace · April 14th, 2005, 09:56 AM

Phil,

Thank you. Removing that line has done the trick for adding a new record. Do you have any thoughts on the difficulties I'm having with saving changes to existing records?

I'd be very grateful for any other help you might be able to give. It's great knowing that helpful people like yourself, Brian and Gonzalo are just a few clicks of a mouse away.

Rob

gbianchi · April 14th, 2005, 10:01 AM

do you have an error when you try to update an old record or you just cant???

HTH

Gonzalo

pgtips · April 14th, 2005, 10:12 AM

2 things I can think of:
1. are you sure you are getting/using the right child id?
2. in your loop I don't see anything to move the recordset after each update, so maybe you're updating the same record 383 times.
you should be able to check these sorts of things by stepping thrugh the code in the IDE and looking at the database as you go.

It's difficult to see exactly what's going on with your code. maybe you should consider splitting it up into small functions that just handle one mode and operation.

hth
Phil