DMax() auto increment number (1 Viewer)

[jsnfrs]

Hey Guys,

I am Jason, a new member of the forum.

I have a small problem.

As it relates to Dmax functions. I am doing an invoicing database and would like to know how I can leverage the use of the Dmax function to automatically generate invoice numbers when a user opens the invoice form to create an invoice.

The database is split. The table in the back end is called tbl_invoiceregister and the form is called frm_invoiceentry. There is the "invoice nmbr" field on the form that is linked directly to the table.

I would like the database to populate this field with the next available invoice number whenever someone opens the invoice entry form to create an invoice.

Kindly assist on the coding and exact directions on where I should place this code to get the desire effects.

It would be greatly appreciated.

Regards

 

[John Big Booty]

Welcome to the forum.

The following Code in the Form's On Current Event Should do the trick;

    If Me.NewRecord = True Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[jsnfrs]

Hey John,

thanks for the response.

I currently have code in the forms on current event that limits record entries, so I tried nesting the two codes as follows but I am getting an error that tells me "variable not defined"

Private Sub Form_Current()


If Me.NewRecord = True Then
Me.asset id = Nz(DMax("asset id", "tbl_assetsregister"), 1) + 1
End If


Dim intMaxNumRecs As Integer

intMaxNumRecs = 5 'Max Number of Records to Allow

If Me.NewRecord Then
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast: .MoveFirst
If .RecordCount >= intMaxNumRecs Then
MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!"
.MoveLast
Me.Bookmark = .Bookmark
End If
End If
End With
End If

End Sub

 

[Hutchy]

Welcome to the forum.

The following Code in the Form's On Current Event Should do the trick;

    If Me.NewRecord = True Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

Thanks a lot for this!
It solved my problem!

PS. Sorry for posting in your thread.

 

[Zippersabrat]

I am trying to figure out a way to use the DMax method to auto assign numbers to items in a list. I have a list of items and each time we receive one of these items it should have a number that follows along with numbers for that item...I know this is confusing! Basically I want ItemA to follow 1,2,3 and ItemB to follow 1,2,3 and ItemC to follow 1,2,3. Is this possible or should I use a different approach?

Thanks a bunch!

 

[gemma-the-husky]

zipper

similar idea. you need an extra argument on the dmax - a "where clause". because your item is a string, it needs to end up looking ike

item = "A" - hence the chr(34) characters.


Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName", "item = " & chr(34) & itemcode & chr(34)), 0) + 1


note that this assumes the itemcode and the number are in separate fields.

A, 1
A, 2
A, 3
B, 1
B, 2

if you try to keep them together in a single string
A1
A2
A3
B1
B2

it's much harder

 

[Solo712]

I currently have code in the forms on current event that limits record entries, so I tried nesting the two codes as follows but I am getting an error that tells me "variable not defined"

  If Me.NewRecord = True Then
        Me.asset id = Nz(DMax("asset id", "tbl_assetsregister"), 1) + 1
    End If

Should be:

  If Me.NewRecord = True Then
        Me.[COLOR="red"][[/COLOR]asset id[COLOR="Red"]][/COLOR] = Nz(DMax("[COLOR="red"][[/COLOR]asset id[COLOR="Red"]][/COLOR]", "tbl_assetsregister"), 1) + 1
    End If

Also, you should be aware that placing the dmax assignment in the Current Event may cause problems in a multiuser environment. I would put it as the last statement that executes in the form's Before_Update event. This radically reduces the possibility that a duplicate key would be issued.

Best,
Jiri