Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Primary Key (2000)

    Hello, I have a form and sub form, in an ideal world detail is completed in the form then the sub but as you know we are not in an ideal world. Occasionally users start to fill out detail in the sub form first which invokes the "primary key cannot be null" message in relation to the main form. Problem is there is no way of getting out of the sub form area to get back to the main form without the message showing. How can I make sure the auto number PK shows immediately as the form opens rather than a user having to start completing a record? Or is there another solution you can suggest?

    Thanks Darren.

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

    Re: Primary Key (2000)

    Second question first: You can't unless you automatically fill something in yourself to start the record, which isn't a really good idea. Autonumbers are assigned when the record is actually being created, which doesn't happen until something is entered into it.

    The simplest way to handle the first question is to simply disable the subform in the oncurrent event when the parent form has a null primary key. That way, there is no way for the user to start entering something in the subform when data hasn't been entered into the parent form first.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key (2000)

    Thank you Charlotte, could you give me guidance on the coding please?

    Regards Darren.

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

    Re: Primary Key (2000)

    Since Charlotte is a bit busy at the moment, I'll take a stab at it with some air code. For the OnCurrent Event, create an event procedure that looks like:
    <font face="Georgia"><font color=blue><pre>If IsNull(Me!PrimaryKey) Then
    Me!SubFormCtrl.Enabled = False
    Else
    Me!SubFormCtrl.Enabled = True
    End If
    </pre>

    </font color=blue></font face=georgia>
    You will need to substitute your equivalent names for the PrimaryKey field, and for the subform control on your main form.
    Wendell

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

    Re: Primary Key (2000)

    And the subform would have to be enabled using similar code in the BeforeInsert event of the main form:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.SubFormCtrl.Enabled = True
    End Sub

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key (2000)

    Thanks guys, so for clarification (I ain't the most gifted with this stuff) say I have a main form called 'people' and a sub form called 'dinners'. How would the coding look, on which forms and which events would each section of coding be for?

    Thanks.

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

    Re: Primary Key (2000)

    Hi Darren,

    All code will be behind the main form 'people'. You must write code for the On Current event of this form:

    Private Sub Form_Current()
    Me.dinners.Enabled = Not IsNull(Me.PrimaryKey)
    End If

    where PrimaryKey must be replaced by the name of the autonumber PK field.

    You must also write code for the Before Insert event of the main form:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.dinners.Enabled = True
    End Sub

    The On Current event is fired when the user navigates to another record. If this is an existing record, the primary key is not null, so Not IsNull(Me.PrimaryKey) is True, and the 'dinners' subform is enabled. If the user moves to a blank new record, the primary key hasn't been filled, so Not IsNull(Me.PrimaryKey) is False, and the 'dinners' subform is disabled.

    The Before Insert event fires as soon as the user types something in a new record. That is the moment the autonumber field is assigned a new value, so the subform can safely be enabled.

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key (2000)

    Thanks Hans, it must be a long day because I just can't get things to work. Any chance you could work your touch on this attachment, I am no longer at work so this is a dummy I have mocked up at home, assume table 1 is 'people' and table 2 is 'dinners'. Thanks very much indeed.
    Attached Files Attached Files

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key (2000)

    ah ah, thanks Hans, another invaluable lesson, kind regards.

    Darren.

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

    Re: Primary Key (2000)

    You must carefully distinguish between a subform as an object in the database window and a subform as a control on the main form. They may have the same name, but that is not necessary. In the database you attached, the subform based on Table2 is named "Table2 " in the database window, and "Table2 subform" as a control on the "Table1" form. For the form based on Table3, both names are "Table3 subform".

    When you refer to a subform on the main form, you must always use the name of the subform as a control on the main form, not the name in the database window. Your code refers to "Table2", it should be "Table2 subform".

    This is the corrected and completed code:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.<!t>[Table2 Subform]<!/t>.Enabled = True
    Me.<!t>[Table3 subform]<!/t>.Enabled = True
    End Sub

    Private Sub Form_Current()
    Me.<!t>[Table2 Subform]<!/t>.Enabled = Not IsNull(Me.id)
    Me.<!t>[Table3 subform]<!/t>.Enabled = Not IsNull(Me.id)
    End Sub

    I have attached the modified database. Since it is only a mockup, I will refrain from lecturing you on naming conventions.

    (There was also an orphaned End Sub in your code, I removed that)
    Attached Files Attached Files

Posting Permissions

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