Forum Discussion

Bill_Sterner's avatar
Bill_Sterner
Copper Contributor
Jun 21, 2023

Using gotoRecord,,acFirst and gotoRecord,,acnext

I am trying to run a "batch" type function by using "gotoRecord,,acFirst," and later after some other processing using "gotoRecord,,acNext" to get to the next record in the table, but the logic does not return the next record.  Help!

access
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Jun 21, 2023
    Show us the entire procedure so we have a way to understand what is actually happening.
  • Bill_Sterner's avatar
    Bill_Sterner
    Copper Contributor
    Jun 21, 2023

    Bill_Sterner 

    Option Compare Database

    Private Sub Form_Load()
    Dim Hours, Minutes, Seconds As Integer
    Seconds = Second(Time)
    Minutes = Minute(Time)
    Hours = Hour(Time)
    Randomize (Hours * Minutes * Seconds)
    stDocName = "app_Spotting_Rpt"
    DoCmd.GoToRecord , , acFirst
    Form_Loop:
    ' Test for end of file
    If Left(SH_RecordKey, 10) = "999-XX-XXX" Then
    GoTo Form_Exit
    End If
    Call rtn_Visual_Ranges
    Form_Battery_1:
    Call rtn_Gun_Battery_1
    If Target_11 > 0 Then
    Ship_Number = Target_11
    Call rtn_Update_Spotting
    End If
    If Target_12 > 0 Then
    Ship_Number = Target_12
    Call rtn_Update_Spotting
    End If
    If Target_13 > 0 Then
    Ship_Number = Target_13
    Call rtn_Update_Spotting
    End If
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
    Form_Next:
    DoCmd.GoToRecord , , acNext
    GoTo Form_Loop
    Form_Exit:
    DoCmd.Close
    End Sub

    Private Sub rtn_Gun_Battery_1()
    GB_RecordKey = SH_RecordKey & "-G1"
    Me.Refresh
    Bty_1 = "G1"
    Range_1 = [sub_Gun_Targets]![GB_Max_Range]
    Target_11 = [sub_Gun_Targets]![GB_Target_1]
    Target_12 = [sub_Gun_Targets]![GB_Target_2]
    Target_13 = [sub_Gun_Targets]![GB_Target_3]
    End Sub

    Private Sub rtn_Update_Spotting()
    Dim X_Diff, Y_Diff, Shp_X_Loc, Shp_Y_Loc, Tgt_X_Loc, Tgt_Y_Loc As Long
    Dim Quadrant As Byte
    Shp_X_Loc = SH_Curr_X_Loc
    Shp_Y_Loc = SH_Curr_Y_Loc
    Me.Refresh
    Tgt_X_Loc = [sub_Target_Info]![SH_Curr_X_Loc]
    Tgt_Y_Loc = [sub_Target_Info]![SH_Curr_Y_Loc]
    X_Diff = Shp_X_Loc - Tgt_X_Loc
    Y_Diff = Shp_Y_Loc - Tgt_Y_Loc
    SR_Range = Int(Sqr(Abs(X_Diff) + Abs(Y_Diff)))
    End Sub

    Private Sub rtn_Visual_Ranges()
    DX_RecordKey = [sub_Title]![SC_TimeofDay] & Left([sub_Title]![SC_Visibility], 1) & SH_Ship_Size
    Me.Refresh
    DX_Size_Neg2 = [sub_DX_Chart]![DX_Size_Neg2]
    DX_Size_Neg1 = [sub_DX_Chart]![DX_Size_Neg1]
    DX_Size_Zero = [sub_DX_Chart]![DX_Size_Zero]
    DX_Size_One = [sub_DX_Chart]![DX_Size_One]
    DX_Size_Two = [sub_DX_Chart]![DX_Size_Two]
    DX_Size_Three = [sub_DX_Chart]![DX_Size_Three]
    DX_Size_Four = [sub_DX_Chart]![DX_Size_Four]
    Maximum_Exit:
    Exit Sub
    End Sub

    Private Sub rtn_ErrorMsg()
    DoCmd.Beep
    ErrorSW = "Y"
    DoCmd.Beep
    End Sub

    Private Sub ErrorMsg_Click()
    ErrorSW = "N"
    ErrorMsg = ""
    End Sub

    Private Sub cmd_Exit_Click()
    DoCmd.Close
    End Sub

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Jun 21, 2023
      Thank you.

      That's unlike any VBA I'm familiar with, primarily because of the GoTo: labels instead of more commonly seen Recordset looping. It's an older programming method that has lost favor, although I suspect it probably does work.

      The problem might be that in your code, you call several other routines, such as "rtn_Gun_Battery_1" which issue Me.Refresh commands. It is possible, I think, that this is causing the form to lose it's place in the current recordset and return to the first record every time. Without data to test against, of course, I can't be certain, but that's what I would try to eliminate as the first trouble-shooting step.
      Others may spot additional potential problems.
      • Bill_Sterner's avatar
        Bill_Sterner
        Copper Contributor
        Jun 22, 2023
        I have stripped down the module and I still only get the first record in the table. I have included the current version below. The ""tbl_Ships" only has four records with the SH_RecordKey field being four unique ship names.


        Option Compare Database

        Private Sub Form_Load()
        Dim SK5_Dbase As Database
        Dim SK5_RecSet As Recordset
        Dim RecKey As String
        Set SK5_Dbase = CurrentDb
        Set SK5_RecSet = SK5_Dbase.OpenRecordset("tbl_Ships")
        Do Until SK5_RecSet.EOF
        RecKey = SH_RecordKey
        SK5_RecSet.MoveNext
        Loop
        Form_Exit:
        DoCmd.Close
        End Sub

Resources