Adding number by 1

  • Thread starter Thread starter Rene
  • Start date Start date
R

Rene

Hello,

On a form a I have a button which adds a new record.
On that form is a textfield in which I would like to have the current
date and a following number

eg

200412035 which is: dec. 3, 2004 and the fifth record of that day
200412036 which is: dec. 3, 2004 and the sixth record of that day

Every time I press the button, I want the following number to add up.
What I've so far is this...What to do with the stripes

Private Sub CmdRecPermitNew_Click()

DoCmd.GoToRecord , , acNewRec
Permit = Format([Datum], "yyyymmdd")-----

End Sub

Thnx

René
 
René

Try it like this...

Private Sub CmdRecPermitNew_Click()
Dim NextSerial As Integer
NextSerial = DMax("Val(Mid([Permit],9))","YourTable", _
"Left([Permit],8)=Format(Date(),'yyyymmdd')") + 1
DoCmd.GoToRecord , , acNewRec
Me.Permit = Format(Date, "yyyymmdd") & NextSerial
End Sub

Having said that, there are probably other ways to approach this. In
effect you are trying to store two separate pieces of information in the
one field, i.e. the permit date, and the daily serial number. I would
expect in the end it is easier to keep these in separate fields, i.e.
have a date field with the Default Value set to Date(), and a serial
number field which you can easily also autoincrement using the default
value property setting. And then, when you need this value within your
application, on forms or reports, to display as required, you can use an
expression to concatenate the string values together at the time.

In any case, instead of the code approach, you could in fact just use
the Default Value property of the Permit control on the form, set to...
Format(Date(),"yyyymmdd") &
DMax("Val(Mid([Permit],9))","YourTable","Left([Permit],8)=Format(Date(),'yyyymmdd')")
+ 1
 
Back
Top