Number and Subform

[Erika]

Currently I am using a subform to populate a table. When people come into
the form, they need to go to the bottom of the data table (sub form) and
enter their new information. 2 questions one, by accident people will sort
the form by client name and forget to re-sort - can I force the sort to
always sort by permit number?

Second question - when the sort is messed up someone different comes in to
enter a new permit they look at the record above and add the next sequential
number - creating duplicates because the sort is wrong - can I add a field or
something that would display the highest number that has been used in the
permit number field?

 

[Klatuu]

There are two things you can do.
First, use the Enter event of the subform control to set the order of the
subform the way it should be.
The second, and also very important to avoid duplicates, it to use the
Before Update event of the subform form to check for duplicates before you
allow the update to continue.

Private Sub txtPermitNumber_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[PermitNumber]", "PermitTable", "[PermitNumber] =
& Me.txtPermitNumber)) Then
Msgbox Me.txtPermitNumber & " Is In Use", vbExclamation
Cancel = True
End If
End Sub

The names are all made up, use your own names.

 

[NKTower]

Shouldn't thate be done in the BeforeInsert() event? If BeforeUpdate
wouldn't that prevent changes to existing rows?

There are two things you can do.
First, use the Enter event of the subform control to set the order of the
subform the way it should be.
The second, and also very important to avoid duplicates, it to use the
Before Update event of the subform form to check for duplicates before you
allow the update to continue.

Private Sub txtPermitNumber_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[PermitNumber]", "PermitTable", "[PermitNumber] =
& Me.txtPermitNumber)) Then
Msgbox Me.txtPermitNumber & " Is In Use", vbExclamation
Cancel = True
End If
End Sub

The names are all made up, use your own names.
--
Dave Hargis, Microsoft Access MVP

Currently I am using a subform to populate a table. When people come into
the form, they need to go to the bottom of the data table (sub form) and
enter their new information. 2 questions one, by accident people will sort
the form by client name and forget to re-sort - can I force the sort to
always sort by permit number?

Second question - when the sort is messed up someone different comes in to
enter a new permit they look at the record above and add the next sequential
number - creating duplicates because the sort is wrong - can I add a field or
something that would display the highest number that has been used in the
permit number field?

 

[Klatuu]

That is what I posted. Did I not make it clear?
--
Dave Hargis, Microsoft Access MVP

Shouldn't thate be done in the BeforeInsert() event? If BeforeUpdate
wouldn't that prevent changes to existing rows?

There are two things you can do.
First, use the Enter event of the subform control to set the order of the
subform the way it should be.
The second, and also very important to avoid duplicates, it to use the
Before Update event of the subform form to check for duplicates before you
allow the update to continue.

Private Sub txtPermitNumber_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[PermitNumber]", "PermitTable", "[PermitNumber] =
& Me.txtPermitNumber)) Then
Msgbox Me.txtPermitNumber & " Is In Use", vbExclamation
Cancel = True
End If
End Sub

The names are all made up, use your own names.
--
Dave Hargis, Microsoft Access MVP

Currently I am using a subform to populate a table. When people come into
the form, they need to go to the bottom of the data table (sub form) and
enter their new information. 2 questions one, by accident people will sort
the form by client name and forget to re-sort - can I force the sort to
always sort by permit number?

Second question - when the sort is messed up someone different comes in to
enter a new permit they look at the record above and add the next sequential
number - creating duplicates because the sort is wrong - can I add a field or
something that would display the highest number that has been used in the
permit number field?