FILTER SUBFORM VIA COMMAND BUTTON

  • Thread starter Thread starter Sammie
  • Start date Start date
S

Sammie

I have a form which relates to its subform on the ReceiptID field. I want
to create a command button on the subform which will filter the subform
records to match the ReceiptID of the main form (WTN RECEIPTS EDIT). What's
wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled the previous
operation."

This filter produces the correct result when I filter by form. I save the
filter as a query, but the query is not available the next time I go back to
load a query into the filter. What am I doing wrong?

Thanks.
Sammy
 
The error message indicates that the Filter string is incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " & Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g. does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on, e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so it's not just a
matter of setting the LinkMasterFields/LinkChildFields properties of the
subform control.

Note that Access is going to get thoroughly confused if you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html
 
Thanks Allen! It was a number field, so the first two
options work.

Now when I move to the next record in my form, the subform
is no longer synchronized with the form. It retains the
receiptID value from the last filter. Ideally, I want the
filter to be subformreceiptID = formReceiptID, not
subformReceiptID = 3 (if 3 was the ReceiptID value of the
form when I first performed the filter). Can I structure
the filter differently, or turn the filter off when I move
to a new record in the form?

Thanks for the explanation of saving a filter as a query.

Now that my command button works, there is no need for it
anyway.
-----Original Message-----
The error message indicates that the Filter string is incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " & Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g. does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on, e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so it's not just a
matter of setting the LinkMasterFields/LinkChildFields properties of the
subform control.

Note that Access is going to get thoroughly confused if you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sammie said:
I have a form which relates to its subform on the ReceiptID field. I want
to create a command button on the subform which will filter the subform
records to match the ReceiptID of the main form (WTN RECEIPTS EDIT).
What's wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled the previous
operation."

This filter produces the correct result when I filter by form. I save the
filter as a query, but the query is not available the next time I go back
to load a query into the filter. What am I doing wrong?
Click to expand...


.
Click to expand...
 
The simplest solution is to use the MasterLinkFields property:

1. Open the main form in design view.

2. Right-click the subform control, and choose Properties.

3. Set these properties:
MasterLinkFields ReceiptID
MasterChildFields ReceiptID

That's it: no code needed.

If that is not suitable for some reason, you must use the Current event of
the main form to assign the RecordSource or Filter of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sammie said:
Thanks Allen! It was a number field, so the first two
options work.

Now when I move to the next record in my form, the subform
is no longer synchronized with the form. It retains the
receiptID value from the last filter. Ideally, I want the
filter to be subformreceiptID = formReceiptID, not
subformReceiptID = 3 (if 3 was the ReceiptID value of the
form when I first performed the filter). Can I structure
the filter differently, or turn the filter off when I move
to a new record in the form?

Thanks for the explanation of saving a filter as a query.

Now that my command button works, there is no need for it
anyway.
-----Original Message-----
The error message indicates that the Filter string is incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " & Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g. does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on, e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so it's not just a
matter of setting the LinkMasterFields/LinkChildFields properties of the
subform control.

Note that Access is going to get thoroughly confused if you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html


Sammie said:
I have a form which relates to its subform on the ReceiptID field. I want
to create a command button on the subform which will filter the subform
records to match the ReceiptID of the main form (WTN RECEIPTS EDIT).
What's wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled the previous
operation."

This filter produces the correct result when I filter by form. I save the
filter as a query, but the query is not available the next time I go back
to load a query into the filter. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
I goofed. The subform is not related to the main form on
ReceiptID. It's related on a field called "client". In
this case, do I use the Current event of the main form to
assign the RecordSource or Filter of the subform? If so,
can you elaborate on how I do this? Thanks.
-----Original Message-----
The simplest solution is to use the MasterLinkFields property:

1. Open the main form in design view.

2. Right-click the subform control, and choose Properties.

3. Set these properties:
MasterLinkFields ReceiptID
MasterChildFields ReceiptID

That's it: no code needed.

If that is not suitable for some reason, you must use the Current event of
the main form to assign the RecordSource or Filter of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen! It was a number field, so the first two
options work.

Now when I move to the next record in my form, the subform
is no longer synchronized with the form. It retains the
receiptID value from the last filter. Ideally, I want the
filter to be subformreceiptID = formReceiptID, not
subformReceiptID = 3 (if 3 was the ReceiptID value of the
form when I first performed the filter). Can I structure
the filter differently, or turn the filter off when I move
to a new record in the form?

Thanks for the explanation of saving a filter as a query.

Now that my command button works, there is no need for it
anyway.
-----Original Message-----
The error message indicates that the Filter string is incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " & Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g. does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on, e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so it's not just a
matter of setting the LinkMasterFields/LinkChildFields properties of the
subform control.

Note that Access is going to get thoroughly confused if you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html


I have a form which relates to its subform on the ReceiptID field. I want
to create a command button on the subform which will filter the subform
records to match the ReceiptID of the main form (WTN RECEIPTS EDIT).
What's wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled the previous
operation."

