Subforms: adding new records: setting one specific field

[stealthshady]

Thanks in advance for everyone's help. Here's my issue:

I have subforms throughout my main form. Everything works fine and dandy. When I add a new record to the subform results, I would like to automatically set a field. For instance: My subform lists functions that belong to a department. I would like to be able to add functions directly in the subform and automatically set the "department ID" field, to match a dropdown that is on the main form... I'd love to do this without even pulling in the "Department ID" field into the subform results...

Any idea how to do this (without having a button on the main form to create the new record in the table with the specified field, then refreshing the subform query)?

Right now I'm working around it by having the field within the subform, manually selecting the Department ID to ensure it's correctly linked.

[yarbrough40]

ok so are you saying that you are making a dropdown box selection in the main form and wanting that selection to populate into a control in a subform when you add a record to that subform rather than updating a query and refreshing? - you want to update the form control directly... is this right?

[missinglinq]

Since this is a one-to-many relationship, department - t o - functions, you should be doing this as a standard main form to subform. The main form should hold information about each department, including its ID, and the subform the same ID number as well as its functions. To enter a new record in the subform, you first move to the record for the department in the main form, then add your record in the subform. If the form-subform is linked correctly, the ID will automatically be entered in the subform (functions) record.

Welcome to Bytes!

Linq ;0)>
.

[stealthshady]

Thanks yarbrough. You're basically correct. on the main form, I use the dropdown (combobox) to select the department I'm currently working on.

That updates the query for my subform, pulling all functions that match the deptartment ID. Within the subform query, I'm pulling in departmentID so when I add a new record, I can select it from the dropdown. My goal is to skip this last step so that when I add a new record, my code will automatically updates the DepartmentID for that record within the subform to match the current department (selected from the main form's dropdown)..

Sorry if that's confusing.. thanks everyone!


@yarbrough40

[yarbrough40]

Looks to me that this is all you're trying to do:

Private Sub TextBox1_Change()
Me.TextBox2.Value = Forms!Mainform1!ComboBox1.Value
End Sub
 
Private Sub TextBox2_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub

[stealthshady]

Yeah I don't think my question came across just right.. I found a workaround, last night.. My main form: a combobox that has a list of departments. My subform: a subform query that pulls a list of functions that have a DepartmentID that matches the current selection of the combobox on my mainform. When someone adds a new record to the subform query results, 'on exit'ing that subform I query that table for any records with a blank DepartmentID, then set it to match the current selection of the combobox (on my main form)..

This seems to work especially since someone can input multiple records before exiting the subform table/query..

Thanks anyway for the assistance. Let me know if there's a more direct way.. Thank you.

@yarbrough40