Number+1

[dcc15 via AccessMonster.com]

Hey Bruce (hope you see this)
I have been trying to apply the code you suggested, but as I probably don't
now what I am doing, it's not working.

Test Table (TBLdmr):
"dmrdate" (Date, General)
"dmrincrnum" (Number, Long Integer)
"dmrnum" (Text)
"partnum" (Text)

I then created a form (FRMdmr) using the wizard.
In design mode, I pasted the code in "Code Builder" on/into the forms "On
Current" event.
When I open the form the code debugger opens with "compile error: expected
sub end" message?
____________________________________________________________
Private Sub dmrincrnum_BeforeUpdate(Cancel As Integer)

End Sub
_____________________________________________________________
Private Sub DMRnum_BeforeUpdate(Cancel As Integer)

End Sub
_____________________________________________________________
Private Sub Form_Current()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")
End If

End Sub
______________________________________________________________
The line, "Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")" is in RED?
First, am I puting the "code" in the right place? I have no "Default Value"
set in Table or Form.
Thanks

 

[Duane Hookom]

For every "If ..." you need a corresponding "End If"

 

[Linq Adams via AccessMonster.com]

As Duane said, you need to close out your intitial If with an End If just
before the End Sub, and yes, Form_Current is the correct place for this type
of code.

 

[dcc15 via AccessMonster.com]

Thanks,
I think it went a little further when I added the End If:

Option Compare Database

Private Sub
Form_Load()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)
End If

If IsNull(varRes
ult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")
End If

End Sub

Private Sub Form_Current()

End Sub

It is now giving ther error: Expected: Identifier
Any help would be appreciated
Thanks

 

[dcc15 via AccessMonster.com]

I did something (?) and it is almost working. I now open the form FRMdmr in
ADD mode (Table TBLdmr contains no records) and "7110001" is entered in the
"dmrincrnum" field (like I want).
Two problems:
1). The next record is supposed to be 7110002 but the +1 is not being applied.

2). Is it possible to have the leading zero for the year (07110002)?
Thanks

Option Compare Database
Private Sub Form_Current()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)
End If

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult, 4))
+ 1, "0000")
End If

End Sub

Private Sub Form_Load()

End Sub

Thanks,
I think it went a little further when I added the End If:

Option Compare Database

Private Sub
Form_Load()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)
End If

If IsNull(varRes
ult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")
End If

End Sub

Private Sub Form_Current()

End Sub

It is now giving ther error: Expected: Identifier
Any help would be appreciated
Thanks

As Duane said, you need to close out your intitial If with an End If just
before the End Sub, and yes, Form_Current is the correct place for this type
of code.

 

[tina]

1). The next record is supposed to be 7110002 but the +1 is not being
applied.

seems to me that your code should only run when Me.NewRecord = True. But
that If expression only controls whether or not the variables are set. The
rest of the code in the Current procedure runs, regardless of the value of
the NewRecord property.

i'd suggest you run this code from the form's BeforeInsert event, instead of
the Current event; using the current event will automatically "dirty" a new
record, before the user decides to actually begin entering a new record.
using the BeforeInsert event means that the code will only run when a new
record is begun, so you won't need the NewRecord test at all.

also, Format(Date, "yymm") returns "0711", and your DMax function's criteria
would translate to [dmrincrnum] Like "0711*". but the value you cite in the
post is 7110001 - no leading zero. even if dmrincrnum were Text, not Long
Integer, the values don't match. i suspect that your DMax function is
actually erring out, and not returning a value.

try the following, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

2). Is it possible to have the leading zero for the year (07110002)?

the data type of field dmrincrnum is Long Integer, per your first post in
this thread. the only way to *store* a leading zero in the field is to
change the data type to Text. if you do that, you're going to have to change
the above code to handle a text value instead of a number. and it may impact
other code and/or other objects in the database, as well. if you just want
to *display* a leading zero where appropriate (for another two years,
anyway!), you can format the field for forms/reports as

Format(dmrincrnum, "00000000")

hth

I did something (?) and it is almost working. I now open the form FRMdmr in
ADD mode (Table TBLdmr contains no records) and "7110001" is entered in the
"dmrincrnum" field (like I want).
Two problems:
1). The next record is supposed to be 7110002 but the +1 is not being applied.

