Incrementing Number

[BillA]

I read an old thread from Allen Browne who replied to a question of
incrementing number using the year and sequential number.

This is the suggestion Allen provided on 8/18/2006 (subject: Incremental
number):
_____________
You will need to use a fixed width number at the end, say 3 digits like
this: VD2006001, VD2006002, ...

Dim varMax as Variant

DocID = "VD" & Year(Date)
varMax = DMax("DocID", "Table1", "DocID Like """ & DocID & "*""")
varMax = Val(Nz(Mid(varMax, 7), 0)) + 1
DocID =DocID & Format(varMax, "000")

--
Allen Browne - Microsoft MVP.
_______________

I would like to use this code to satisfy a need to increment a currently
filled (manually) field which uses the year and sequential number, i.e.
2007001; 2007002, etc... (I don't need the addional "VD")
Can someone explain where this code is placed. In the module for the table
holding the field I will use, in my case tbl_Project.ProjectID? I haven't
been able to get this to work for me.

Any advice would be appreciated.
Thanks,
Bill

 

[Ofer Cohen]

Use this code on the Form OnCurrent event (it wont work if you want to add
the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

*********************************
Note: you need to change the FieldName (in the table), TableName and
TextBoxName (in the form) that holds the value in the table
*********************************

 

[Rick Brandt]

Use this code on the Form OnCurrent event (it wont work if you want
to add the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

*********************************
Note: you need to change the FieldName (in the table), TableName and
TextBoxName (in the form) that holds the value in the table
*********************************

Using the Current event as described though would almost certainly not work very
well in a multi-user environment. Best event for that is BeforeUpdate.

 

[Ofer Cohen]

Good point, thanks Rick

Use this code on the Form OnCurrent event (it wont work if you want
to add the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

*********************************
Note: you need to change the FieldName (in the table), TableName and
TextBoxName (in the form) that holds the value in the table
*********************************

Using the Current event as described though would almost certainly not work very
well in a multi-user environment. Best event for that is BeforeUpdate.

 

[Marshall Barton]

Use this code on the Form OnCurrent event (it wont work if you want to add
the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

In addition to Rick's remarks about multi-user issues.

Using the Current event is generally not a good idea because
the code is making the record dirty. If a user
inadvertantly navigates to the new record and back to an
existing record, the result would be a blank record being
saved. To avoid that, users will then be required to
remember (unlikely) to undo the record before navigating
back to an existing record. The BeforeUpdate event is only
triggered after the user has dirtied the record by entering
real data in the record.

 

[Amy Blankenship]

Use this code on the Form OnCurrent event (it wont work if you want to add
the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

In addition to Rick's remarks about multi-user issues.

Using the Current event is generally not a good idea because
the code is making the record dirty. If a user
inadvertantly navigates to the new record and back to an
existing record, the result would be a blank record being
saved. To avoid that, users will then be required to
remember (unlikely) to undo the record before navigating
back to an existing record. The BeforeUpdate event is only
triggered after the user has dirtied the record by entering
real data in the record.

You could also use an on current to set a default value (not set the actual
value) for the person who is entering the data to see, and use BeforeUpdate
to set the real value based on whatever records are actually in the database
when the record commits.

 

[BillA]

Hello Ofer:
I've been trying to get this code to work using the 'Befoe Update' event,
but I've been unsuccessful.

This is what my code looks like:
Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date)
MyCount = MyCount & Format(Nz(DMax("Val(Mid([ProjectNumber],5))",
"[tbl_Project]"), 0) + 1, "000")
Me.[ProjectNumber] = MyCount
End If

End Sub

Thank you, Rick, Marshall and Amy for your interest and advice.
Bill
____________________________________

Use this code on the Form OnCurrent event (it wont work if you want to add
the record directly to the table)

Something like

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date())
MyCount = MyCount &
Format(Nz(DMax("Val(Mid([FieldName],5))","[TableName]"),0)+1,"000")
Me.[TextBoxName] = MyCount
End If

*********************************
Note: you need to change the FieldName (in the table), TableName and
TextBoxName (in the form) that holds the value in the table
*********************************
--
Good Luck
BS"D

I read an old thread from Allen Browne who replied to a question of
incrementing number using the year and sequential number.

This is the suggestion Allen provided on 8/18/2006 (subject: Incremental
number):
_____________
You will need to use a fixed width number at the end, say 3 digits like
this: VD2006001, VD2006002, ...

Dim varMax as Variant

DocID = "VD" & Year(Date)
varMax = DMax("DocID", "Table1", "DocID Like """ & DocID & "*""")
varMax = Val(Nz(Mid(varMax, 7), 0)) + 1
DocID =DocID & Format(varMax, "000")

--
Allen Browne - Microsoft MVP.
_______________

I would like to use this code to satisfy a need to increment a currently
filled (manually) field which uses the year and sequential number, i.e.
2007001; 2007002, etc... (I don't need the addional "VD")
Can someone explain where this code is placed. In the module for the table
holding the field I will use, in my case tbl_Project.ProjectID? I haven't
been able to get this to work for me.

Any advice would be appreciated.
Thanks,
Bill

 

[Marshall Barton]

I've been trying to get this code to work using the 'Befoe Update' event,
but I've been unsuccessful.

This is what my code looks like:
Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date)
MyCount = MyCount & Format(Nz(DMax("Val(Mid([ProjectNumber],5))",
"[tbl_Project]"), 0) + 1, "000")
Me.[ProjectNumber] = MyCount
End If

End Sub

If by "unsuccessful", you mean that itdoesn't restart the
counter each year:

Dim varMax as Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

 

[BillA]

Hello Marshall:
Thank you for your response. Unfortunately, the code I posted doesn't
create the intended incrementing number; the field [ProjectNumber] is blank.
I see your code takes a different route and will run this to see if the
results are different.

Thank you again.
Bill

I've been trying to get this code to work using the 'Befoe Update' event,
but I've been unsuccessful.

This is what my code looks like:
Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date)
MyCount = MyCount & Format(Nz(DMax("Val(Mid([ProjectNumber],5))",
"[tbl_Project]"), 0) + 1, "000")
Me.[ProjectNumber] = MyCount
End If

End Sub

If by "unsuccessful", you mean that itdoesn't restart the
counter each year:

Dim varMax as Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

 

[Marshall Barton]

Did you try it? If so, did it do what you want? If it did
not do what you want, what did it do?
--
Marsh
MVP [MS Access]

Thank you for your response. Unfortunately, the code I posted doesn't
create the intended incrementing number; the field [ProjectNumber] is blank.
I see your code takes a different route and will run this to see if the
results are different.

I've been trying to get this code to work using the 'Befoe Update' event,
but I've been unsuccessful.

This is what my code looks like:
Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date)
MyCount = MyCount & Format(Nz(DMax("Val(Mid([ProjectNumber],5))",
"[tbl_Project]"), 0) + 1, "000")
Me.[ProjectNumber] = MyCount
End If

End Sub

If by "unsuccessful", you mean that itdoesn't restart the
counter each year:

Dim varMax as Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

 

[BillA]

Hello Marshall:
I'm still trying to work through some 'bugs.'
First let me show you where my code stands:
_____________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
__________________
The problem I'm experiencing is with the Year(Date) throwing a "Type
mismatch." When I remove 'Year' and leave (Date) the ProjectNumber field is
populated with a date of: 5/29/2007001, and it isn't incrementing. Again my
goal is to show the year and incrementing numbers, reset at the begining of
each year.

Any suggestions?

Thanks again for your interest.
Bill

Did you try it? If so, did it do what you want? If it did
not do what you want, what did it do?
--
Marsh
MVP [MS Access]

Thank you for your response. Unfortunately, the code I posted doesn't
create the intended incrementing number; the field [ProjectNumber] is blank.
I see your code takes a different route and will run this to see if the
results are different.

BillA wrote:
I've been trying to get this code to work using the 'Befoe Update' event,
but I've been unsuccessful.

This is what my code looks like:
Private Sub ProjectNumber_BeforeUpdate(Cancel As Integer)

Dim MyCount As String
If Me.NewRecord Then
MyCount = Year(Date)
MyCount = MyCount & Format(Nz(DMax("Val(Mid([ProjectNumber],5))",
"[tbl_Project]"), 0) + 1, "000")
Me.[ProjectNumber] = MyCount
End If

End Sub

If by "unsuccessful", you mean that itdoesn't restart the
counter each year:

Dim varMax as Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

 

[Marshall Barton]

I'm still trying to work through some 'bugs.'
First let me show you where my code stands:
_____________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
__________________
The problem I'm experiencing is with the Year(Date) throwing a "Type
mismatch." When I remove 'Year' and leave (Date) the ProjectNumber field is
populated with a date of: 5/29/2007001, and it isn't incrementing. Again my
goal is to show the year and incrementing numbers, reset at the begining of
each year.

Both Year and Date are built-in VBA functions. Since the
Date is coming through fine in your test, I conclude that
you have defined something (probably a Function) named
"Year" and that is overriding the VBA function. Use the VBA
Edit - Find menu item to search your entire project and
rename the function and all references to it.

Note: Make sure you turn OFF the NameAutoCorrect feature
(Access Tools - Options, General tab) BEFORE making this
kind of change. Actually, unless you really, really know
all about this feature, you should never turn it on.

 

[BillA]

Hi Marshall:
Your suggestion to look for 'Year' defined somewhere in my db found the
following;
No functions with Year, however I do have a field, in the same table that
this code will add a record, named 'Year.' I followed your recommendations
to change this field's name after turning off "Perform Name Auto Correct." I
now get the following error:
Run-time error '2465:'
....Database can't find the field 'Year' referred to in your expression.

I've tried to adjust the code using my feeble knowledge of VB - without any
success.

BTW: I will be removing the field year - as I realize it is unecessary.
Any suggestions?
Thanks again,
Bill

I'm still trying to work through some 'bugs.'
First let me show you where my code stands:
_____________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
__________________
The problem I'm experiencing is with the Year(Date) throwing a "Type
mismatch." When I remove 'Year' and leave (Date) the ProjectNumber field is
populated with a date of: 5/29/2007001, and it isn't incrementing. Again my
goal is to show the year and incrementing numbers, reset at the begining of
each year.

Both Year and Date are built-in VBA functions. Since the
Date is coming through fine in your test, I conclude that
you have defined something (probably a Function) named
"Year" and that is overriding the VBA function. Use the VBA
Edit - Find menu item to search your entire project and
rename the function and all references to it.

Note: Make sure you turn OFF the NameAutoCorrect feature
(Access Tools - Options, General tab) BEFORE making this
kind of change. Actually, unless you really, really know
all about this feature, you should never turn it on.

 

[Marshall Barton]

You seem to still have a reference to the Year field
somwhere. It would be a big help if you can find a way to
narrow down the point where the error occurs. I am not
familiar with that particular message. If it was in a text
box expression, I would expect #Name instead of the message.
If it was in a query, I would expect you to be prompted for
a value. Maybe you have some code that refers to that field
in a recordset???

In the meantime, you can work around the name conflict in
the BeforeUpdate procedure we've been working on by
disambiguating the function name:

DocID = VBA.Year(Date)
--
Marsh
MVP [MS Access]

Your suggestion to look for 'Year' defined somewhere in my db found the
following;
No functions with Year, however I do have a field, in the same table that
this code will add a record, named 'Year.' I followed your recommendations
to change this field's name after turning off "Perform Name Auto Correct." I
now get the following error:
Run-time error '2465:'
...Database can't find the field 'Year' referred to in your expression.

I've tried to adjust the code using my feeble knowledge of VB - without any
success.

BTW: I will be removing the field year - as I realize it is unecessary.

I'm still trying to work through some 'bugs.'
First let me show you where my code stands:
_____________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
__________________
The problem I'm experiencing is with the Year(Date) throwing a "Type
mismatch." When I remove 'Year' and leave (Date) the ProjectNumber field is
populated with a date of: 5/29/2007001, and it isn't incrementing. Again my
goal is to show the year and incrementing numbers, reset at the begining of
each year.

Both Year and Date are built-in VBA functions. Since the
Date is coming through fine in your test, I conclude that
you have defined something (probably a Function) named
"Year" and that is overriding the VBA function. Use the VBA
Edit - Find menu item to search your entire project and
rename the function and all references to it.

Note: Make sure you turn OFF the NameAutoCorrect feature
(Access Tools - Options, General tab) BEFORE making this
kind of change. Actually, unless you really, really know
all about this feature, you should never turn it on.

 

[BillA]

Marshall:

Brilliant !!
That did the trick. Although I will need to find where that error is
originating; at least I can move on – thanks to your help.

I’m posting the final code for those who may need it:
_______________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = VBA.Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
_______________________________________________

Thank you again for your time and assistance.
Bill

You seem to still have a reference to the Year field
somwhere. It would be a big help if you can find a way to
narrow down the point where the error occurs. I am not
familiar with that particular message. If it was in a text
box expression, I would expect #Name instead of the message.
If it was in a query, I would expect you to be prompted for
a value. Maybe you have some code that refers to that field
in a recordset???

In the meantime, you can work around the name conflict in
the BeforeUpdate procedure we've been working on by
disambiguating the function name:

DocID = VBA.Year(Date)
--
Marsh
MVP [MS Access]

Your suggestion to look for 'Year' defined somewhere in my db found the
following;
No functions with Year, however I do have a field, in the same table that
this code will add a record, named 'Year.' I followed your recommendations
to change this field's name after turning off "Perform Name Auto Correct." I
now get the following error:
Run-time error '2465:'
...Database can't find the field 'Year' referred to in your expression.

I've tried to adjust the code using my feeble knowledge of VB - without any
success.

BTW: I will be removing the field year - as I realize it is unecessary.

BillA wrote:
I'm still trying to work through some 'bugs.'
First let me show you where my code stands:
_____________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
__________________
The problem I'm experiencing is with the Year(Date) throwing a "Type
mismatch." When I remove 'Year' and leave (Date) the ProjectNumber field is
populated with a date of: 5/29/2007001, and it isn't incrementing. Again my
goal is to show the year and incrementing numbers, reset at the begining of
each year.

Both Year and Date are built-in VBA functions. Since the
Date is coming through fine in your test, I conclude that
you have defined something (probably a Function) named
"Year" and that is overriding the VBA function. Use the VBA
Edit - Find menu item to search your entire project and
rename the function and all references to it.

Note: Make sure you turn OFF the NameAutoCorrect feature
(Access Tools - Options, General tab) BEFORE making this
kind of change. Actually, unless you really, really know
all about this feature, you should never turn it on.

 

[Maggie]

Hello gentlemen,
I believe your conversation and code get me ALMOST to where I need to be as
well...
My fields involve a "file number" (simply 1, 2, 3...) that is assigned based
on what a user enters into a state field. For example, every time a user
enters a record for Florida, I want the File Number to increment by 1 based
on the last max number for Florida. I'd like my table to end up looking
something like this

File# State
1 FL
2 FL
1 OH
3 FL
2 OH
1 IN
etc...
I'll be entering these records in a form so I considered the on exit
property on the State field, but not sure how my code should look.
I'm desparate for any assistance on how to write the code. Thanks so much...
Maggie

 

[AccessVandal via AccessMonster.com]

Hi Maggie,

You have to start a new thread for this, as it will involve ADO/DAO
recordsets.

For a start, do something like….(avoid using the reserved character “#†in
the field)

Dim rs As DAO.Recordset
Dim strSql As String

‘the name of the “State†the field textbox or combobox
‘as “combobox†to filter the recordset

strSql = "SELECT State FROM YourTable Where State = ‘" & me.combobox &
â€â€™â€
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

If not rs.EOF then
rs.movelast ‘move to last record
‘add 1 to last filenumber to your form’s field
me.FileNumber = rs!FileNumber + 1
else
msgbox “no records found, first number addedâ€
me.filenumber = 1
end if

rs.Close
Set rs = Nothing

Note:
Me.FileNumber is the form’s control name
rs!FileNumber is the recordset’s field name of your table

 

[Marshall Barton]

The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub
--
Marsh
MVP [MS Access]

I believe your conversation and code get me ALMOST to where I need to be as
well...
My fields involve a "file number" (simply 1, 2, 3...) that is assigned based
on what a user enters into a state field. For example, every time a user
enters a record for Florida, I want the File Number to increment by 1 based
on the last max number for Florida. I'd like my table to end up looking
something like this

File# State
1 FL
2 FL
1 OH
3 FL
2 OH
1 IN
etc...
I'll be entering these records in a form so I considered the on exit
property on the State field, but not sure how my code should look.
I'm desparate for any assistance on how to write the code. Thanks so much...
Maggie

Marshall:

Brilliant !!
That did the trick. Although I will need to find where that error is
originating; at least I can move on – thanks to your help.

I’m posting the final code for those who may need it:
_______________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = VBA.Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
_______________________________________________

Thank you again for your time and assistance.
Bill

 

[Maggie]

We're so close...I can feel it...

I'm afraid I probably haven't been as precise with my explanation of my need
as I should have been. The code itself should generate a [filenumber] and
populate it in the [filenumber] field in the "Property Table". (btw, I can't
get around the spaces in the table and file names)

The User will just be entering into basic text boxes that populate the
Property table. Eventually they come to a field called [State Name] and they
will simply be typing a 2-letter abbreviation like "CA" or "NY", etc... so
step by step I need
1. the code to assess the value entered into the [State Name] field on the
form
1.1 if this is the 1st occurence of that state, then the [filenumber] for
that record should = "1"
ELSE
2. lookup the max [filenumber] value associated with that [State Name]
3. Add 1 to that [filenumber]
4. Populate that new value to the record's [filenumber] field in the table

As I look back on my first question, I'm feeling that this is a better
description of what I'm looking to do. Again, any thought are greatly
appreciated.

The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub
--
Marsh
MVP [MS Access]

I believe your conversation and code get me ALMOST to where I need to be as
well...
My fields involve a "file number" (simply 1, 2, 3...) that is assigned based
on what a user enters into a state field. For example, every time a user
enters a record for Florida, I want the File Number to increment by 1 based
on the last max number for Florida. I'd like my table to end up looking
something like this

File# State
1 FL
2 FL
1 OH
3 FL
2 OH
1 IN
etc...
I'll be entering these records in a form so I considered the on exit
property on the State field, but not sure how my code should look.
I'm desparate for any assistance on how to write the code. Thanks so much...
Maggie

Marshall:

Brilliant !!
That did the trick. Although I will need to find where that error is
originating; at least I can move on – thanks to your help.

I’m posting the final code for those who may need it:
_______________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varMax As Variant
Dim DocID As String
DocID = VBA.Year(Date)
varMax = Nz(DMax("Val(Mid(ProjectNumber, 5))", _
"tbl_Project", _
"ProjectNumber Like """ & DocID & "*"""), 0) + 1
Me.[ProjectNumber] = DocID & Format(varMax, "000")

End Sub
_______________________________________________

Thank you again for your time and assistance.
Bill

 

[Marshall Barton]

I can feel it too ;-)

You first post was clear and I understood it just fine.
Unfortunately, I typed = when I meant <>

Using your names, change two lines to:

If Nz(Me.[State Name], "") <> "" Then 'must have state
Me.filenumber = DMax("filenumber", "Property Table", _
"[State Name] = """ & Me.[State Name] & """ "), 0) + 1
--
Marsh
MVP [MS Access]

We're so close...I can feel it...

I'm afraid I probably haven't been as precise with my explanation of my need
as I should have been. The code itself should generate a [filenumber] and
populate it in the [filenumber] field in the "Property Table". (btw, I can't
get around the spaces in the table and file names)

The User will just be entering into basic text boxes that populate the
Property table. Eventually they come to a field called [State Name] and they
will simply be typing a 2-letter abbreviation like "CA" or "NY", etc... so
step by step I need
1. the code to assess the value entered into the [State Name] field on the
form
1.1 if this is the 1st occurence of that state, then the [filenumber] for
that record should = "1"
ELSE
2. lookup the max [filenumber] value associated with that [State Name]
3. Add 1 to that [filenumber]
4. Populate that new value to the record's [filenumber] field in the table

As I look back on my first question, I'm feeling that this is a better
description of what I'm looking to do. Again, any thought are greatly
appreciated.

The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub