Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Post Unanswered: recordsource.move first error

    Hello

    I am trying to upload data into the table using simple access vba code but it doesn't seem to work now

    The same code is working with other access link tables but when i use the other link table ( same concept) it doesnt seem to work

    The code is breaking at rs.movefirst and its showing the message as " no current record" error

    I would appreciate if someone can help me figure out the issue here

    The below is my code

    Function abc()
    Dim rs1 As DAO.Recordset, curLastGc As Currency, rstGroupTotal As DAO.Recordset
    Dim curLastTrans As Currency

    'query to clear the table1
    OpenTrustedQuery "qry_Delete"


    If Form_frmReportMenu.chkExlude0Client = True Then
    'query to append records in the table1
    OpenTrustedQuery "qry_append_Clients"
    Else
    MsgBox " Please check the value to exclude 0 clients and try again"
    Exit Function
    End If


    curGCTotal = DSum("[GC]", "tbl1")

    Set rs1 = CurrentDb.OpenRecordset("tbl1")

    'get client total
    rs1.MoveLast
    lngCounter = rs1.RecordCount
    rs1.MoveFirst

    'get group total

    If Form_frmReportMenu.chkExlude0Clients = True Then
    'query to append in table2
    OpenTrustedQuery "qry_groups"
    Else
    MsgBox " Please check the value to exclude 0 clients and try again"
    Exit Function

    End If

    DoCmd.SetWarnings True
    Set rstGroupTotal = CurrentDb.OpenRecordset("tbl2", dbOpenSnapshot)
    rstGroupTotal.MoveFirst
    rstGroupTotal.MoveLast
    lngTotalGroups = rstGroupTotal.RecordCount
    rstGroupTotal.Close

    iCounter = 0
    Do Until iCounter = lngCounter

    If iCounter = 0 Then
    iCounter = iCounter + 1
    With rs1
    .Edit
    !RecordNum = iCounter
    !CummGC = rs1!GC
    !CummTran = rs1!TranAmt
    .Update
    End With
    rs1.MoveNext
    End If

    If iCounter > 0 Then
    rs1.Move -1
    curLastGC = CCur(rs1!CummgC)
    curLastTranAmt = CCur(rs1!CummTran)
    rs1.MoveNext
    rs1.Edit
    rs1!RecordNum = iCounter + 1
    rs1!CummGC = curLastGC + rs1!GC
    rs1!CummTran = curLastTranAmt + rs1!TranAmt
    rs1.Update
    End If

    iCounter = iCounter + 1
    rs1.MoveNext
    Loop

    rs1.Close


    Call function2

    End Function



    Function function2()
    Dim rs2 As DAO.Recordset



    Set rs2 = CurrentDb.OpenRecordset("tbl3")
    ' when i debug the code breaks below
    rs2.MoveFirst
    iCounter = 0
    'it finds the number record by mult total records x percent at each level, then going and finding the
    'the corresponding totals at that percent and the percent of total at that level.
    Do Until rs2.EOF
    With rs2
    .Edit
    !Clients = CInt(rs2!CummTier * lngCounter)
    !Groups = CInt(rs2!CummTier * lngTotalGroups)
    !CummTran = DLookup("[CummTran]", "tbl1", "[RecordNum] =" & rs2!Clients)
    !CummGC = DLookup("[CummGC]", "tbl1", "[RecordNum] =" & rs2!Clients)
    !CummPt = !CummGDC / curGCTotal
    !CummAverage = Fix((![CummGC] / ![Clients]))
    .Update
    End With
    rs2.MoveNext
    iCounter = iCounter + 1
    Loop

    rs2.Close


    End Function
    Last edited by sam_01; 10-05-12 at 04:13.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to test the EOF and BOF properties of the Recordset before using any of its method (MoveFirst, MoveLast, etc). If both properties are True, it means that the Recordset is empty (no records).
    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Posts
    93

    Red face rst.movefirst error

    I tried checking EOF and BOF but doesn't seem to have any issue there

    its coming out of the recordset and showing as no current record error

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If rs1.BOF = True And rs1.BOF = True then the Recordset is empty,i.e. it has no records, and you receive an error when you try to use the methods rs1.MoveFirst or rs1.MoveLast.
    Have a nice day!

Posting Permissions

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