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

 

[Linq Adams via AccessMonster.com]

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

 

[johngordon]

Thanks Linq

But why will the following not work?
Private Sub BatchNumber_AfterUpdate()
BatchNumber.DefaultValue=Me.BatchNumber.Value+1
End Sub
Thanks
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

 

[johngordon via AccessMonster.com]

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]

 

[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

 

[AccessVandal via AccessMonster.com]

The result of the default value will not change record. What it does is just
simply change the properties of the control's default value. It does not
affect the table records.

Maybe something like this might work.

Private Sub BatchNumber_AfterUpdate()
Me.BatchNumber=Me.BatchNumber + 1
End Sub

But I would suggest something like Linq Adam's code.

 

[johngordon via AccessMonster.com]

Thanks Jeanette,
But it only displays for the first record input in the session and actually
gives an error message on subsequent records saying that the BatchNumber
field cannot be blank.
Regards

John

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

[quoted text clipped - 39 lines]