ApplyFilter question

[Stephen D Cook]

I have 2 a show history button and a serial number text box on my Main
form.

I also have a subform to show the history of the serial number.
It has [SerialNo] = [Forms]![frmMain]![txtSN] in the query it is
linked to.
The subform is set up so no updates or additions may be entered in it.

There are multiple records in the table for each serial number.

In the code for the history button, I have:
DoCmd.ApplyFilter , "[SerialNumber] = Forms!frmMain!txtSN"

The first time I enter a serial number, the subform fills in
correctly.

The second time I enter the same serial number, I get
ApplyFilter action is cancelled.

I am looking for a way to programatically turn off the ApplyFilter
after showing the data in the subform.

I find that my Main form automatically inserts my filter into it's
properties after I run the form.

There are other text boxes and a command button for adding new records
in the Main form, but the ApplyFilter seems to take over even the Add
new record button, turning it into an insert button at first instance
of the records shown in history.

I have tried using:
If Me.FilterOn = True Then
Me.FilterOn = False
End If
..... in the form coding, but still have the ApplyFilter taking over.

 

[tina]

if the subform's underlying query already has criteria set as

[SerialNo] = [Forms]![frmMain]![txtSN]

then there is no need to apply the same criteria again via a form filter.

simply set code on the history command button to requery the subform, as

Me!SubformControlName.Form.Requery

better yet, if the table underlying the main form has a one-to-many
relationship with the table used in the query that underlies the subform,
then remove the criteria from the query and just set the subform control's
LinkChildFields and LinkMasterFields to the foreign key field / primary key
field combination that links the two tables. the parent (main form) and
child (subform) records will be synchronized automatically without any code
required.

hth