Primary Key (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Regards Darren.

Reply | Quote

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:

If IsNull(Me!PrimaryKey) Then
    Me!SubFormCtrl.Enabled = False
Else
    Me!SubFormCtrl.Enabled = True
End If

You will need to substitute your equivalent names for the PrimaryKey field, and for the subform control on your main form.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.[Table2 Subform].Enabled = True
Me.[Table3 subform].Enabled = True
End Sub

Private Sub Form_Current()
Me.[Table2 Subform].Enabled = Not IsNull(Me.id)
Me.[Table3 subform].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)

Reply | Quote

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

Darren.

Reply | Quote

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

Darren.

Reply | Quote

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.[Table2 Subform].Enabled = True
Me.[Table3 subform].Enabled = True
End Sub

Private Sub Form_Current()
Me.[Table2 Subform].Enabled = Not IsNull(Me.id)
Me.[Table3 subform].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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

If IsNull(Me!PrimaryKey) Then
    Me!SubFormCtrl.Enabled = False
Else
    Me!SubFormCtrl.Enabled = True
End If

You will need to substitute your equivalent names for the PrimaryKey field, and for the subform control on your main form.

Reply | Quote

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

Regards Darren.

Reply | Quote

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.

Reply | Quote