Clear button not working. What's wrong?

[MustbeAweful]

Good day,

I come to you all as an almost complete n00b to Microsoft Access out of an inability to figure out what the problem is.

You see, I've made a clear button - as you do - and it keeps giving me a "Run-time error '3420': Object invalid or no longer set." I don't know why this is happening, as the following code is the exact same as in a more complicated database that I created:

PPrivate Sub ClearSearchBtn_Click()

FirstNameSearch = ""    
LastNameSearch = ""   
SuSearch = ""    
MoSearch = ""    
TuSearch = ""    
WeSearch = ""    
ThSearch = ""    
FrSearch = ""    
SaSearch = ""    
EmergencySearch = ""    
NeedHelperSearch = ""    
BackupSearch = ""    
Me.Requery    

End Sub 

The result of this is that the fields are cleared, but all of my continuous forms are "#NAME", as if I deleted the whole report.

Interesting side note: I'm also struggling with my search. I have a search button that works, but when I try searching by hitting ENTER (because the search button is default) the field clears, and if I try searching something else and hitting the clear button, the previous search will resurrect like a phoenix from the ashes. I don't know why.



What's going on?

Any help would be greatly appreciated

[June7]

I would set controls to Null instead of empty string.

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.

Why the extra "P" in "PPrivate"?

[MustbeAweful]

Why the extra "P" in "PPrivate"?

I must have done something funny when copying and pasting into my post. This error is not in my database.

Unfortunately, when I try to send it via the link you gave me, it tells me that the file is too big (limit = 500kb). Is there something further I can do to give you a better picture?

[MustbeAweful]

Driver Database.zip

Scratch that. I think I figured it out.

[Welshgasman]

Driver Database.zip

Scratch that. I think I figured it out.

Well do let us know what it was. That could help someone else, after all that is what these sites are for.?

[CJ_London]

Think the OP figured out how to zip a file

[Micron]

when I try searching by hitting ENTER (because the search button is default) the field clears, and if I try searching something else and hitting the clear button, the previous search will resurrect like a phoenix from the ashes

Perhaps that is because under Options you have enter key behaviour set to "move to next record", although I thought that would only affect datasheet view (table, query or datasheet form view) but not sure. If that's what's happening, then clearing could be causing you to go back to the previous record. Without seeing how your form works it's impossible to say for sure.

[Micron]

Some observations/suggestions:

No special characters in object names (? or #, etc.) Underscore is ok but I don't use it.
Date fields are better for flagging any kind of obselence (e.g. "Active") because dates tell you when. If date field is null, then the thing is active.

Similar fields should be broken out into related tables (e.g. Phone, email) if you want to mazimize db normalization. Better than repeating fields. Same with availability. What you have may be ok - until you want to summarize data across fields. Then it becomes a problem. It's a bad habit to get into. Better to have tblAvailability with days of the week as records. Then you'd need a junction table because many 'members' can be available over the many possible days. That's known as a many-to-many relationship.

Given how far you've gotten I doubt you will start over and do this correctly. Regardless, here's what you should read before next time:

https://www.accessforums.net/showthr...773#post521773

Will see if I can figure out your immediate issues with what you posted.

[Welshgasman]

Well it is on all your multiple Me.Requery, but I cannot fathom why at the moment.

That happens if I add it to the form load

Private Sub Form_Load()
Me.Requery
End Sub

so beyond my limited experience.

[Micron]

The code isn't compiled, so what you're reading really isn't what the compiled code is. Try to compile it and you'll see there are a few problems that need to be fixed. When I commented out the problem lines the error was resolved. However, it came back. Not sure when - perhaps after I raised a run time error because there is an IsNull test on ID whjen you click the Open button. ID doesn't exist on the form. If that is the reason, the error may be causing Access to dump the form reference from memory. Will alter code just so I can compile and try to raise the error again without using Open button first.

[Welshgasman]

I walked the code and on Me.Requery before it executed, I could get Me.Name, Me.DataEntry and could see it in Locals.
I would have expected it to be compiled before being uploaded.

I got rid of the extra Subs and it compiles, but still errors on Me.Requery.

[Micron]

I could get Me.Name,

EDIT - Forgot to say "me too".

Well, whatever I thought the problem might be, apparently it's not. Still happens, but if you choose debug and then hit the editor menubar continue button, it finishes running.

[Micron]

Obviously I need more coffee. While I don't understand how the connection to Me is lost, I'm saying it happens because you can't set a checkbox to "".
I forgot that June7 commented on that, but I wasn't focused on the control types so I overlooked the fact that they're checkboxes at first.
Going for more now...

OP has to fix the compile errors (or better yet, read all the links I posted a link to and start over!).
FWIW, I thought it might be caused by not referencing Me in the code because I would not do it as written. However, With Me and using "" still raised the error. So in the end, I have this

With Me
    .FirstNameSearch = Null
    .LastNameSearch = Null
    .SuSearch = Null
    .MoSearch = Null
    .TuSearch = Null
    .WeSearch = Null
    .ThSearch = Null
    .FrSearch = Null
    .SaSearch = Null
    .EmergencySearch = Null
    .NeedHelperSearch = Null
    .BackupSearch = Null
    .Requery
End With

which may turn out to be a factor, but I don't plan to test it.

[davegri]

Reworked the search functions. Removed a couple of dozen 'Requery' statements as unnecessary and problematic.

Driver Database-davegri-v02.zip

Added a combo box to allow search on Names. The combo box will show only the names allowed by the day search selection results.
Created 2 search buttons to either search the days as AND or OR.
It's unclear how the Emergency, Backup and Helper choices would fit into the searches in a business logic sense.




Edit: Apparently the first DB upload I made was corrupt. Replaced zip with V02.

[Welshgasman]

Dave,
Unnecessary requiries perhaps, but it was looking at the form controls, but why was Me.requery failing, when it was still shown in Locals?