Update Subform with Append Query Results and Capture ID from Main Form

[cbibles]

I have a main form with 2 subforms. The two subforms are linked with Master and Child fields on ID. For a new record, I fill out details in the main form, then go to subform1 and select an entry from a combo box. On the "After Update" of the combo I'm running an Append query to capture records related to the selection from the combo box. I am appending the results to a table so users can select which records they want to use for an email merge using a checkbox. I'm having two problems, one - I can't get the append query to capture the ID from the main form and attach it to the records that I'm appending to the table. This problem leads to the second problem that I can't get subform2 to update and show the results. Any suggestions or help is much appreciated!!!

[June7]

Is this a split design? Are there multiple users? Is the 'temp' table in BE or FE?

Show code, sql statement, attach project for analysis.

[cbibles]

No this is not a split design. Will only have one user at a time. Right now everything is in one db not split.

The main form simply contains fields for ID, FileAttachment, SubjectLine, and body of email message. Subform1 contains ID and an option group for selecting House, Senate, USHouse, USSenate. User chooses an option from option group and combo box will show House, Senate, USHouse, or USSenate representatives. The user then chooses a representative from the combo box and the after update happens. The query - qryHouseFilter - selects the association members that are in the selected representatives district and appends the records to a table. The table the records are appended to also contains a check box that is set to "On" and it has an ID field for storing the ID number that would store the ID from the main form (also the child field link), and Reply-yes/no field for recording if the association member called their Rep. I want to display the association members in the selected representative's district so the user can remove the check mark from any members they do not want to send the email to. Everything is working except I can't figure out how to get the Main form ID and capture and record it with the results of the query.

I have removed the code I've tried so far that didn't work. The code in the "after update" of the combo box (below) simply runs the append query. The append query retrieves the correct records for members that are in the house representative's district but without the ID from the main form. I'm trying to figure out how I can capture the ID from the main form and add it to the query before the records are appended, or during the process somehow so I can display the records in subform2. I've used an INSERT INTO sql statement and now just the query (set up as an append query) to retrieve the member records. Just can't figure out how to capture the main form ID so the member records stored in the temp table are associated with the email being written in the main form. The varID variable is actually capturing the ID but even with an INSERT INTO sql statement I couldn't figure out how to add the ID number to the results of the query.

The code in the "after update" property for the combo box is:

Private Sub cboLegisLookup_AfterUpdate()

On Error GoTo cboLegisLookup_AfterUpdate_Err

DoCmd.SearchForRecord , "", acFirst, "[LegislatorID] = " & Str(Nz(Screen.ActiveControl, 0))
Me.frmLegislatorDetailsForEmailSubform.Requery
Dim varID
varID = "SELECT Max([tblEmailCampaignDetails]![ID]) FROM tblEmailCampaignDetails;"
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryHouseFilter"
DoCmd.SetWarnings True


cboLegisLookup_AfterUpdate_Exit:
Exit Sub

cboLegisLookup_AfterUpdate_Err:
MsgBox Error$
Resume cboLegisLookup_AfterUpdate_Exit

End Sub

[June7]

I really recommend some procedure that does not require copying records, such as setting filter criteria fields in table. I presume the table copying to is a 'temp' table - must be purged of records for each execution of the process?

I don't understand why the SearchForRecord nor why SELECT Max.

You don't show the INSERT action query statement. Grabbing the ID for inclusion in the sql should be simple parameter reference to form field/control.