Run-time Error 2001

[Sharon Hague]

Hi All

Can anybody help with the following: -

I have created a subform from a query. In the subform design view I have created an option group for me to select an option for the form to filter. In my option group properties I have entered the following code in the afterupdate option: -

Private Sub FilterOptions_AfterUpdate()

If FilterOptions = 2 Then
Me.Filter = "SalaryMth='1'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

However when I run this and click on the option an error appears as "Run-tim error 2001" You cancelled the previous operation, resulting in the form not filtering the records.

When I press debug it takes me to the code and the Me.filteron=True is highlighted.

I can't uderstand what could be wrong as I have used a similar option group in other subforms where it works.

Can anybody help?

 

[Sharon Hague]

Hi All

I am still struggling with the above - can anybody help.

Could this be because I have already created an option group with filter options in the same form, however this is on a seperate tab control.

Please, please advise.

 

[namliam]

Try adding Me.undo to your code before the "Me.filteron" line

Regards

 

[Sharon Hague]

Namliam

Good to hear from you and thanks for your reply, however its still no good.

It still displays the same error message.

I didn't mention before but I can't even get this to work in my subform itself as well as my main form. That's why I can't think of it being because I have other filters in other tabs on my main form. I can't think that that could be what is causing it.

Any other thoughts?

 

[namliam]

It seems strange, Me.undo should undo any changes on the form...

*shaking my head* I am at a loss at this moment

Sorry

 

[Sharon Hague]

Namliam

I'm confused also.

Would it help if I sent you an small example?

 

[Sharon Hague]

Namliam

I have attached a quick example of my probs.

I'd appreciate it if you or anybody else could have a quick look and advise your thoughts.

Cheers.

 

[namliam]

As expected the problem was 'simple' and the error was fage...

The problem is SalaryMth is a number value not a string. Thus changing Me.Filter = "SalaryMth='1'" to Me.Filter = "SalaryMth=1" got it working.

I also changed it a bit to be more efficient have a look at what i did in attached db.

Regards

 

[Sharon Hague]

Namliam

Thanks for looking at this.

It never occured to me that this was not a string and to try and take the ' ' off.

I am quite new to VBA so am only just getting to know bits and bobs.

I have also changed my code to what you have suggested. Again with me being quite new to this I always take the long way around to do things and didn't think of doing this your way. This has saved me a lot of work in the coding for my db.

Cheers.

 

[namliam]

I can come over and "show you the ropes" as they say...

Regards

 

[Sharon Hague]

Namliam

Bit of a long trip for you! just to 'show me the ropes'. Are you a tutor for Access then? I am picking more bits up from this site that any courses I've been on.

Hey! Just noticed we are the same age!

Anyway thanks for your help.

Cheers.

 

[namliam]

I am NO tutor! *ARGH*

I am a Programmer/analyst one of my specialties beeing Office automation and DB design. I am capable of coaching people (done that in the past), but dont call me a tutor *ARGH*

Funny us beeing the same age... sweet 13

In love/Engaged/married/kidds?

Anyway England is not that far away, i know places in the netherlands that will take me longer to drive to (asspecially in rush hour) than it would take me to drive to the airport and fly to england.... (a bit more expensive maybe... but far away?) I have gone as far as (over) 2,5 hours driving to work (and thats not during rush) but then offcourse staying overnight in a hotel or something. So if you have an extra bed

Regards

 

[Sharon Hague]

Sorry! - If you don't ask how do you get to know? ha!

Now it is getting a bit personal! ha! - only joking.

Unfortunately I'm happily married, no kids, 5 horses and 1 dog instead so no time for kids.

Speak to you soon.

 

[namliam]

Sorry! - If you don't ask how do you get to know? ha!

Now it is getting a bit personal! ha! - only joking.

Unfortunately I'm happily married, no kids, 5 horses and 1 dog instead so no time for kids.

Speak to you soon.

Why unfortunatly? LOL your not thinking what i think your thinking?
I am glad your happy! I am happlily living together.. No kids No dog No horses... Was just wondering what someone my age was up to in the UK...

I can see where you have no time for kids, however most kids love dogs and horses. So that might be where you could get your time... (but that only starts at over 8 yrs (i think))

Regards

 

[Sharon Hague]

I didn't mean unfortunately as it sounds.

It's just a figure of speach.

 

[namliam]

Good.... I was allmost scared you thought i was trying to start some online thing with you or something

"Live long and prosper"'

Regards

 

[Sharon Hague]

Namliam

I have just had another thought.

I want to add another filter to my subform to choose the Years.

Therefore I have created another option group called Filter Option Year.

In here, at present, I have All Years, 2002, 2003, 2004, 2005.

I want to be able to first choose what year I want to filter and then from the next option group choose the salary month.

Is this possible? If so what is the best way to do this? - Would you have 2 seperate option groups to perform this or could you do this in the same one you helped with above?

I'd appreciate your help if you can.

Cheers.

 

[namliam]

Hiya sharon,

Well try searching for Cascading comboboxes. See if that gets you anywhere.

If you dont want that but simply add a thing to select Year there are two options:
1) Options are limited (this year, last year, Year before that)
(radio button is an option)
2) Options are unlimmited depending on the dates available in the DB. (must use (multi select) list box or combo box)

Hope that helps you along

Regards

The Mailman