Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    1

    Question Unanswered: Access 2000 Forms | "Error 6: Overflow"

    Hi Guys

    I have inherited an Access DB in a very poorly state! I have offered to make some changes to it for a local charity but have got stuck on this error it seems to be creating for all new records.

    I am working within a Form which is calling another Form matched on the CarerID, this is within Access 2000. Clicking the cmdLinkExistingCaredFor_Click produces an Error 6: Overflow error.

    Can anyone help me as to what maybe making the code fall down?

    Code:
    Private Sub cmdLinkExistingCaredFor_Click()
    On Error GoTo Err_cmdLinkExistingCaredFor_Click
     
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblPeople " & _
    "WHERE tblPeople.PersonType = 'Cared For' OR tblPeople.PersonType = 'Both' ")
    If rst.RecordCount > 0 Then
    Me.Tag = Me.CurrentRecord
    stLinkCriteria = "[CarerID]= " & Me.PersonID
    stDocName = "frmExistingCaredForByCarer"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.txtForeName & " " & Me.txtFamilyName
    Else
    MsgBox "There are NO 'Cared Fors' in the database."
    End If
     
    Exit_cmdLinkExistingCaredFor_Click:
    Exit Sub
     
    Err_cmdLinkExistingCaredFor_Click:
    Call LogError(Err.Number, Err.Description, Me.Name, "cmdLinkExistingCaredFor_Click")
    Resume Exit_cmdLinkExistingCaredFor_Click
     
    End Sub

    Any help would be much appreciated - was just trying to do someone a favour but got in a little over my head!!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to MS Access topic.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What would happen if you stuck a
    Code:
    rs.MoveFirst
    After the If statement?
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2007
    Posts
    277
    My best guess is this is the line that is "falling down" as you say. The debugger should have highlighted the line that was giving the Run Time error.
    Me.Tag = Me.CurrentRecord
    At any rate, why are you saving the CurrentRecord in the form's tag property? If you want to save it then create a variant variable in which to save it.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you even need to open up a recordset? This...

    stLinkCriteria = "[CarerID]= " & Me!PersonID <- Note: use of ! verses .
    stDocName = "frmExistingCaredForByCarer"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!txtForeName & " " & Me!txtFamilyName <-Note use of ! verses .

    should work and unless you need to "tag" the recordset somehow, I'm wondering if you need to go to the trouble of doing so. If so, instead of using the tag, I'll dimension a public variable and set the ID (i.e. CID as integer) to the current record's ID (autonumber) field.
    Last edited by pkstormy; 10-22-07 at 12:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Furthermore you have not closed your recordset object.
    Code:
    Exit_cmdLinkExistingCaredFor_Click:
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
      Exit Sub
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also,
    This...
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM tblPeople " & _
    "WHERE tblPeople.PersonType = 'Cared For' OR tblPeople.PersonType = 'Both' ")
    If rst.RecordCount > 0 Then
    Me.Tag = Me.CurrentRecord

    has no declarations for the rst (ie..). But I may be wrong on this as I mostly use ADO.
    (as ADO)...
    Dim dbs as ADODB.Recordset
    Dim rst as ADODB.Recordset
    or
    Dim dbs as DAO.Recordset
    Dim rst as DAO.Recordset
    ....
    ....
    rst.close <- don't forget to close the recordset when you're done with it!
    set rst = nothing
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2007
    Posts
    277
    Feel free to just answer one question at a time Angie! We can take it as slow and easy as you want.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •