how to autofill +1

[John]

I have a simple form for data collection. The fist field is BatchNumber. I
need this field to be automatically increased by 1 for each record as i input
data. I have tried several methods using AfterUpdate but it only works for
the 2nd record and not subsequent.
John

 

[Jeanette Cunningham]

Hi johngordon,
Here are some guidelines for doing default values

Private Sub controlname_AfterUpdate()
'text field
Me.TheTextControl.DefaultValue = """" & Me. TheTextControl & """"

'date field
Me.TheDateControl.DefaultValue = "#" & Me.TheDateControl & "#"

'number field
Me.TheNumberField.DefaultValue = Me.TheNumberField
End Sub

I noticed in the code you posted that *Me.* was missing form
BatchNumber.DefaultValue=Me.BatchNumber.Value+1

Is your batch number a number data type or text data type?
That could be anothe reason for it not working.

If you have a multi user application, you would not use the above methods
for getting the next value for a primary key.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Linq

But why will the following not work?
Private Sub BatchNumber_AfterUpdate()
BatchNumber.DefaultValue=Me.BatchNumber.Value+1
End Sub
Thanks
John

Here's a typical Auto-incrementing Number hack. The first code here would
be
for an BatchNumber that is defined in the table as Text datatype. "Number"
fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim StartName as Long

If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.BatchNumber = "1"
Else
Me.BatchNumber = DMax("val([BatchNumber])", "YourTableName") + 1
End If
End If
End Sub

Here's the same code for an BatchNumber defined as Numerical:

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

 

[Jeanette Cunningham]

Hi,
to display on the form, you need a text box which is bound to the field
called BatchNo.
You will also want the batch no to be displayed when the user starts a new
record.
So move Linq's code to the Load event instead of the Before Update event.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette

Thanks for the reply. I will try the changes soon.

BUT I have tried the method proposed by Linq above. This almost works.

But I need the Batch Number field to be displayed on the form.

Any ideas

Thanks
John

Hi johngordon,
Here are some guidelines for doing default values

Private Sub controlname_AfterUpdate()
'text field
Me.TheTextControl.DefaultValue = """" & Me. TheTextControl & """"

'date field
Me.TheDateControl.DefaultValue = "#" & Me.TheDateControl & "#"

'number field
Me.TheNumberField.DefaultValue = Me.TheNumberField
End Sub

I noticed in the code you posted that *Me.* was missing form
BatchNumber.DefaultValue=Me.BatchNumber.Value+1

Is your batch number a number data type or text data type?
That could be anothe reason for it not working.

If you have a multi user application, you would not use the above methods
for getting the next value for a primary key.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Linq

[quoted text clipped - 32 lines]

End If
End Sub