Forum Discussion
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!
9 Replies
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 SubPrivate 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 SubPrivate 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 SubPrivate 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 SubPrivate Sub rtn_ErrorMsg()
DoCmd.Beep
ErrorSW = "Y"
DoCmd.Beep
End SubPrivate Sub ErrorMsg_Click()
ErrorSW = "N"
ErrorMsg = ""
End SubPrivate Sub cmd_Exit_Click()
DoCmd.Close
End Sub- 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.- 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
- Show us the entire procedure so we have a way to understand what is actually happening.
