Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    supress error (2002)

    Hi All, I have a form with surname, given, and dateofbirth as the combined PK. The trouble I'm having is when the end-user uses the mouse wheel to navigate they will sometimes inadvertently go to the end of the records (as if they're entering new date). This results in an error message that is difficult to close : "Index or primary key can't contain a null value". How can I avoid this?
    Thanks, Van

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

    Re: supress error (2002)

    You could use the Before Update event of the form to check whether all three fields have been filled in, and cancel the update if one is missing.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    would that involve IF Then statements? ( I'm not all that great with the coding side of Access). ie: if surname ="" or given = "" then close form.......

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

    Re: supress error (2002)

    The code could look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.surname) Then
    Me.surname.SetFocus
    MsgBox "Please enter a surname.", vbExclamation
    Cancel = True
    ElseIf IsNull(Me.given) Then
    Me.given.SetFocus
    MsgBox "Please enter a given name.", vbExclamation
    Cancel = True
    ElseIf IsNull(Me.dateofbirth) Then
    Me.dateofbirth.SetFocus
    MsgBox "Please enter a date of birth.", vbExclamation
    Cancel = True
    End If
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    thanks Hans, that works better in terms of letting the user know what's happening, however the message "please enter a surname" keeps coming up everytime you try to scroll to the previous record (ie. the last record of the dataset). Perhaps as i've read in a previous thread a better solution would be to have a separate form reserved exclusively for data entry with a "save record" command button.
    Regards, Van

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

    Re: supress error (2002)

    Do you have code that automatically modifies the value of a field? Otherwise you shouldn't get a message when you simply scroll through records.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    yes, I do have an option group with a default value (ie. hospital site).....is this causing the problem? I've done this so the end-user doesn't have to enter the site manually and also so it's not entered incorrectly.
    Van

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

    Re: supress error (2002)

    Setting the Default Value property of a field or control is not a problem, but setting the value itself of a control *is* a problem, because it means that the record will be modified even if the user hasn't entered any data. You could set the value in the Before Insert event of the form - this will occur as soon as the user starts entering data in a new record.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    I'm not sure I fully understand the difference, but as far as i can tell, only the default value property of the option group is set. It does seem however that somehow the second bit of your explanation is what is happening??? Is the code for setting the value as follows: let site.value = 1
    Thanks,
    Van

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

    Re: supress error (2002)

    Could you post the code behind the form? Or a stripped down and zipped copy of the database? (See <post#=401925>post 401925</post#> for instructions.)

  11. #11
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    OK. see attached
    Attached Files Attached Files

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

    Re: supress error (2002)

    The culprit is the line

    Let Me.Hdegree = Null

    in the On Current event of the form. This modifies the record each time the user moves to a different record. You shouldn't do this when the user moves to a new record, and if possible I wouldn't use this line at all.

    By the way, the keyword Let is not necessary, it can be omitted except in very special circumstances which you probably won't encounter.

  13. #13
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: supress error (2002)

    Thanks muchly Hans. I never would have found that.

    Kind Regards,
    Van

Posting Permissions

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