Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null Errors (2k)

    I have a form that is linked to a table that has 5 primary keys to create uniqueness. When the form loads up, a few of the primary fields are already known and filled in accordingly. However, 2 of them are not known, so if I open the form and close it without making any entries i get an error saying: "Index or primary key cannot contain a null value" and then a 'can't save record at this time' error. I understand why I'm getting this error, but I don't want to if i haven't made any changes to the record (i know the 3 default values start to create a record). And i can't have default values for these fields.

    So how can i avoid this annoying double error message everytime i open up the wrong record by mistake and just want to close it?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    Does your form always open to a new record?
    Where do the three values come from? Do you have code that enters them into the appropriate fields?

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    The form opens up to whatever record the user is on at the time, meaning no it doesn't always open to a 'new' record, but maybe a blank one. If a record is blank, i wrote a code to fill in the three fields taht are definitely already known. So the three values come from my main table, and are linked to my other form's table to save the person the hassle of entering all the data.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    One workaround would be to populate the three fields in the Before Insert event of the form instead of in the On Load event or whatever you are using now. The Before Insert event occurs the moment the user starts to enter something in a new (blank) record. If the user does NOT enter anything, there will be no error message since the fields will not have been filled.

    Another workaround would be to use a separate unbound form to enter a new record, with an OK button to save the record.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    i like your first work around better Hans, the second one looks like it would be a ton of work for me (my form has a lot of controls). i'll let you know how that turns out.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    I agree that the first one is less work. But there are circumstances in which an unbound form is the best solution, because you (as designer) have complete control over what happens with the data entered by the user.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    well that works how its supposed to, but im not so sure that its how i want it to [img]/forums/images/smilies/tongue.gif[/img]

    i was hoping it would still display the information before anything was entered when the form loaded, but i guess that would require your second work around... these guys aren't so great with computers so im trying to limit the amount of things they have to do (even if its just clicking update or save). i guess i'll stick with the beforeupdate though, its probably not necessary that the information is displayed. thanks for the help Hans.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    You wrote in the first post in this thread that default values for these fields wouldn't do. Why not? The advantage of a default value is that it is displayed in a new record before the user enters anything, yet doesn't count as a change in the record. So if the user moves away from it without entering anything, there will be no error message. You can also default values in code, if desired.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    The reason default values wouldn't do is because the default dates would make poor reports if the user was lazy and didn't change dates or something of that sort.

    FYI: I found an interesting section in the Access 2000: Developer's Handbook (volume 2) that you recommended me. On page 39 there is a code that customizes error dialogs. Since the users wouldn't understand the error that they are getting, I am going to try and re-write the error dialog to something that they would understand that makes sure they entered all the data that was necessary, otherwise delete the record. sound feasible?

    edit: the dialog doesn't have an error code, does that mean its not technically an error? or i just have to search for the error code?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    Sorry I don't understand that. Why would it be acceptable (except for the problem you ran into) to set the value of the three fields, but not to use default values?

    The error you get does have an error code (3058). Why do you think it doesn't?

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    My main table is a database of all the drawings that need to get completed. About 500 of these drawings of 5000 are completed so far. As we progress through the drawings, information on them can change, such as the Drawing Number, Type, and something called a Dash number (denotes its a sub-part of the drawing). These drawings go through a checking process to make sure everything is ok w/ them. That process is what is kept track of on the 2nd form where some of the information is transferred to. So the drawing number, Type, and Title is carried over from the first form to the 2nd to take out the hassle of writing down annoying letter/number combinations and long title information as well as keeping the data the same as the data in the main table. Its repetitive and unnecessary. Then there are two other fields that have to do with the date received (which isn't necessarily the first time they open the record), and another varying number field. so if reports were run and that information was present, we would end up with erroneous data. thats why i'd rather not use default fields, since I want to force the checking people to enter in these dates to better track their progress. i hope that was a better explanation.

    and i didn't know if it had an error code because i'm not that familiar with them. Since it didn't display an error code to me on the dialog I wasn't sure if it was considered an error, or if it was just a prompt to make sure the user knew there was incomplete information. there are actually 2 errors that i want to take care of, and I still don't know the error code for the other one. how did you find out the error code?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Null Errors (2k)

    I still don't understand why filling in values is OK, but setting these exact same values as a default value is not OK, but if you insist... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    You might use a form-level error handler. Write code like this for the On Error event of the form itself - the following version is strictly for use during development:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox "Error with code " & DataErr & " occurred." & vbCrLf & _
    "Error message: " & AccessError(DataErr)
    Response = acDataErrContinue
    End Sub

    The DataErr argument is the error code. You can retrieve the corresponding error message by using the AccessError function.
    The Response Error can be set to two values:
    acDataErrContinue - tells Access that you handled the error
    acDataErrDisplay - tells Access to display the built-in error message (this is the default)

  13. #13
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    haha don't worry about the filling in the values, its not really important either way.

    the form-level error handler was what i was trying, i guess i should have specified that too.

    i have a habit of doing things the hard way. i think i am just gonna put in default values (feel free to pull out your hair Hans <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>). i set the date to 12/31/2050 and i hope that the users are smart enough to realize the date needs to be changed (until i can find a better solution).
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Null Errors (2k)

    With the issue on dates, would it make sense to base the default date on the current date? The Date() function can do that very nicely in combination with some other logic if necessary. Or as an alternative, you can set a validation rule on the date control (or at the table level) that complains with a specific message if the date is not within a certain set of reasonable bounds.
    Wendell

  15. #15
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Errors (2k)

    Good suggestion Wendell, thanks.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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