How to Add a +1 to a field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
Click to expand...

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,
How about if you want the next record to automatically increment one quarter
(3 months)?
M

John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
John,
How about if you want the next record to automatically increment one quarter
(3 months)?
Click to expand...

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]
 
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
 
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,
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?
Click to expand...

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]
 
Back
Top