How to Add a +1 to a field?

[JR]

I am trying to have a seq# starting at 64 then increaseing by one each time I
add a new record is there a way to do this?

 

[John Vinson]

I am trying to have a seq# starting at 64 then increaseing by one each time I
add a new record is there a way to do this?

Use a Long Integer field in your table (don't use the # character in
the name though, it's a date delimiter and may cause trouble in the
future).

You MUST - no option! - use a Form to add data to the table; table
datasheets don't have any usable events.

In the Form's BeforeInsert event, click the ... icon, invoke the Code
Builder, and edit it to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![txtSeq] = NZ(DMax("[Seq]", "[yourtablename]"), 63) + 1
End Sub

This will put 64 into the table for the first record added, and
increment the highest existing value by one for each subsequent
record.

John W. Vinson[MVP]

 

[chickalina]

John,
How about if you want the next record to automatically increment one quarter
(3 months)?
M

I am trying to have a seq# starting at 64 then increaseing by one each time I
add a new record is there a way to do this?

Use a Long Integer field in your table (don't use the # character in
the name though, it's a date delimiter and may cause trouble in the
future).

You MUST - no option! - use a Form to add data to the table; table
datasheets don't have any usable events.

In the Form's BeforeInsert event, click the ... icon, invoke the Code
Builder, and edit it to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![txtSeq] = NZ(DMax("[Seq]", "[yourtablename]"), 63) + 1
End Sub

This will put 64 into the table for the first record added, and
increment the highest existing value by one for each subsequent
record.

John W. Vinson[MVP]

 

[John Vinson]

John,
How about if you want the next record to automatically increment one quarter
(3 months)?

If you're using a Date field bound to a textbox named txtQuarter, put
the following code in the FOrm's AfterUpdate event:

Private Sub Form_AfterUpdate()
Me!txtQuarter.DefaultValue = "'" & _
Format(DateAdd("m", 3, Me.txtQuarter), "mm/dd/yyyy") & "'"
End Sub

This will set the DefaultValue property of the textbox to

'06/01/2007'

if the current record has 03/01/2007 in it.

John W. Vinson[MVP]

 

[scubadiver]

In my db, I am trying to use the dmax function

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!CustomerID = DMax("[CustomerID]", "[tble_customer]") + 1
Me.Dirty = False

End Sub

It isn't working.

I am using a command button to create a new record (not the navigation
buttons).

Is there something I am missing?

thanks

 

[scubadiver]

it does work. I checked what is required for the beforeinsert event to work.

 

[chickalina]

John,
That worked great!!! Thanks.
Just one small thing... when you advance to the next record, the date
advances, but you have to edit the date to get the next record to advance.
What can I change about the code to get the cell to advance when a different
field is used as an AfterUpdate?
M

 

[John Vinson]

John,
That worked great!!! Thanks.
Just one small thing... when you advance to the next record, the date
advances, but you have to edit the date to get the next record to advance.
What can I change about the code to get the cell to advance when a different
field is used as an AfterUpdate?

Well... you don't need to update the date itself, but you do need to
update SOMETHING in the record. Does this table consist only of the
quarter dates and nothing else??? If so you'll need a different
technique, perhaps an Append query.

John W. Vinson[MVP]