Display The Next Record Number on 'New Record'?

[Orv]

(Acess2k)

I have a field (RTFnumber) where the input is based on year-record#
(04-0001).

What I want to do is to be able to have the 'new record' display the new#
(the next one) automatically.
I was thinking of using 'primary key' for this, but I don't think I will be
able to change the 'autonumber' when I start entering records in 2005
(ie..05-0001, etc...). Currently my records for the past few years have
never gone over 700 (04-0700) the extra '0' is for 'just in case'.

I think the code below may be the answer, or at least on the right track.

If Me.NewRecord Then
Me!Your Name.Value = Nz(DMax("[YourTableField]", "YourTable"), 0) + 1
End If

Will this do what I want? What do I replace 'Your Name.Value' with?

Thanks, Orv

 

[Allen Browne]

You need to assign this number at the last possible moment, so if two people
are entering new records they are not given the same number. The
BeforeUpdate event of the form is the last event that fires just before the
record is saved, so that's the one to use.

The code will look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim iNum As Integer

If Me.NewRecord Then
strWhere = "RTFnumber Like " & Format(Year(Date()), "yy")
varResult = DMax("RTFnumber", "Table1", strWhere)

If Not IsNull(varResult) Then
iNum = Val(Right(varResult, 4))
End If

Me.RTFnumber = Format(Year(Date()), "yy") & "-" & str(iNum + 1)
End If
End Sub

 

[Orv]

Thanks... I will check and let you know.

You need to assign this number at the last possible moment, so if two
people are entering new records they are not given the same number. The
BeforeUpdate event of the form is the last event that fires just before
the record is saved, so that's the one to use.

The code will look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim iNum As Integer

If Me.NewRecord Then
strWhere = "RTFnumber Like " & Format(Year(Date()), "yy")
varResult = DMax("RTFnumber", "Table1", strWhere)

If Not IsNull(varResult) Then
iNum = Val(Right(varResult, 4))
End If

Me.RTFnumber = Format(Year(Date()), "yy") & "-" & str(iNum + 1)
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

(Acess2k)

I have a field (RTFnumber) where the input is based on year-record#
(04-0001).

What I want to do is to be able to have the 'new record' display the new#
(the next one) automatically.
I was thinking of using 'primary key' for this, but I don't think I will
be able to change the 'autonumber' when I start entering records in 2005
(ie..05-0001, etc...). Currently my records for the past few years have
never gone over 700 (04-0700) the extra '0' is for 'just in case'.

I think the code below may be the answer, or at least on the right track.

If Me.NewRecord Then
Me!Your Name.Value = Nz(DMax("[YourTableField]", "YourTable"), 0) + 1
End If

Will this do what I want? What do I replace 'Your Name.Value' with?

Thanks, Orv

 

[Orv]

When I click on ' Add New Record' from the Switchboard' the blank form
shows, with focus set to 'RTFNumber' but it's filled with '00-0000'. (I
entered 04-0001) in the table 'tblRTF' prior. Shouldn't this field on the
new record display 04-0002? If I put in a new number, click save, I get my
message box saying it's saved. But when I go to close the form I get a run
time error with the following line highlighted:

Me.RTFNumber = Format(Year(Date), "yy") & "-" & Str(iNum + 1)

Also when I cut and paste your code, it gets ghanged to this:
-------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim iNum As Integer

If Me.NewRecord Then
strWhere = "RTFnumber Like " & Format(Year(Date), "yy")
varResult = DMax("RTFnumber", "tblRTF", strWhere)

If Not IsNull(varResult) Then
iNum = Val(Right(varResult, 4))
End If

Me.RTFNumber = Format(Year(Date), "yy") & "-" & Str(iNum + 1)
End If

End Sub
----------------------------------------------------------

I also have code in 'On Current'


Private Sub Form_Current()

MeAllowEdits = False ' Return the form to its read only state.

End Sub

and in 'After Update':

Private Sub Form_AfterUpdate()

MeAllowEdits = False ' Return the form to its read only state.
MsgBox "Record Saved."

End Sub
----------------------------------------
I also have a button on the form for 'Save Record', with a message box

Private Sub Ctl_Save_Record_Click()
On Error GoTo Err_Ctl_Save_Record_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Ctl_Save_Record_Click:
Exit Sub

Err_Ctl_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Ctl_Save_Record_Click

End Sub
---------------------------------
Private Sub Save_Record_Click()
MsgBox "Record Saved."
End Sub
---------------------------------


All help is greatly appreciated!!!

Orv

 

[Orv]

Here is the run-time error message:

Run-time error '2147352567 (80020009)':
The value you entered isn't valid for this field.

 

[Dan]

I'm trying almost the same thing, except when I SAVE the record I get:
Runtime error- (missing operator) in query expression 'callnumber Like05'

Selecting the Debug button brings me to the code line:
varResult=DMax("CallNumber", "tblCallLog", strwhere)

Callnumber is the field in tblCallLog

My DB logs Callins-I'd like the call number to be automatically assigned as
yy-nnnn. There will be three people using the DB as a split DB on the network.
Thanks for any help...

 

[Dan]

Sorry:
In my last post the runtime error was 3075
Dan