Updating table record from within filtered subform

[abcrf]

Subform "SubPT" is bound to table TBL_TRAINING_PROTOCOL. The main form that contains it is unbound, and contains 3 unbound combo boxes: "ComboStudy", "ComboNumber", and "ComboVersion". Each of these sets the value of a locked text box in the subform, as you can see in the after_update code below. These text boxes are bound to the appropriate columns in TBL_TRAINING_PROTOCOL.

SubPT is filtered per the following code:

Private Sub Form_Load()
If IsNull(Me.ComboVersion) Then
   Me.SubPT.Form.Filter = "[PROTOCOL TRAINING ID] = 0"
   Me.SubPT.Form.FilterOn = True
   End If
End Sub
 
Private Sub ComboVersion_AfterUpdate()
Forms!FRM_TRAINING_PROTOCOL!SubPT.Form!STUDY = Me.ComboStudy.Value
Forms!FRM_TRAINING_PROTOCOL!SubPT.Form![PROTOCOL NUMBER] = Me.ComboNumber.Value
Forms!FRM_TRAINING_PROTOCOL!SubPT.Form![PROTOCOL VERSION] = Me.ComboVersion.Value
Me.SubPT.Form.Filter = "[PROTOCOL VERSION] = """ & Me.ComboVersion.Value & """"
SubPT.Requery
End Sub

Everything almost works out fine with the filtering: the correct records display, as does the starred new record row; but an additional, blank record also displays, and TBL_TRAINING_PROTOCOL has a new row with the 3 columns filled in per the text boxes.

My question is this: how do I tweak this so that only the currently extant records fitting the filter display, while also allowing me to add a new record which will include the values from the textboxes?

Thanks!

[zmbd]

When you first open the form, no entries etc... does the form display correctly, that is, only the filtered records and the new-record row?

[abcrf]

Well, per the Form_Load code above, I have it set to initially only display records with a key value of 0, i.e. no records. It shows a single blank record, but doesn't make any sort of entry in the table.

[zmbd]

In and of itself that is useful information.

For troubleshooting, would you mind setting the key value to 1 or something other current value in your recordset( :) ) just to see what is going on...

As for the data entry, I'm not expecting one to be made from the code you've provided thus-far. In the after_update event, I am actually not expecting a new record from the code in post one, there's nothing there to create a new record, the current record might be effected; however, this really depends on the form design and any other code you might have in the parent/child.

[abcrf]

Setting it to filter by key value = 20 instead of = 0 by default causes it to display entry 20, which can be modified (changes reflected in source table), and a starred new entry slot below it.

[zmbd]

Just noticed, in the OP, On_Load line 3 [PROTOCOL TRAINING ID] vs after_update line 12 [PROTOCOL VERSION], did you intend to change the referenced field for the filter?

[abcrf]

Yeah. In general, I want to filter based on where the protocol version = the value they just set in ComboVersion. But when there's nothing in ComboVersion, I'm filtering based on the training id field (autonumber key) = 0, which I know will always return no results.

[abcrf]

Thanks for engaging me on this, Z. I managed to figure out how to get the intended result, which was actually pretty simple in the end.

Changed the above after_update code to:

Private Sub ComboVersion_AfterUpdate()
Me.SubPT.Form.Filter = "[PROTOCOL VERSION] = """ & Me.ComboVersion.Value & """"
Forms!FRM_TRAINING_PROTOCOL!SubPT!SubStudy.DefaultValue = Chr(34) & Me.ComboStudy.Value & Chr(34)
Forms!FRM_TRAINING_PROTOCOL!SubPT!SubNumber.DefaultValue = Chr(34) & Me.ComboNumber.Value & Chr(34)
Forms!FRM_TRAINING_PROTOCOL!SubPT!SubVersion.DefaultValue = Chr(34) & Me.ComboVersion.Value & Chr(34)
SubPT.Requery
End Sub