Insert detail from one form to newRec on subform.

[Numpty]

Another question from me, this ones got me stumped.

I have a Main form which displays Course details and within this form there is a subform which allows users to book "Delegates" onto the course.
Problem I have is that to choose a Delegate the user selects from a combo box on the subform. Now, potentially there could be 1000's of Delegates so we feel the use of a combo box may not be suitable. I have created separate form used as a search form and what I would like to do is once the correct Delegate has been found on the search form the user selects a button and this will add the Delegate as a new record on the subform - they are then booked on that particular course.

Unfortunatly I'm unable to figure out how to transfer the DelegateID into the new record on the subform.

 

[dcx693]

I assume you already have the subform on a new record and that the search form for the DelegateID is open. It sounds like you can find the Delegate on the search form but you just need to know how to get the value of the ID into the subform.

You can use an expression of this type, substituting the name of your MainForm, subForm and whatever your actual fieldnames are:
Forms!MainFormName.subFormName.Form.DelegateID=Me.DelegateID

Put that code somewhere in your search form (in the click event of a command button perhaps.

 

[Dugantrain]

You can do this with SQL behind the Search form or you can use an Append Query which specifies your forms' objects as criteria. I'll tell you the latter way as it is easier to set up and doesn't require as much ADO/DAO knowledge.

Make a new query. Make it an Append Query. For the field's name, transpose:

Forms!YourSearchForm.form!The_Control_That_Captures_Your_Delegate_ID

Append this to the table and to the field that will store the Delegate_ID (In the Query's "Append To" field). You'll need a second field in this Append query which will append the primary key from the main data entry form to the foreign key of the Delegates Subform so that you'll keep these records relational:

Forms!Your_Data_Entry_form.Form!Your_Primary_Key

Append this to your foreign key field.
Call this query in the After_Update event of the control that captures the Delegate_ID in the search form using

DoCmd.OpenQuery "YourQuery", acnormal

You can use docmd.setwarning false right before this line to turn off the Append warning and then setwarning true after the line to turn warning back on, it's up to you. And the last line of ocde would be:

forms!Your_Data_Entry_Form.Refresh

so that this inserted field will show up in the Data Entry Form.

Oh yeah, in order for this to work, both of your forms must be loaded in memory, else you'll get an error.

 

[Dugantrain]

dcx693,
the only issue with your recommendation is that on a datasheet subform, this will alter the first record Document_ID rather than append a new record. Numpty, if you're subform is in single form view, then do it the way that dcx693 suggested. If it is in Datasheet view, then it gets quite a bit more complex.

 

[Numpty]

Cheers for the replies there dcx and Dugantrain, and thanks for taking time to help out.

dcx's solution works fine, I'm using a continuous subform but it still works well. I've added a line to the button that calls the search form to move to the new record in the subform first, the search form then adds there rather than change an existing entry.

Cheers