Creating a new form with an autopopulated field

[breazzyyy]

Good morning,

I have a table with information on thousands of boxes with an Autopopulated BOX ID field, and some information. I created a form that will allow users to input a new box and all the information required into the form. The question I have is, I have another field called Box # (which is not automated) and I was wondering how I can open the form and have the Box # field autopopulate to the next number.

thank you!

[Missinglinq]

First, you need to change the to something like BoxNo or BoxNum instead of Box #. Names shouldn't have spaces (this requires extra treatment in VBA code, and, more importantly, the Octothorp (hash tag/pound sign) has a special meaning in VBA...it is used to delimit or enclose Dates. Using one like this can be confusing to the Access Gnomes!

Where you do this depends on whether or not this is going to be used in a multi-user environment. If this is how it'll be used, it needs to be done at the last second before saving a New Record, in the Form_BeforeUpdate event; this cuts down the chance on two users generating the same number. If this is only going involve a single user, you can use the same general approach in the Default Value Property of the Textbox, on the Form, but the Form_BeforeUpdate would work well here, too.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
    Me.BoxNum = 1
   Else
    Me.BoxNum = DMax("[BoxNum]", "YourTableName") + 1
   End If
 End If
End Sub

Replace YourTableName with just that, and this code assumes that BoxNum is defined as a Number Datatype. If it is defined as Text, say so ans we can adjust the syntax for that.

Linq ;0)>