2). Is it possible to have the leading zero for the year (07110002)?
Thanks

Option Compare Database
Private Sub Form_Current()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)
End If

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult, 4))
+ 1, "0000")
End If

End Sub

Private Sub Form_Load()

End Sub

Thanks,
I think it went a little further when I added the End If:

Option Compare Database

Private Sub
Form_Load()
Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Me.dmrdate, "yymm")

If Me.NewRecord Then
strWhere = "[dmrincrnum] Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)
End If

If IsNull(varRes
ult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & Format(Val(Right(varResult,
4)) + 1, "0000")
End If

End Sub

Private Sub Form_Current()

End Sub

It is now giving ther error: Expected: Identifier
Any help would be appreciated
Thanks

As Duane said, you need to close out your intitial If with an End If just
before the End Sub, and yes, Form_Current is the correct place for this type
of code.

 

[dcc15 via AccessMonster.com]

Thanks Tina,
I had to add a ( between CDbl and Format and drop one of the 0's from "Format
(Val(Right(varResult, 4))+1, "0000")" because the first record was 7110001
but the second on was 71100002, good learning for me. Tested month and year
changes and every thing is working (great!).
Problem 2:
What I am trying to do with this is create a Document Number format, so
making "dmrincrnum" a text field would not be a problem (in the application
as no further "calculations" will be made/used, in fact I would also like to
be able to include a text string before the number (Example: DMR-07110001)
and then (I hope) be able to use this code by editing the Table, Field and
Text String portion. The number (text) will be the primary key and will also
be used to relate (one to many) some of the various tables to each other. If
converting the 'incrnum' to text is too difficult/complicated I can live
without the leading zero, I do need to get the Text String added for this to
work for me, so if you can help with this I would really appreciate it (I'm
attempting to, but so far unsuccessful).
Thanks again

1). The next record is supposed to be 7110002 but the +1 is not being
applied.

seems to me that your code should only run when Me.NewRecord = True. But
that If expression only controls whether or not the variables are set. The
rest of the code in the Current procedure runs, regardless of the value of
the NewRecord property.

i'd suggest you run this code from the form's BeforeInsert event, instead of
the Current event; using the current event will automatically "dirty" a new
record, before the user decides to actually begin entering a new record.
using the BeforeInsert event means that the code will only run when a new
record is begun, so you won't need the NewRecord test at all.

also, Format(Date, "yymm") returns "0711", and your DMax function's criteria
would translate to [dmrincrnum] Like "0711*". but the value you cite in the
post is 7110001 - no leading zero. even if dmrincrnum were Text, not Long
Integer, the values don't match. i suspect that your DMax function is
actually erring out, and not returning a value.

try the following, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

2). Is it possible to have the leading zero for the year (07110002)?

the data type of field dmrincrnum is Long Integer, per your first post in
this thread. the only way to *store* a leading zero in the field is to
change the data type to Text. if you do that, you're going to have to change
the above code to handle a text value instead of a number. and it may impact
other code and/or other objects in the database, as well. if you just want
to *display* a leading zero where appropriate (for another two years,
anyway!), you can format the field for forms/reports as

Format(dmrincrnum, "00000000")

hth

I did something (?) and it is almost working. I now open the form FRMdmr in
ADD mode (Table TBLdmr contains no records) and "7110001" is entered in the

[quoted text clipped - 68 lines]

 

[tina]

you can't add letters to a Number data type, you'd need to change the
field's data type to Text. having already helped you get your code running,
i have to say that the general rule of thumb is to NOT store multiple values
in one field. instead, what i'd really recommend is three fields, as

testPrefix testDate testCount
(Text data type) (Date data type) (Number data type)
DMR 11/10/2007 1
DMR 11/10/2007 2
etc...

concatenate the values as you need them, formatting as you go, as
testPrefix & "-" & Format(testDate,"yymm") & Format(testCount,"0000")

i'd probably use an Autonumber as the table's primary key, rather than the
three fields above (personally, i detest multi-field primary keys).

you'd need to change the code we worked on before, something like

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!testPrefix = "DMR"
Me!testDate = Date
' if not the current date, then whatever date is appropriate.

Me!dmrincrnum = Nz(DMax("dmrincrnum", "TBLdmr", _
"Year(testDate) = " &Year(Date) & _
" And Month(testDate) = " & Month(Date)), 0) + 1

End Sub

hth

Thanks Tina,
I had to add a ( between CDbl and Format and drop one of the 0's from "Format
(Val(Right(varResult, 4))+1, "0000")" because the first record was 7110001
but the second on was 71100002, good learning for me. Tested month and year
changes and every thing is working (great!).
Problem 2:
What I am trying to do with this is create a Document Number format, so
making "dmrincrnum" a text field would not be a problem (in the application
as no further "calculations" will be made/used, in fact I would also like to
be able to include a text string before the number (Example: DMR-07110001)
and then (I hope) be able to use this code by editing the Table, Field and
Text String portion. The number (text) will be the primary key and will also
be used to relate (one to many) some of the various tables to each other. If
converting the 'incrnum' to text is too difficult/complicated I can live
without the leading zero, I do need to get the Text String added for this to
work for me, so if you can help with this I would really appreciate it (I'm
attempting to, but so far unsuccessful).
Thanks again

1). The next record is supposed to be 7110002 but the +1 is not being
applied.

seems to me that your code should only run when Me.NewRecord = True. But
that If expression only controls whether or not the variables are set. The
rest of the code in the Current procedure runs, regardless of the value of
the NewRecord property.

i'd suggest you run this code from the form's BeforeInsert event, instead of
the Current event; using the current event will automatically "dirty" a new
record, before the user decides to actually begin entering a new record.
using the BeforeInsert event means that the code will only run when a new
record is begun, so you won't need the NewRecord test at all.

also, Format(Date, "yymm") returns "0711", and your DMax function's criteria
would translate to [dmrincrnum] Like "0711*". but the value you cite in the
post is 7110001 - no leading zero. even if dmrincrnum were Text, not Long
Integer, the values don't match. i suspect that your DMax function is
actually erring out, and not returning a value.

try the following, as

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 4) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

2). Is it possible to have the leading zero for the year (07110002)?

the data type of field dmrincrnum is Long Integer, per your first post in
this thread. the only way to *store* a leading zero in the field is to
change the data type to Text. if you do that, you're going to have to change
the above code to handle a text value instead of a number. and it may impact
other code and/or other objects in the database, as well. if you just want
to *display* a leading zero where appropriate (for another two years,
anyway!), you can format the field for forms/reports as

Format(dmrincrnum, "00000000")

hth

I did something (?) and it is almost working. I now open the form FRMdmr in
ADD mode (Table TBLdmr contains no records) and "7110001" is entered in

the
[quoted text clipped - 68 lines]

before the End Sub, and yes, Form_Current is the correct place for this type
of code.

 

[dcc15 via AccessMonster.com]

How do I put this?
I'd like to stay with the previous code as it is getting me closer to what I
would like to have.

I changed the Data Type to Text in the Table (TBLdmr) and the code still
works (7110001 entered).

Can the year be formatted to be all four "Characters" so the string would
read 2007110001? This would get rid of the leading zero issue. I tried
changing the Date format (in the code) from "yymm" to "yyyymm" and it worked
for the first entry but the next entry dropped the month portion (changed it
back to yymm), that would have been too easy.

I am hoping the "Prefix' can be incorporated into the code so I can used it
for ID'ing records from different tables by just editing the field/table
names and the prefix of the code.
Example: DMR-2007110001 (TBLdmr), RMA-2007110001 (TBLrma), CAR-2007110001
(TBLcar) and so on. I was planning on using this Alphanumeric "number" as a
record ID (no duplicates allowed, may Key a separate autonumber anyway).

Right now I am using the DMR number (manually entered) in a Flat File (for
all data) so I am unable to, for example, issue multiple RMA's against a
single DMR without either losing history info or have to create (copy) a new
DMR with all the same info except for the RMA info. Also the new data base
will be used and have data entered by multiple users so I am hoping to goof-
proof this part of the data entry (I have mis-entered several times/ entries
and it's "My Data").

Again, I really appreciate your help

you can't add letters to a Number data type, you'd need to change the
field's data type to Text. having already helped you get your code running,
i have to say that the general rule of thumb is to NOT store multiple values
in one field. instead, what i'd really recommend is three fields, as

testPrefix testDate testCount
(Text data type) (Date data type) (Number data type)
DMR 11/10/2007 1
DMR 11/10/2007 2
etc...

concatenate the values as you need them, formatting as you go, as
testPrefix & "-" & Format(testDate,"yymm") & Format(testCount,"0000")

i'd probably use an Autonumber as the table's primary key, rather than the
three fields above (personally, i detest multi-field primary keys).

you'd need to change the code we worked on before, something like

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!testPrefix = "DMR"
Me!testDate = Date
' if not the current date, then whatever date is appropriate.

Me!dmrincrnum = Nz(DMax("dmrincrnum", "TBLdmr", _
"Year(testDate) = " &Year(Date) & _
" And Month(testDate) = " & Month(Date)), 0) + 1

End Sub

hth

Thanks Tina,
I had to add a ( between CDbl and Format and drop one of the 0's from "Format

[quoted text clipped - 76 lines]

 

[tina]

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

i think you need to stop and study the above code, reading up on the various
functions such as DMax(), Left(), Format(), etc, so you understand what
they're doing - instead of just using them blindly. compare the code above
to the previous code i posted, to see what changes i made to handle a
4-digit year. read up especially on the Left() and Right() functions, the
Len() function, and the Mid() function if it is available in the version of
Access you're using. with an understanding of how the functions work, and
giving it some thought, i bet you'll come up with a solution that includes
your prefix.

hth

How do I put this?
I'd like to stay with the previous code as it is getting me closer to what I
would like to have.

I changed the Data Type to Text in the Table (TBLdmr) and the code still
works (7110001 entered).

Can the year be formatted to be all four "Characters" so the string would
read 2007110001? This would get rid of the leading zero issue. I tried
changing the Date format (in the code) from "yymm" to "yyyymm" and it worked
for the first entry but the next entry dropped the month portion (changed it
back to yymm), that would have been too easy.

I am hoping the "Prefix' can be incorporated into the code so I can used it
for ID'ing records from different tables by just editing the field/table
names and the prefix of the code.
Example: DMR-2007110001 (TBLdmr), RMA-2007110001 (TBLrma), CAR-2007110001
(TBLcar) and so on. I was planning on using this Alphanumeric "number" as a
record ID (no duplicates allowed, may Key a separate autonumber anyway).

Right now I am using the DMR number (manually entered) in a Flat File (for
all data) so I am unable to, for example, issue multiple RMA's against a
single DMR without either losing history info or have to create (copy) a new
DMR with all the same info except for the RMA info. Also the new data base
will be used and have data entered by multiple users so I am hoping to goof-
proof this part of the data entry (I have mis-entered several times/ entries
and it's "My Data").

Again, I really appreciate your help

you can't add letters to a Number data type, you'd need to change the
field's data type to Text. having already helped you get your code running,
i have to say that the general rule of thumb is to NOT store multiple values
in one field. instead, what i'd really recommend is three fields, as

testPrefix testDate testCount
(Text data type) (Date data type) (Number data type)
DMR 11/10/2007 1
DMR 11/10/2007 2
etc...

concatenate the values as you need them, formatting as you go, as
testPrefix & "-" & Format(testDate,"yymm") & Format(testCount,"0000")

i'd probably use an Autonumber as the table's primary key, rather than the
three fields above (personally, i detest multi-field primary keys).

you'd need to change the code we worked on before, something like

Private Sub Form_BeforeInsert(Cancel As Integer)

Me!testPrefix = "DMR"
Me!testDate = Date
' if not the current date, then whatever date is appropriate.

Me!dmrincrnum = Nz(DMax("dmrincrnum", "TBLdmr", _
"Year(testDate) = " &Year(Date) & _
" And Month(testDate) = " & Month(Date)), 0) + 1

End Sub

hth

Thanks Tina,
I had to add a ( between CDbl and Format and drop one of the 0's from

"Format
[quoted text clipped - 76 lines]

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200711/

1

 

[dcc15 via AccessMonster.com]

Thanks, got changing the year to 2007, and am thinking I will need to change
the year/month portion to MID() and create the fixed text as Left(), am I
getting this right?
I posted a new thread with the changes you have provided (also shortened the
number to 3 places, more than enough). I picked up a book yesterday and am
reading as fast as I can. Thanks again

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

i think you need to stop and study the above code, reading up on the various
functions such as DMax(), Left(), Format(), etc, so you understand what
they're doing - instead of just using them blindly. compare the code above
to the previous code i posted, to see what changes i made to handle a
4-digit year. read up especially on the Left() and Right() functions, the
Len() function, and the Mid() function if it is available in the version of
Access you're using. with an understanding of how the functions work, and
giving it some thought, i bet you'll come up with a solution that includes
your prefix.

hth

How do I put this?
I'd like to stay with the previous code as it is getting me closer to what I

[quoted text clipped - 64 lines]

 

[dcc15 via AccessMonster.com]

Thanks for the "Kick" , I was getting tired. Can you take a look at what I
have now to see if it is/ would be considered "proper", it seems to test-out
fine. If so, Thanks a lot, been trying to get this for a long time (I will
end the other thread also). I still don't understand the language (working on
that).
To put a comment in the code is there a character (I think it's ')/ way to do
that?
Anyway thanks again for the help!

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strPreYrMo As String
Dim varResult As Variant

strPreYrMo = "DMR-" & CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strPreYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPreYrMo & "001"
Else
Me.dmrincrnum = Left(varResult, 11) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If


End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

i think you need to stop and study the above code, reading up on the various
functions such as DMax(), Left(), Format(), etc, so you understand what
they're doing - instead of just using them blindly. compare the code above
to the previous code i posted, to see what changes i made to handle a
4-digit year. read up especially on the Left() and Right() functions, the
Len() function, and the Mid() function if it is available in the version of
Access you're using. with an understanding of how the functions work, and
giving it some thought, i bet you'll come up with a solution that includes
your prefix.

hth

How do I put this?
I'd like to stay with the previous code as it is getting me closer to what I

[quoted text clipped - 64 lines]

 

[tina]

To put a comment in the code is there a character (I think it's ')/ way to
do

that?

yes, it's the single quote character, as

' this is a commented line.

Can you take a look at what I
have now to see if it is/ would be considered "proper", it seems to test-out
fine.

it looks okay to me, except for

Me.dmrincrnum = Left(varResult, 11) & _
Format(Val(Right(varResult, 3)) + 1, "000")

varResult is going to hold a "complete" dmrincrnum value, such as
DMR-200711015

the first 11 characters of that string are
DMR-2007110

the last three characters, formatted and incremented, are
016

put the two strings together, and you have
DMR-2007110016

do you see how you're adding an extra character to dmrincrnum? suggest you
re-evaluate the number of characters being pulled by the Left() and Right()
functions in this portion of the code, and make adjustments as needed.

hth

Thanks for the "Kick" , I was getting tired. Can you take a look at what I
have now to see if it is/ would be considered "proper", it seems to test-out
fine. If so, Thanks a lot, been trying to get this for a long time (I will
end the other thread also). I still don't understand the language (working on
that).
To put a comment in the code is there a character (I think it's ')/ way to do
that?
Anyway thanks again for the help!

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strPreYrMo As String
Dim varResult As Variant

strPreYrMo = "DMR-" & CDbl(Format(Date, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strPreYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strPreYrMo & "001"
Else
Me.dmrincrnum = Left(varResult, 11) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If


End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim strWhere As String, strYrMo As String
Dim varResult As Variant

strYrMo = CDblFormat(Me.dmrdate, "yyyymm"))
strWhere = "CStr([dmrincrnum]) Like """ & strYrMo & "*"""
varResult = DMax("[dmrincrnum]", "TBLdmr", strWhere)

If IsNull(varResult) Then
Me.dmrincrnum = strYrMo & "0001"
Else
Me.dmrincrnum = Left(varResult, 6) & _
Format(Val(Right(varResult, 4))+1, "0000")
End If

End Sub

i think you need to stop and study the above code, reading up on the various
functions such as DMax(), Left(), Format(), etc, so you understand what
they're doing - instead of just using them blindly. compare the code above
to the previous code i posted, to see what changes i made to handle a
4-digit year. read up especially on the Left() and Right() functions, the
Len() function, and the Mid() function if it is available in the version of
Access you're using. with an understanding of how the functions work, and
giving it some thought, i bet you'll come up with a solution that includes
your prefix.

hth

How do I put this?
I'd like to stay with the previous code as it is getting me closer to

what I
[quoted text clipped - 64 lines]

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200711

/1