Dmax+1 on a continuous form

[asac]

I an new to access and I am having trouble with Dmax+1
I can get it to work on a single form but when I try to set the form to continuous on a subform it stops working
I need this number for a barcode so I can print it out and later retrieve the record using the barcode
Any help would be appreciated

 

[missinglinq]

We really need to see the code you've tried on the subform.

 

[asac]

The code I used on the form is
=Nz(DMax("[RollID]","Line_tbl"))+1

 

[missinglinq]

Where do you have this code placed in your subform?

 

[asac]

properties, Default value

 

[johndoomed]

Is there a reason why you dont use autonumber in your table?

I would put the DMax+1 in a global function, and then calling this as the default value.

 

[boblarson]

Is there a reason why you dont use autonumber in your table?

Actually there are some good reasons to not use an autonumber and use a DMax+1.

I would put the DMax+1 in a global function, and then calling this as the default value.

This is true, I would agree that it should be turned into a function. I disagree with that it should be used as the default value. If this is for a key field, it should be assigned at the last possible moment to avoid conflicts with other users getting the same number. So, the Before Update of the form would be a good place to assign this.

 

[asac]

I tried to use auto number but had problems using this in a report to print out a barcode ticket and later retrieve the record using bar code scanner
I have used this code on the before update
Private Sub RollID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_RollID

' If user puts in an existing value into previously blank control
' (i.e. because a new rec), then warn and cancel.
If IsNull(RollID.OldValue) Then
If Not IsNull(DLookup("[RollID]", "Line_tbl", "[RollID] = Form.[RollID]")) Then
MsgBox "This Roll ID # already exists.", vbExclamation
Cancel = True
End If
End If

Exit Sub

Err_RollID:
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
Exit Sub

End Sub

 

[missinglinq]

I agree completely with Bob about the Autonumbers!

John W. Vinson, MVP, puts it well:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.

I also agree that if the app is multi-user, or has any chance of becoming multi-user down the road, assigning the ID should be done in the form's BeforeUpdate.

Linq

 

[ajetrumpet]

John W. Vinson, MVP, puts it well:

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.

Although Mr. Vinson's grammar is absolutely horrible here, he's got a good point! How many times have I said that I hate autonumbers??????????????????????????

If I remember correctly, it has been more times than I can count on all 50 of my hands.

 

[asac]

I can get this code to work well on the sub form when I set it to single form, the RollID number increments by 1 every time I start a new record. But it is important that the user can view the other records in the sub form as they enter data
When I set the sub form to continuous form so that the other records in the sub form can be seen the code no longer works, the RollId no longer increments by 1
Any ideas why?