Move subform to new record from called form

Question

I am struggling with the problem described here. Using a popup window called from the main form, I want to add a record to a table, update the continuous subform showing the data from that table, and then move the subform to the added record.

However, that solution didn't work, and it throws the same error described here. Below is the code for this setup.

Private Sub Form_Load()
   Set prevForm = Screen.ActiveForm
   
   Me.cbo_EventCat = prevForm!cbo_EventCatSel2
   Me.cbo_EventType = prevForm!cbo_EventTypeSel2
   Me.cbo_SeasonStart = 1
   Me.cbo_SeasonEnd = 1
   Me.cbo_CostType = 1
   Me.txt_MinCost = ""
   Me.txt_MaxCost = ""
   Me.box_NewComments.Visible = False
   Me.txt_NewComments.Visible = False
   Me.box_OriginalComments.Visible = False
   Me.txt_OriginalComments.Visible = False
   Me.cbo_ExistingComments_Sel.Visible = False
   Me.box_NewBuiltComments.Visible = False
   Me.txt_NewBuiltComments.Visible = False
End Sub

Private Sub btn_SubmitAndReturn_Click()
   Dim strSQL, strSQLflds, strSQLvals, formName, strSubform As String
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim recID As Integer
   Dim anyReqFldBlank As Boolean
   
   Set db = CurrentDb
   
   anyReqFldBlank = False
   
   strSQLflds = "INSERT INTO t_EventDetails (fkEventType,fkSeasonStart"
   strSQLvals = "VALUES (" & Me.cbo_EventType & ", " & Me.cbo_SeasonStart
   
   If Me.cbo_SeasonStart = 1 Then
      Me.cbo_SeasonStart.BorderColor = RGB(255, 0, 0)
      anyReqFldBlank = True
   Else
      Me.cbo_SeasonStart.BorderColor = RGB(0, 0, 0)
      anyReqFldBlank = False
   End If
   If Me.cbo_SeasonStart > 2 Then
      If Me.cbo_SeasonEnd = 1 Then
         Me.cbo_SeasonEnd.BorderColor = RGB(255, 0, 0)
         anyReqFldBlank = True
      Else
         Me.cbo_SeasonEnd.BorderColor = RGB(0, 0, 0)
         anyReqFldBlank = False
         strSQLflds = strSQLflds & ",fkSeasonEnd"
         strSQLvals = strSQLvals & ", " & Me.cbo_SeasonEnd
      End If
   End If
   If Me.cbo_CostType = 1 Then
      Me.cbo_CostType.BorderColor = RGB(255, 0, 0)
      anyReqFldBlank = True
   Else
      Me.cbo_CostType.BorderColor = RGB(0, 0, 0)
      anyReqFldBlank = False
      strSQLflds = strSQLflds & ",fkCostType"
      strSQLvals = strSQLvals & ", " & Me.cbo_CostType
      If Me.cbo_CostType = 3 Then
         strSQLflds = strSQLflds & ",MinCost"
         strSQLvals = strSQLvals & ", " & Me.txt_MinCost
      ElseIf Me.cbo_CostType = 5 Then
         strSQLflds = strSQLflds & ",MinCost,MaxCost"
         strSQLvals = strSQLvals & ", " & Me.txt_MinCost & ", " & Me.txt_MaxCost
      Else
      End If
   End If
   If Me.cbo_CommentsSel > 1 Then
      strSQLflds = strSQLflds & ",fkComments"
      strSQLvals = strSQLvals & ", " & Me.cbo_CommentsSel
   End If
   If anyReqFldBlank = True Then
      MsgBox ("Fill in required information.")
      Exit Sub
   Else
      strSQL = strSQL & strSQLflds & ") " & strSQLvals & ");"
      MsgBox ("strSQL = " & strSQL)
      db.Execute strSQL, dbFailOnError
      prevForm!chd_EventDetails2.SetFocus
      prevForm!chd_EventDetails2.Requery
      DoCmd.GoToRecord , , acLast
      DoCmd.Close acForm, "f_AddEventInfo", acSaveNo
   End If
End Sub

I've also tried these techniques, with various problems.

Set rs = db.OpenRecordset("SELECT * FROM t_EventDetails")
rs.MoveLast
DoCmd.GoToRecord , , acGoto, rs!fkID

makes Access throw an error, saying I can't go to that record.

DoCmd.GoToRecord , , acLast

does nothing and throws no errors.

I do not know how to solve this problem.

Gustav Gustav · Accepted Answer · 2020-11-07T21:41:47

Try using the RecordsetClone of the subform:

Dim frm As Form
Dim rst As DAO.Recordset

Set frm = Forms!MainForm!SubformControlName.Form
frm.Requery

Set rst = frm.RecordsetClone
rst.MoveLast
' Sync form to recordset.
frm.Bookmark = rst.Bookmark
rst.Close

Set rst = Nothing
Set frm = Nothing

Move subform to new record from called form

1 Answers