Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    No Error if Empy? (2007 SP1)

    I thought that Referential Integrity ensured that data couldn't be entered into a Child Table until the data in a Parent Table had been completed?

    I have a DB with 3 Tables: Client, Room, and Boxes, ie, 1 Client can have many Rooms, and each Room can contain many Boxes.

    These are displayed on 2 Forms: the Client Form, which has a button to Open the Room form (with a Subform for Boxes).

    When I create a Client and then go to enter details about the Records they have, Access allows me to complete these details without first asking me to complete the Rooms Table. Therefore, when I close the Record, no Record ID has been assigned to the Room Number (and thus, no ID is assigned to the Boxes table).

    Therefore, when I reopen the client's Room Form, all the Records don't appear. I understand why they don't appear, but I don't understand why Access allowed me to enter Records without prompting me to complete the Rooms Table. I have enforced Referential Integrity in the Relationships window, and I have made one of the Fields on the Rooms Table required.

    Has it been so long since I designed in Access that I've forgotten something simple, or is this unusual behavior?

    Thanks for any help!

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

    Re: No Error if Empy? (2007 SP1)

    Access will not allow you to create a Room record for a Client that does not exist, but it will allow you to create a client without any corresponding Rooms. You should be able to create Rooms for the Client later on.

    If you use a separate Room form, you should use code to set the Default Value property of the ClientID (or whatever you've named the field that links Rooms to Clients) to the value of ClientID in the Client form. This ensures that new records that you create in the Room form will have the ClientID of the current Client.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    Hi Hans

    Thanks for your reply, as always.

    Forgetting about the Client for a moment . . . the Room is the Parent, and the Boxes are the Children. Access *is* allowing me to create the child records without the Parent (the Room), so the child records (the boxes) become orphaned as soon as the Form is closed.

    The Room Table is quite simple: just the RoomID (AutoNumber) and Number of Rooms
    The Boxes Table has details of what's in the Boxes, date created, etc. The Foreign Key is the RoomID Number.

    Even if I set the default Foreign Key value to the RoomID number, it is still blank as Access doesn't create a Room ID unless I complete the only other Field on the Rooms Form (Number of Rooms).

    So, I'm left with a position whereby I'd have to create a Prompt to remind the user to complete the 'Number of Room's field every time they create a Record, otherwise all the Box details will become orphans.

    Why isn't Access AutoNumbering the RoomID Field as soon as a Child is entered? And why doesn't it force the user to enter something into the Number of Rooms Field when I've set this to Required?

    Thanks again.

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

    Re: No Error if Empy? (2007 SP1)

    If you have ticked the check box "Enforce referential integrity" for the relationship between Rooms and Boxes, you shouldn't be able to save a Box record unless it contains a valid RoomID.

    Access will create a new AutoNumber value for RoomID when the user enters something in a blank new record in the Rooms table.
    If the user enters something in a blank new record in the Boxes table, Access does *not* automatically create a new RoomID - that would be disastrous, for each new Box record would create a new Room record.
    You could put code in the Before Insert event of the Boxes subform to prevent the user from starting a new record if there is no parent record yet:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    ' Is there a parent record yet?
    If IsNull(Me.Parent!RoomID) Then
    ' If not, show a message
    MsgBox "Enter the number of rooms before creating a record in the subform!", vbExclamation
    ' Cancel the new child record
    Cancel = True
    ' Move back to the parent form
    Me.Parent.SetFocus
    ' And set focus to the Number of Rooms control
    Me.Parent![Number of Rooms].SetFocus
    End If
    End Sub

    Replace RoomID and Number of Rooms with the real names.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    In the table Boxes you have to set the RoomID Number as required.
    Francois

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    Thanks again Hans.

    "If you have ticked the check box "Enforce referential integrity" for the relationship between Rooms and Boxes, you shouldn't be able to save a Box record unless it contains a valid RoomID."

    That's what I thought, but Access *does* allow me to create entries in the Boxes subform. And, as stated, they all disappear when I return to the Record after closing it.

    I've added your code as a workaround, but it produces a run-time error 2449 at Me.Parent.SetFocus.

    Sorry to question you, as I know you are super-smart, but can SetFocus be added to the Parent argument? I tried typing Me.Parent. and SetFocus doesn't AutoComplete. If I try, eg, Me.ActiveControl, AutoComplete does show SetFocus as an option???

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

    Re: No Error if Empy? (2007 SP1)

    The code should be in the Before Update event of the Boxes subform, not in the Before Update of the Rooms main form. It should work OK - I copied the code straight from a database where it works as intended, and only changed the names and the text of the warning message.

    The reason that Me.Parent. doesn't make IntelliSense show an autocomplete list is that Me.Parent could be different things in different situations, so IntelliSense can't tell what would be appropriate.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    Hi Hans

    The code is in the Subform and not on the Main Form?

    ???

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

    Re: No Error if Empy? (2007 SP1)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    Sure. Thanks for taking a look!
    Attached Files Attached Files

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

    Re: No Error if Empy? (2007 SP1)

    Which tables and forms should I look at? The names are different from the ones you mentioned in this thread.

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    The Parent Form is tblSRBox and the subform is frmUndertakings.

    Sorry for any confusion--I always try to simplify the names in these posts.

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

    Re: No Error if Empy? (2007 SP1)

    The line

    Me.Parent!NoBoxes.SetFocus

    should be

    Me.Parent!cboNoBoxes.SetFocus

    because you want to set focus to a control, and cboNoBoxes is the name of the control, even if the name of the field it is bound to is NoBoxes.

    With that change, I do get the warning if I go to a new record in the main form and try to enter something in the frmUndertakings subform.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: No Error if Empy? (2007 SP1)

    Thanks Hans, and sorry for the delay in replying.

    I thought that because the De###### stopped at the Me.Parent.SetFocus line that the error was on that line (it still stops at that line, so I've just deleted it, and it works).

    Sorry, I didn't realize the error could relate to the following line.

    Thanks again for taking a look and for sussing it out.

    Take care

    Brian

Posting Permissions

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