how "call" an "enter" event with a button?

[laghetto]

Hi everybody,
first of all sorry for my bad english!
this is my problem:
I created a subform that contain a textbox (in yellow) which works as a filter for records. It works but i have a problem, if I change record in the form the value in the filterbox doesn't change. so I created a button that update the submask.
On enter pressing event there is a (public) routine that make the filter works (the same on change but i don't know is useful).
I think is not easy make that when the subform is open, or when i go back and forth with form record in the form the box is automatically updated.
So I need to add to the bottom some code that virtually press enter on the filter box.
I tried with a "call" on the event after update but it doesn't work.
(are my events correct?)
this is a similar file there is onlya a form, not subform

best regards

PS: I don't know VBA or SQL

 

[stopher]

Do you mean you want to reset the filter if someone changes a value in a record?

You can call the button with:

Call Comando19_Click


You can use the On Change event for a given field to run code if that field changes.

hth
Chris

 

[laghetto]

hi Chris!,
i'have done a "little bit" cofusion. sorry!!!
is better to solve one problem at time
in the file i have uploaded, the record in the form will not change in this form.
it is used only to search datas.
the button works correctly. It have only to reset the green boxes and reset the filter.
This is my problem:
I write something in the green boxes, then I have to go on the yellow one and press enter.
is it possible to make that when i change somethin in green boxes the filter is updated automatically (as if I press enter on the yellow box)?

thank you in advance!
lg

 

[stopher]

Yes. I normally use the AfterUpdate event for each text box.

But regarding the code for the yellow box, I would be inclined to put all the filter code in a subroutine. The each textbox AfterUpdate event can just call the subroutine and the subroutine does all the filtering as well as updating the yellow box. In other words, your yellow box does not need any events.

By the way, your filtering needs to account for when more than one box is used. It doesn't seem to do this.

hth
Chris

 

[laghetto]

I created the yellow box that make a union of green boxes content because i'cant write VBA. that put in AND the three filters... so is newbbie trick.
I think yellow box works you have only to write in green boxes in a particolar way:
for the first on the left you have to write starting from the left part of record you are searcing (e.g. you cant search "macchina", but "capo" or "capo macchina")
for the second one you have to write the entire string but in the real use that field will be only a single caracter (Qz s only a test)
the las one search starting from the right (e.g. it works if you write 11 or 2011 but doesn't work if you write 20.. it is also useful for fhe 202X years... if i will survive to access!!!!).
Unfortunately I can't understand what you say about after_update event and subroutine...
thank you for your patience

 

[stopher]

Take a look at the attached.

I've used the After Update event instead of the On Change event. The difference is that the On Change event fires every time you type a character. We can switch back to that if you prefer.

See how each After Update event calls the same subroutine that does the filter.

Yes you can leave the calculation in the yellow box as it is.

Chris

 

[laghetto]

I have tried to write that code for a week... peoples on many forums say me "it is so easy! write it by yourself!... or give me the code but once i put it in my file (after change word necessary i had only errors!)
thank you very much!
2 questions:
- applyfilter is not an element on my file (ad textbox...)?is it?
on my file when i click on yellow box it filter this is useful for the "basic filter" wich is with 2011 so I put call applyfilter on the opening event of the form so when open it will show me records for 2011. that is wonderful!
-Is it possible to find a way that make the filter run on changing without waiting for an update (as in your file) or an enter (as in my one?).
I tryed appling your metod using my filter code... but doesn't work. if i use your applyfilter code it search only a caracter.
tankyou again!

lg

PS: i

 

[stopher]

2 questions:
- applyfilter is not an element on my file (ad textbox...)?is it?

No. It's a subroutine I wrote and I decided to call it ApplyFilter. As you suspect, you can call this routine in other places as required.

-Is it possible to find a way that make the filter run on changing without waiting for an update (as in your file) or an enter (as in my one?).

See the attached version. It changes the filter as each character is typed.

The problem with the On Change event is that you need to read the .text attribute of the textbox not the .value attribute. This is because .value doesn't get updated until the After Update event has fired. But that event does fire while you are still entering characters in the box. So if you looked at .value you would see an out of date value.

To overcome this I've added a function (called filtr) which returns either the .text or .value depending on whether the supplied textbox has the focus or not.

Hope that makes sense.

Chris

 

[laghetto]

it work perfectly!
I hope to be able to use your code for all my forms and subform.
the next one have the button that i was trying to put the call into.
but maybe with your filtr instruction i can avoid it.
In this case the problem is that I have a form based on a crossed fields query.
this work perfectly alone. but if i use it as a subform, access tell me that i have to setup culumnsheadins... and I don't understand what i have to do.
So in the form that I want to use as a subform I created:
a box(A) wich bring the corrent ID number on the master form,
a filter box(B) as for my precedent file (my version), with my original code on enter event
and a button that update the filter box(B) and take the box (A) value.
then pressing on B make the the filter run so my form act as a subform and show me the correct record.

later i try to use your fantastic code on this new file... will i be able...
Or is there a way to solve the problem with culumns heading?
(I know I have to update the mask, but culumns headis are "years" so i have to do it once for year... is not a problem!)

for now I really don't know how to repay you!
best regard and 1000 times thanks!

 

[laghetto]

Hi Chris!
I worked on your file... but I had some problems.
is it possible to make that when the mask applyfilter works without i change any value on green boxes? (in my file it will filter by "2011").
(I tryed to make it putting a call apply filter on opening or launging event but it give me error)

in the new file i put a botton on subfield Mask_filtri that i'd like to open itself filtering by ID_master and mansione... i tryed to insert both with an AND but as you know VBA and me aren't really friends.... so it doesn't works.
The second submask is based on a crossedfield query, so i can't use master and secondary field link as usual.
So i put in the submask a field that read the current ID_master. using applyfilter but i can't work on opening... it works only on "change" event.
is it possible to make it works as for the first file (without i have to "change"value or click...)
(To make that query works i have changed the "Qualifica" type field in the table1 from text to number. I have to make a similar query also with a text type, hoping that is it possible)

have a nice day

lg

 

[laghetto]

no way? can i give any other informations?

bye
lg

 

[stopher]

I'm not entirely sure what you are asking for.

In your latest database I see a form with two subforms. The filter obviously works on the first subform. Do you want the same filter applied to the second subform?

Chris

 

[laghetto]

Hi Chris,
as you see when the firts subform is open it show "2011" in the last green box but if i want to "call" applyfilter i have to "change" somethin in green box.
I'd like that when I open the master form it is filtered by the value of that box.
(in the real file I call apply filter by a button "press me!" like alice in wonderland... i hope my boss understand!... is not' so hard, is it?).
on the right of first submask there is a button (for each record) i'd like it open another subform showin only record for "mansione + year" AND ID_master". it could be another way to see records without typing in green boxes.

In the second subform i used a crossed field query... but isn't really a subform because is not possible set master and secondary fields in mask properties, so i use your vba to create a filter by the "fn" texbox. in the real file it works by click on a button again (as for the first subform) and show me records only for the current ID_master.
there are other filter box always with your code. it work but if I try search something that doesn't exist it go on error and all is blocked. so I put in the master mask a button that run a macro that close and open the file... the problem is that it losts the id_master number i'm watching.
I can't upload the file because is a company (work in progress) document, if you are agree i can send you privately.

 

[laghetto]

Hi again...
i'm still blocked on the same problem.
I put the code in my new database and it works but if i write a word on the that not exist in the table it go on error and i have to close and re open my file.
(in your original file it doesn't stop to work but simply do not shows record in that case)

"filtri3_v3" is your file...
"new" is mine...
where is my error? (in my real database i have 4 forms with this code... but only one works correctly. the others have this problem). two of this forms are based on a query

i'm desperate like women in wisteria lane... or more!

thankyou in advance for your help!

 

[laghetto]

partially solved... in the file I uploaded the allow add property of my form was "no", on your is "yes". i changed it and now it seems work, i have to try also on my more complicaded form.
i hope the problem is the same...
good night

update:
one query wich I use behind a form is not editable. I think is for this reason the filter function give problems when i try to filter for a value that is not in the records
is it possible to solve it?

 

[stopher]

one query wich I use behind a form is not editable. I think is for this reason the filter function give problems when i try to filter for a value that is not in the records
is it possible to solve it?

Some queries are not updatable e.g. aggregate queries, certain join queries and crosstab queries. If you want the data to be updatable then you need to use a simply query. The filter should not have any effect on the updatability of the query.

If you post the query we can take a look.

Regards
Chris

 

[laghetto]

Hi!

in this case my problem isn't the not edidability of query but it was that if i search on the form something that doesn't exist in the query it block the form( based on a query of a crosstab/field query. i know it isnt editable and is a good thing)
i solved using an iff count that if not find something blocks the applyfilter sub., then i added a color alert to highlight that nothing has be found, i told to my user that if area under filterboxes become red he is searching for something that doesn't exist. he replied me: "i'm not stupid... i know seems there is a problem on that object!!!"

the last thing i need is find a way to laung che call apply filter when i open the form because i'd like to filter directly by curren year. so i used a preset value in the filterbox ( year(Now()) ), but it doesn't work until i do some changes on filters.
i tryed to call apply filter on form event but it give me errors.

have a nice weekend end thank you for all