Place values in new record on subform in continuous form view

[fire2ice]

How would I go about transferring the values contained in one form to a subform where the values are placed in a new record? The below code is called from a command button and places the values in the subform. However, it places them in the record at which the cursor is in.

I imagine I would need to set focus to the new record, but am unsure of the code. It seems that using the recordset property might be a solution, but I'm not clear on how to make that work. Going to the end of record would just get me to the last record, not where the new record is.

Thanks for your help!!!

----------------------------------------------------------------------
Private Sub cmdCategorySelectAtoL_Click()

On Error Resume Next

DoCmd.GoToRecord acDataForm, "Forms!frmCaseDetails!sfrmCategories", acNewRec

Forms!frmCaseDetails!sfrmCategories.Form.cboCategory.Value = Category
Forms!frmCaseDetails!sfrmCategories.Form.cboSubcategory.Value = Subcategory
Forms!frmCaseDetails!sfrmCategories.Form.cboSubSubcategory.Value = SubSubcategory

DoCmd.Close acForm, "frmCategoriesDetailedList", acSaveNo

Forms!frmCaseDetails!sfrmCategories.Form.Requery

End Sub

 

[MarkK]

Or just a write a new record for the data in the appropriate table, and then requery the subform. Poof, the data you added to the table will appear.

 

[fire2ice]

Is there a way to write the new record to the table without running an append query or is that the best approach? If I'm placing the data directly in the table, I will also need to capture the ID from the main form so that I can maintain the relationship between the two tables.

Thanks.

Or just a write a new record for the data in the appropriate table, and then requery the subform. Poof, the data you added to the table will appear.

 

[fire2ice]

Thanks for the help. Solution posted below.

This is how I set up the code to pass the values to the table. Thanks for the suggestion.

-----------------------------------------------------------------------

'Append the data from the subform to the table in order to add the selection to the record
DoCmd.SetWarnings False 'Turns off warning of appending record to table
DoCmd.RunSQL "Insert Into tblCategories (Category, Subcategory, Subsubcategory, CaseID) " & _
"Values ('" & Me.Category & "', '" & Me.Subcategory & "', '" & Me.SubSubcategory & "','" & Forms!frmCaseDetails.ID & "')"
DoCmd.SetWarnings True

'Close the form
DoCmd.Close acForm, "frmCategoriesDetailedList", acSaveNo

'Refresh the subform
Forms!frmCaseDetails!sfrmCategories.Form.Requery