Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VERY Stubborn Error (Access2K, Win2KPro)

    I have an Access db with a main form that can display either an Intake subform or a Discharge subform. On the discharge subform I have a Discharge Date field that insists on throwing up the following dialoge TWICE everytime I try to enter data for a new discharge:

    The Value you've entered is not valid for this field.

    I've checked the obvious: is the field actually set to take a date? of course it is. is there some other field in the Discharge form that is, perhaps, trying to assign itself a default value that is not legal? (This error only occurs on assigning a date to a new discharge; existing discharge records can be edited/updated with no problems.) Well, apparently not. I have deleted, stripped down, compacted and repaired this accursed db and the error WILL NOT GO AWAY. I can just instruct the end user to clear the 'error' (this is a single-user, single-machine db) but I am NOT HAPPY with Access's whining.

    Unfortunately, after removing every table, query and piece of code not strictly necessary to demonstrate this brain-damaged app it doesn't zip to less than 100K so I can't just attach the beast to this post. I'd REALLY like to, tho. Any recommends on some way to get the size down some more? the 'normal' db is about 6.7 MB (with a lot of tables, some largish subforms, a mass of queries, several VBA modules and a macro) and the 'problem' db is 656 K without any of that and only shrinks to 127K, which is still too large. <img src=/S/beep.gif border=0 alt=beep width=15 height=15>

    At this point I'd like to at least trap out the error or sendkey it automatically but Access REALLY REALLY wants to let me know it's shorts are in a tangle. a VBA On Error Resume Next does nothing but I may have it in the wrong place... The error arises on entering a new date in the date field for a new discharge, there is NO VBA for this event and if I put On Error Resume Next or On Error [Sendkeys "[ENTER}"] on the OnChange event for the field to dismiss the error warning but nope nope nope <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15> Tried OnExit and also no dice.

    Any ideas? I am, obviously, frustrated at this time. I'd rather, of course, find out what Access is crying about but I'd also be satisfied if it shut up. <img src=/S/hushmouth.gif border=0 alt=hushmouth width=16 height=16>

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    Saving the database in Access 97 format usually reduces the size significantly (Tools | Database Utilities | Convert Database | To Prior Access Database Version...)

    Have you tried the On Error event of the form as a whole? Its format is

    Private Sub Form_Error(DataErr As Integer, Response As Integer)

    DataErr is the error number; you can get the corresponding error message as AccessError(DataErr); Response is a variable that determines how Access handles the error; you can set its value in the event procedure:

    Response = acDataErrDisplay is the default: Access displays the standard error message.
    Response = acDataErrContinue suppresses the default message; it is up to you as programmer to handle the error.

    Do you have an input mask on the date field or on the text box bound to the date field? And/or a validation rule?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    thanks for the tip on converting to Acc97 - I did that and now it is small enough to attach. <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    I'll go ahead and post it and in the meantime look into the issue of handling the error on the form itself. I'd like to know what the problem is, however! Since posting the last time I also went in and deleted every unused field on the source tables and the problem continues to fester, but my attachment will have all the fields originally specified for both tables just to give a better picture of what the overall db is setup to do.

    As to your other questions, I've tried formatting the Date field with or without an input mask - no difference, and I don't like the input mask in this case anyway so don't want to use it. No validation rules in place either. the field in question is a Date field drawn from a query from a table with Date set to ShortDate.

    Here's the db:
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    Using the OnError for the form itself does indeed seem to repress the error - so Access does, in fact, shut up. <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    I wonder, though, what the real problem is? I am not all that enthusiastic about short-circuting errors on an entire form but I also am a bit tired of chasing a ghost.

    Well, some progress at least! <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    I'm afraid that the problem didn't survive the conversion from Access 2000 to Access 97 to Access 2002. If I ignore the error messages about missing Mousewheel.dll, I can enter a discharge date without problems...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    For testing purposes, you can display the value of DataErr and/or AccessError(DataErr), and see if that leads anywhere - you can search this forum, or the Microsoft KnowledgeBase, or the newsgroups, or post them here.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    Well the error is a bit sneaky. Try this: in the Intake Form, click on the red button to delete the entire record. Click on New Record and enter Client Info on the main form and select a residency on the Intake subform. (if you don't do this, the discharge date goes away. that's OK as people must enter Intake Info) Anyhow, you've now got a brand new record and an Entry date. Go to Discharge and try and enter a discharge date. Go ahead!! I dare ya! <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22>

    The error DOESN'T show if you are altering a discharge data on an existing Discharge. It likes to show up if you delete the Discharge and try re-entering, but not always.

    As to the mousewheel stuff, you can delete the OnLoad and OnClose event procedures to make it easier to work with the db.

    I'll work on researching the actual error number in a bit. Thanks for the quick response! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    just dug up the actual error number. Error 13, Type Mismatch. Very informative!! (Sarcasm)

    At least I know that it's probably not going to be fatal to ignore this error. It seems like Access expects me to type in the entire date all at once, which is really silly. Maybe there's some weird little setting in the form/subform arrangement but with the simplest possible settings I can think of I don't notice any glaring problems. But, type mismatch... *I* am not seeing any data loss. Which is the basic goal of using a db in the first place...

    I guess the problem's not really solved but I am not terribly worried at this point in time.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    I'm stumped for the moment. If I enter something in the number of months before entering the discharge date, no problem; if I start by entering something in the discharge date, I get the error messages. I'll look at it again later.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    well, like I said, Access is being stubborn. you might also notice that I have provided the calculate month/day based on the discharge date/entry date but that operation doesn't, I think, have anything to do with it as you only run the VBA code on clicking the button. When I run the Form_Error to get a detail on the error, it gives me Error 13, Type Mismatch and highlights the VBA in the Form_Error sub -- not too informative.

    It really looks like Access is being overzealous: I type '1' in the date field and Access goes 'Hey! that's not a date format!' -- of course, you silly prat. I enter '/' and again Access chirps. I finish the date and Access is now satisfied. It's almost like Access is evaluating a match to date format with OnChange but when I loaded OnChange with a msgbox, I got that with every keystroke, not just two. Weird. Eerie. Bizarre. Also, IMO, nothing to sweat over...

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    OnChange is triggered by every keystroke. It's one of the reasons I rarely use it for that kind of purpose.
    Charlotte

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    FIXED.

    Upon further reflection, I decided to examine any occurance of DischargeDate in the modules. I had written a OnLoad event that attempts to setfocus to DischargeDate and I did it twice. I took one out, deleted the OnError for the subform, and no errors.

    I suppose Access isn't the one that's acting brain damaged <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VERY Stubborn Error (Access2K, Win2KPro)

    Like most software and all computer, Access does what you tell it to, even if that isn't really what you meant. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •