Append to table based on subform, then update subform (1 Viewer)

[JMarie]

I have built an import program. It pulls in all the data from 2 other systems runs consolidations and matches it by each client.
The user needs to view the claims (data) from the imported spreadsheet. If these claims are not in her database, she needs append the data to her claims table.
I made a form. It has a basic search box for her to look up the client. This form has 2 subforms:
1st subform is the data from the imported spreadsheets. It has several fields that the user may need to fill in. This subform includes an ADD button. When the ADD button is clicked the information should be appended to the claims table and the information should now show up on the 2nd subform.

2nd subform is the claims currently in her database.

My forms almost work, but not completely.
1. All info is appended EXCEPT for the new data that the user may add/update.
The ADD button runs a macro that opens the append query. When it runs it only adds the data from the original spreadsheet. It does not add the user input.
(note: IF I open the actual query instead of clicking the add button, it updates all the data correctly.)

2. I cannot get the 1st subform to update to a 'do not show on form' since the data is now transferred. I assume this is because the form is still open when trying to run an update query on it.

3. A requery of the forms don't seem to work. Does this need to be on each subform?

Any ways to work around these issues?
Thanks,

 

[JHB]

..
1. All info is appended EXCEPT for the new data that the user may add/update.
The ADD button runs a macro that opens the append query. When it runs it only adds the data from the original spreadsheet. It does not add the user input.
(note: IF I open the actual query instead of clicking the add button, it updates all the data correctly.)

I don't think the new data is add to the table when you click the Add button
Therefore do a test if the form is Dirty just before you click the button.

If Me.Dirty Then
  Me.Dirty = False
End If