The first bit of the code opens the form and pre populates the controls.
Private Sub AddNewComment_Click()
Dim strReference As String
Dim strEmployeeName As String
Dim dtDateTime As Date
' Get the filtered values from the subform
strReference = Me.TblComments_subform.Form.Recordset.Fields("ReferenceAlpha").Value
strEmployeeName = Me.TblComments_subform.Form.Recordset.Fields("Employee Name").Value
dtDateTime = Me.TblComments_subform.Form.Recordset.Fields("Date/Time").Value
' Open the "AddNewComment" form in add mode
DoCmd.OpenForm "AddNewComment", acNormal, , , acFormAdd, , strReference
' Set the values in the "AddNewComment" form
Forms("AddNewComment").ReferenceAlphaTxt.DefaultValue = "'" & strReference & "'"
Forms("AddNewComment").EmployeeNameTxt.DefaultValue = "'" & strEmployeeName & "'"
Forms("AddNewComment").DateTimeTxt.DefaultValue = "#" & Format(dtDateTime, "yyyy-mm-dd hh:mm:ss") & "#"
' Requery the controls to display the prepopulated values
Forms("AddNewComment").ReferenceAlphaTxt.Requery
Forms("AddNewComment").EmployeeNameTxt.Requery
Forms("AddNewComment").DateTimeTxt.Requery
End Sub
Then this is my save button
Private Sub SaveRecordBtn_Click()
' Check if all required information is entered
If Not IsNull(Me.ReferenceAlphaTxt.Value) And Not IsNull(Me.EmployeeNameTxt.Value) And Not IsNull(Me.DateTimeTxt.Value) Then
' Add a new record to the TblComments table
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TblComments", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs("ReferenceAlpha").Value = Me.ReferenceAlphaTxt.Value
rs("Employee Name").Value = Me.EmployeeNameTxt.Value
rs("Date/Time").Value = Me.DateTimeTxt.Value
rs.Update
rs.Close
' Close the "AddNewComment" form
DoCmd.Close acForm, Me.Name
' Refresh the subform in the "EditAbsence" form
Forms("EditAbsence").TblComments_subform.Form.Requery
Else
MsgBox "Please enter all required information.", vbInformation + vbOKOnly, "Incomplete Information"
End If
End Sub