Results 1 to 5 of 5
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    error trapping on a subform

    I have a form with multiple subforms arranged as tabs. The tab I am concerned with here contains information about individuals such as name, age and other demographics. There is a subform on this tab that contains phone numbers and the phone type. The main form is named Client_Intake_form, The subform on the tab is called persons_subform, and the phone number subform is called phone_contact_subform. The subform has a text box on it for the phone number named 'phoneNm' and a combo box for the phone type named 'CmboPhoneType'. Users don't have to enter phone numbers for people but if they enter a phone number, they have to enter a phone type or Access generates an error. The error is " The Microsoft Access databse engine cannot find a record in the table 'tblPhoneTypes' with key matching field(s) 'phonetype_ID'" I don't want the user to see this error so I have tried to create code to show my own error. However, after my error message is shown, the Access error still pops up. Is there any way to stop this behavior? I have tried putting this code in a variety of events on the form including the forms before update event and on the text box itself such but have failed to stop the Access error.

    Here is the code, currently on the before update event for the text box on the phone contact subform:

    Code:
    Private Sub PhoneNm_BeforeUpdate(Cancel As Integer)On Error GoTo Err_PhoneNm_BeforeUpdate
    
    
    If Me.NewRecord = True And Me.CmboPhoneType = 0 Then
        Msgbox " You must enter the type of phone.", vbOKOnly, "Missing info."
        Me.CmboPhoneType.SetFocus
    End If
    Exit_Err_PhoneNm_BeforeUpdate:
    Exit Sub
    
    
    Err_PhoneNm_BeforeUpdate:
    Msgbox Err.Description
    Resume Exit_Err_PhoneNm_BeforeUpdate
    End Sub
    Here are the table relationships:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	10 
Size:	15.9 KB 
ID:	35229



    Any advice would be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Instead of using code,can't you set the field to required?
    no code needed.

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Quote Originally Posted by ranman256 View Post
    Instead of using code,can't you set the field to required?
    no code needed.
    Setting the 'phoneType_ID' field to required at the table level doesn't change the error. I tried to set a validation rule of "is not null" on the combobox in the from but that didn't work either. Maybe I didn't do the validation rule correctly?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would combobox have phone type 0? I doubt there is a record in PhoneTypes with ID 0 therefore error. Is DefaultValue property in table SET to 0? Remove it. Make sure combobox does not have this setting. Then code checks if combobox is null. Probably should use PhoneNm AfterUpdate event or the form's BeforeUpdate event.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Quote Originally Posted by June7 View Post
    Why would combobox have phone type 0? I doubt there is a record in PhoneTypes with ID 0 therefore error. Is DefaultValue property in table SET to 0? Remove it. Make sure combobox does not have this setting. Then code checks if combobox is null. Probably should use PhoneNm AfterUpdate event or the form's BeforeUpdate event.
    The default property in the table was zero so I removed that and changed the following code changes:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)If Me.NewRecord = True Then
        If IsNull(Me.CmboPhoneType) Then
            Msgbox " You must enter the type of phone.", vbOKOnly, "Missing info."
            Cancel = True
            Me.CmboPhoneType.SetFocus
        End If
    End If
    End Sub
    Now it works. I could not get anything to work by putting the event on the combobox or the textbox.

    thanks for the tip to get me pointed in the right direction.
    Now for a new post on what seems to me to be a more difficult problem to solve!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-20-2016, 03:58 AM
  2. Replies: 3
    Last Post: 03-06-2013, 03:52 PM
  3. Append query error trapping
    By tpcervelo in forum Programming
    Replies: 4
    Last Post: 12-22-2011, 10:57 AM
  4. Proper Error Trapping
    By SemiAuto40 in forum Programming
    Replies: 6
    Last Post: 08-11-2011, 10:22 AM
  5. Form and Subform error trapping
    By usmcgrunt in forum Forms
    Replies: 8
    Last Post: 09-12-2010, 11:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums