Overflow

[Indigo]

I am running Access 2003 and have created the following:

Sub MSDeficient()
     Dim HoldMSComplID As Integer
     Dim HoldMNumber As Integer
     Dim HoldMName As String
     Dim HoldCourseID As Integer
     Dim HoldDeficient As Integer
     Dim dbObject As DAO.Database
     Dim MSDeficiencyRS As DAO.Recordset
     Dim EmpInfoRS As DAO.Recordset
     Dim strQuery As String
     Dim strquery1 As String
     HoldMSComplID = Forms!frmMSCompl.Controls!TxtIDNumber.Value
     HoldCourseID = Forms!frmMSCompl.Controls!MSCourse.Value
        Set dbObject = CurrentDb
        strquery1 = "SELECT * FROM MSDeficient "
        strQuery = "SELECT * FROM EmpInfo "
 
        Set MSDeficiencyRS = dbObject.OpenRecordset(strquery1)
        Set EmpInfoRS = dbObject.OpenRecordset(strQuery)
        With TMInfoRS
               .MoveFirst
                    Do While Not .EOF
                    If .Fields("MSDeficient").Value = 0 Then
                        .MoveNext
                            Else
                    If .Fields("MSDeficient").Value = -1 Then
                            HoldTMNumber = .Fields("MNumber").Value
                            HoldTMName = .Fields("MName").Value
                            HoldDeficient = .Fields("MSDeficient").Value
                            End If
    If .Fields("MSDeficient").Value = -1 Then
        With MSDeficiencyRS
                    .AddNew
                    .Fields("MSCompID").Value = HoldMSComplID
                    .Fields("MNumber").Value = HoldTMNumber
                    .Fields("MName").Value = HoldTMName
                    .Fields("MSCourseID").Value = HoldCourseID
                    .Fields("MSDeficient").Value = HoldDeficient
                    .Update
        End With
      End If
        End If
            .MoveNext
            Loop
        End With
    EmpInfoRS.Close
End Sub

I modeled if after a successful one I have running in another database but this one gives me an "overflow" error and I can't figure out what that means or why I am getting the error. Can someone help me out?

 

[pbaldy]

First guess is that one of your values is larger than an Integer can handle (32,767). If so, change it to Long.

 

[Indigo]

Ugh, thank you for helping me find the forest in the trees...

 

[Indigo]

Paul, after making the changes, I now get a "no current record" Run-time error '3021' pointing to:

.MoveNext

Can you help me fix this?

 

[pbaldy]

I indent differently than you do, and when I indent it my way the answer becomes more visible (to me anyway):

    Do While Not .EOF
      If .Fields("MSDeficient").Value = 0 Then
        [COLOR="Red"].MoveNext[/COLOR]
      Else
        If .Fields("MSDeficient").Value = -1 Then
          HoldTMNumber = .Fields("MNumber").Value
          HoldTMName = .Fields("MName").Value
          HoldDeficient = .Fields("MSDeficient").Value
        End If
        If .Fields("MSDeficient").Value = -1 Then
          With MSDeficiencyRS
            .AddNew
            .Fields("MSCompID").Value = HoldMSComplID
            .Fields("MNumber").Value = HoldTMNumber
            .Fields("MName").Value = HoldTMName
            .Fields("MSCourseID").Value = HoldCourseID
            .Fields("MSDeficient").Value = HoldDeficient
            .Update
          End With
        End If
      End If
      [COLOR="Red"].MoveNext[/COLOR]
    Loop

If that test is met for the last record, the first .MoveNext will move to EOF, so the second will error.

 

[Indigo]

Paul, bear with me here, I am trying to get the logic clear in my head....

So the code starts to move through 20 records, for example, and the 7th record has a MSDeficient value of -1, so it writes that record to the
table, wouldn't it then have to "move next" to the 8th record and continue
to loop through until the next -1 value, say the 14th record to write that record to the table? (yes, I know my indents are sloppy )

I guess I am not seeing what you are seeing....

 

[pbaldy]

Whenever the value of MSDeficient is 0, the code will move to the line after the test, which is the MoveNext. Then, it will run the next line after the If/Then block, which is another MoveNext. See if this works for you:

    Do While Not .EOF
      If .Fields("MSDeficient").Value <> 0 Then
        If .Fields("MSDeficient").Value = -1 Then
          HoldTMNumber = .Fields("MNumber").Value
          HoldTMName = .Fields("MName").Value
          HoldDeficient = .Fields("MSDeficient").Value
        End If
        If .Fields("MSDeficient").Value = -1 Then
          With MSDeficiencyRS
            .AddNew
            .Fields("MSCompID").Value = HoldMSComplID
            .Fields("MNumber").Value = HoldTMNumber
            .Fields("MName").Value = HoldTMName
            .Fields("MSCourseID").Value = HoldCourseID
            .Fields("MSDeficient").Value = HoldDeficient
            .Update
          End With
        End If
      End If
      .MoveNext
    Loop

 

[Indigo]

Thank you, once again, Paul!

Ah, I see what you are doing...one more question, though: MSDeficient will only ever be 0 or -1 as the control on the form is a check box, so do I still need this line in my code?

If .Fields("TSMSDeficient").Value <> 0 Then

 

[pbaldy]

Good catch! I didn't even look at the next line of code. You are correct, the outer If/Then block is not needed.