Access macro - failed to go to the last record after new month is added

[Apple08]

Hi All

I have the macro below for Access however after a new month is added, the screen just go back to the first month record instead of staying in the last record. I have used DoCmd.GoToRecord , , acLast but it still doesn't work.

Please can anybody tell me what have I done wrong? Many thanks.





Private Sub nextMonth_Click()
Dim newRecordResponse As Integer
Dim recordNumber As Long
If DCount("[monthID]", Screen.ActiveForm.RecordSource) = Screen.ActiveForm.CurrentRecord Then
newRecordResponse = MsgBox("This is the last record. " & _
"Do you want to create a new reporting month for " & _
vbCrLf & DateSerial(Year(Screen.ActiveForm.monthID), Month(Screen.ActiveForm.monthID) + 2, 0) & _
"?", vbYesNo)
If newRecordResponse = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "q_appendStrategicProjects"
DoCmd.OpenQuery "q_appendStrategicProjectFinance"
DoCmd.SetWarnings False
Screen.ActiveForm.Requery
DoCmd.GoToRecord , , acLast

Else
End If
Else
DoCmd.GoToRecord , , acNext
End If
End Sub

 

[mole999]

acfirst ?

 

[Apple08]

Thanks, unfortunately it stills go to the first month record after a new month record is added by using acfirst.

 

[mole999]

at the end of your sub you then use

DoCmd.GoToRecord , , acNext

was that intentional

 

[Apple08]

Yes, as so users can click the month button to select the next record. I have tried to changed both to acFirst, however it still went back to the first month record after a new month record is added. Also, since the last one is changed to acFirst, the record stays with the first one and not allowing me to select the next.