Auto Numbering...Default Value: =DMax("[Task#]","[QryTask#]")+1

[AndreasO]

Please, not again, you might think...!!!

It works perfectly assigning a new Task# to a new record in a subform in
single mode view, but not in continuous mode view, since a new record is
created before the current one is fininshed.
The numbers dbl up.
As soon as I run out of space in the subform and need to use the add rec
button, it works.
Is there a way not having to write the code (sample):

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me!PartID = 0 Then
Me!PartID = Nz(DMax("PartID", "qryPartNo")) + 1
End If
End Sub

I would lke to keep it simple and could get away by just adding the addrec
control to insert a new sub rec, instead of having access do that and mess up
my numbering..

Thanks for your help

 

[Allen Browne]

Use the BeforeUpdate event of the form rather than
BeforeInsert.Form_BeforeUpdate fires at the last possible moment, reducing
the chance that 2 users entering data at the same time could be given the
same number.

Lookup the table (rather than a query) to reduce the chance that this gets
messed up if someone changes, renames, or deletes the query.

Assuming that you want the next available number limited to the ID on the
main form, it would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord
strWhere = "[MyForeignKey] = " & Me.Parent!MyID
Me!PartID = Nz(DMax("PartID", "tblParts", strWhere), 0) + 1
End If
End Sub

 

[AndreasO]

Very nice solution, it is working great, updated all my subfomrs with this
code...Thanks again

Use the BeforeUpdate event of the form rather than
BeforeInsert.Form_BeforeUpdate fires at the last possible moment, reducing
the chance that 2 users entering data at the same time could be given the
same number.

Lookup the table (rather than a query) to reduce the chance that this gets
messed up if someone changes, renames, or deletes the query.

Assuming that you want the next available number limited to the ID on the
main form, it would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord
strWhere = "[MyForeignKey] = " & Me.Parent!MyID
Me!PartID = Nz(DMax("PartID", "tblParts", strWhere), 0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Please, not again, you might think...!!!

It works perfectly assigning a new Task# to a new record in a subform in
single mode view, but not in continuous mode view, since a new record is
created before the current one is fininshed.
The numbers dbl up.
As soon as I run out of space in the subform and need to use the add rec
button, it works.
Is there a way not having to write the code (sample):

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me!PartID = 0 Then
Me!PartID = Nz(DMax("PartID", "qryPartNo")) + 1
End If
End Sub

I would lke to keep it simple and could get away by just adding the addrec
control to insert a new sub rec, instead of having access do that and mess
up
my numbering..

Thanks for your help

 

[AndreasO]

Had to turn the Project ID from autonumber into a text field because of
adding the year prefix for new records.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE '" _
& Format(date, "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 4)) + 1
End If
Me![Project ID] = Format(date, "yy") & "-" & Format(iNext, "0000")

End Sub

Its working for adding the main record, getting the YY prefix staring the 4
digit ID. But now the task# for sub records don't count up. Is it not seeing
Project ID as str??

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord Then
strWhere = "[Project ID]=" & Me![Project ID]
Me![Task#] = Nz(DMax("[Task#]", "[detail]", strWhere), 0) + 1
End If
End Sub

Thanks for your help!!





Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord Then
strWhere = "[Project ID]=" & Me![Project ID]
Me![Task#] = Nz(DMax("[Task#]", "[detail]", strWhere), 0) + 1
End If
End Sub

Use the BeforeUpdate event of the form rather than
BeforeInsert.Form_BeforeUpdate fires at the last possible moment, reducing
the chance that 2 users entering data at the same time could be given the
same number.

Lookup the table (rather than a query) to reduce the chance that this gets
messed up if someone changes, renames, or deletes the query.

Assuming that you want the next available number limited to the ID on the
main form, it would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
If Me.NewRecord
strWhere = "[MyForeignKey] = " & Me.Parent!MyID
Me!PartID = Nz(DMax("PartID", "tblParts", strWhere), 0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Please, not again, you might think...!!!

It works perfectly assigning a new Task# to a new record in a subform in
single mode view, but not in continuous mode view, since a new record is
created before the current one is fininshed.
The numbers dbl up.
As soon as I run out of space in the subform and need to use the add rec
button, it works.
Is there a way not having to write the code (sample):

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me!PartID = 0 Then
Me!PartID = Nz(DMax("PartID", "qryPartNo")) + 1
End If
End Sub

I would lke to keep it simple and could get away by just adding the addrec
control to insert a new sub rec, instead of having access do that and mess
up
my numbering..

Thanks for your help

 

[John W. Vinson]

Had to turn the Project ID from autonumber into a text field because of
adding the year prefix for new records.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE '" _
& Format(date, "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 4)) + 1
End If
Me![Project ID] = Format(date, "yy") & "-" & Format(iNext, "0000")

End Sub

Its working for adding the main record, getting the YY prefix staring the 4
digit ID. But now the task# for sub records don't count up. Is it not seeing
Project ID as str??

How on Earth could anyone here possibly tell?

What do you mean by "the task# for sub records don't count up"? Do you have a
relationship between this [Project ID] and a [Project ID] in one or more
related tables? If so, then the Project ID in the child tables must be changed
from Long Integer to Text(7) and any existing data records edited to match the
parent ID.

Remember: You can see your database. We cannot.