This filter produces the correct result when I filter by form. I save the
filter as a query, but the query is not available the next time I go back
to load a query into the filter. What am I doing wrong?
Click to expand...
Click to expand...


.
Click to expand...
 
Sorry, I started out with a wrong assumption. The subform
is linked to the main form on field "client" not on field
"ReceiptID". In this case, how do I retain the correct
filter from record to record? If I need to use the Current
event of the main form to assign the RecordSource or Filter
of the subform, please elaborate on how I do this. Thanks.
-----Original Message-----
The simplest solution is to use the MasterLinkFields property:

1. Open the main form in design view.

2. Right-click the subform control, and choose Properties.

3. Set these properties:
MasterLinkFields ReceiptID
MasterChildFields ReceiptID

That's it: no code needed.

If that is not suitable for some reason, you must use the Current event of
the main form to assign the RecordSource or Filter of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen! It was a number field, so the first two
options work.

Now when I move to the next record in my form, the subform
is no longer synchronized with the form. It retains the
receiptID value from the last filter. Ideally, I want the
filter to be subformreceiptID = formReceiptID, not
subformReceiptID = 3 (if 3 was the ReceiptID value of the
form when I first performed the filter). Can I structure
the filter differently, or turn the filter off when I move
to a new record in the form?

Thanks for the explanation of saving a filter as a query.

Now that my command button works, there is no need for it
anyway.
-----Original Message-----
The error message indicates that the Filter string is incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " & Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g. does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on, e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so it's not just a
matter of setting the LinkMasterFields/LinkChildFields properties of the
subform control.

Note that Access is going to get thoroughly confused if you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html


I have a form which relates to its subform on the ReceiptID field. I want
to create a command button on the subform which will filter the subform
records to match the ReceiptID of the main form (WTN RECEIPTS EDIT).
What's wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled the previous
operation."

This filter produces the correct result when I filter by form. I save the
filter as a query, but the query is not available the next time I go back
to load a query into the filter. What am I doing wrong?
Click to expand...
Click to expand...


.
Click to expand...
 
Okay: use the Client field in the link fields.

Whatever the field is called on the main form, use that name in
LinkMasterFields. And whatever is the matching field in the subform, use
that name in LinkChildFields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sammie said:
Sorry, I started out with a wrong assumption. The subform
is linked to the main form on field "client" not on field
"ReceiptID". In this case, how do I retain the correct
filter from record to record? If I need to use the Current
event of the main form to assign the RecordSource or Filter
of the subform, please elaborate on how I do this. Thanks.
-----Original Message-----
The simplest solution is to use the MasterLinkFields property:

1. Open the main form in design view.

2. Right-click the subform control, and choose Properties.

3. Set these properties:
MasterLinkFields ReceiptID
MasterChildFields ReceiptID

That's it: no code needed.

If that is not suitable for some reason, you must use the Current event of
the main form to assign the RecordSource or Filter of the subform.


Thanks Allen! It was a number field, so the first two
options work.

Now when I move to the next record in my form, the subform
is no longer synchronized with the form. It retains the
receiptID value from the last filter. Ideally, I want the
filter to be subformreceiptID = formReceiptID, not
subformReceiptID = 3 (if 3 was the ReceiptID value of the
form when I first performed the filter). Can I structure
the filter differently, or turn the filter off when I move
to a new record in the form?

Thanks for the explanation of saving a filter as a query.

Now that my command button works, there is no need for it
anyway.

-----Original Message-----
The error message indicates that the Filter string is
incorrect.

In the subform's table, what kind of field is ReceiptID?
If it's a Number field, drop the extra quotes, i.e.:
Me.Filter = "ReceiptID = " & [Forms]![WTN RECEIPTS
EDIT]![ReceiptID]
or better still:
Me.Filter = "[ReceiptID] = " &
Nz(Me.Parent.[ReceiptID], 0)

If that still fails, double check the field names (e.g.
does it have a
space?)
Use the Immediate window (Ctrl+G) to test what's going on,
e.g.:
? [Forms]![WTN RECEIPTS EDIT]![ReceiptID]


Presumably you need to be able to remove this filter, so
it's not just a
matter of setting the LinkMasterFields/LinkChildFields
properties of the
subform control.

Note that Access is going to get thoroughly confused if
you apply a filter
to the main form and also to the subform. Details in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html


I have a form which relates to its subform on the
ReceiptID field. I want
to create a command button on the subform which will
filter the subform
records to match the ReceiptID of the main form (WTN
RECEIPTS EDIT).
What's wrong with my code?

Me.Filter = "ReceiptID ='" & [Forms]![WTN RECEIPTS
EDIT]![ReceiptID] & "'"
Me.FilterOn = true

I get a runtime error '2001' "You cancelled
the previous
operation."

This filter produces the correct result when I filter by
form. I save the
filter as a query, but the query is not available the
next time I go back
to load a query into the filter. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top