DMax help

[Bruce]

On several previous occasions I have posted a variant of
this question, and have received a variety of answers, but
am unfortunately as far away from a solution as I was
several days ago. If what I am trying to do is not
possible, please let me know, and I will stop asking.
I have a form (frmMain). It contains the text box
[txtFormID]. The table (tblMain) that underlies the form
contains the field [FormID]. [txtFormID] in frmMain has
as its record source [FormID] in tblMain. The first form
of the year is to be numbered (in [FormID]) 04-01, the
second 04-02, etc. 04 is the year. I don't care how it
gets there, but I would like the numbering to start
automatically with 05-01 next year.
My idea was to create a field [Increment] in tblMain, and
to have the value of [FormID] be (Year)&"-"&[Increment].
I got the idea that I could use DMax to increment the
number. I tried attaching to On Current:
If Me.NewRecord Then
Me.txtFormID = Nz(DMax("[Increment]", "tblRFImain"), 0)
+ 1
End If

as a means of incrementing a number (not bothering with
the 04- part of the number for now), but it did not work.
When I used the same formula except with [FormID] in place
of [Increment], it worked, and [txtFormID] showed 1, 2, 3,
etc. on successive records.

I have seen variants of this formula attached to Before
Update. That works, but I would prefer that the user see
the form number when a new form is started. I believe I
can attach something to the On Error event to increment
the number in case there are two users at the same time,
assuming that I can get it to work in the first place.
Also, I have seen variants with Me.txtFormID at the
beginning of the code, and other with Me![txtFormID].
Both seem to work. What's up with that?
Thanks in anticipation of good news, but failing that, any
news.

 

[Pavel Romashkin]

What you want to do is certainly possible.
Make your FormID field numeric, type Long, set Required to Yes,
Duplicates to No.
Make your txtFormId unbound and not editable. Set Format property of
txtFormID to 00-00.
Put the following into the Current event of the form:

Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 4))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 2)) + 1
Me!FormID = Year(Date) & Format(lTemp, "00")
End If

Notice that it is up to you to decide how many forms will be allowed for
the year. If you may have more than 99, then use

Private Sub Form_Current()
Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 5))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 3)) + 1
Me!FormID = Year(Date) & Format(lTemp, "000")
End If
End Sub

and set format string to 00-000.
Cheers,
Pavel

On several previous occasions I have posted a variant of
this question, and have received a variety of answers, but
am unfortunately as far away from a solution as I was
several days ago. If what I am trying to do is not
possible, please let me know, and I will stop asking.
I have a form (frmMain). It contains the text box
[txtFormID]. The table (tblMain) that underlies the form
contains the field [FormID]. [txtFormID] in frmMain has
as its record source [FormID] in tblMain. The first form
of the year is to be numbered (in [FormID]) 04-01, the
second 04-02, etc. 04 is the year. I don't care how it
gets there, but I would like the numbering to start
automatically with 05-01 next year.
My idea was to create a field [Increment] in tblMain, and
to have the value of [FormID] be (Year)&"-"&[Increment].
I got the idea that I could use DMax to increment the
number. I tried attaching to On Current:
If Me.NewRecord Then
Me.txtFormID = Nz(DMax("[Increment]", "tblRFImain"), 0)
+ 1
End If

as a means of incrementing a number (not bothering with
the 04- part of the number for now), but it did not work.
When I used the same formula except with [FormID] in place
of [Increment], it worked, and [txtFormID] showed 1, 2, 3,
etc. on successive records.

I have seen variants of this formula attached to Before
Update. That works, but I would prefer that the user see
the form number when a new form is started. I believe I
can attach something to the On Error event to increment
the number in case there are two users at the same time,
assuming that I can get it to work in the first place.
Also, I have seen variants with Me.txtFormID at the
beginning of the code, and other with Me![txtFormID].
Both seem to work. What's up with that?
Thanks in anticipation of good news, but failing that, any
news.

 

[Bruce]

It almost worked. However, it showed the first two
numbers as 00. I changed a line of the code as follows:

Me!FormID = Format(Date, "yy") & Format(lTemp, "00")
This got the necessary 04-01 onto the first record, but
when I tried to start a new record I got an Invalid Use of
Null at this line: Me.txt = CLng(Right(Me!RFI, 4))
I did not get that error message until I changed the date
formatting.
What all of this tells me is that I have no business
attempting to create a form to be used by others if I do
not begin to understand what is happening benind the
scenes. I had thought that all I had to do was to use
DMax to increment a number, concatenate it with the date,
and I would be done. I will need to use concatenated
fields elsewhere in this and other projects, and my
complete inability to do so tells me that the project is
doomed until I can learn much, much more than I know now.
I appreciate your help, but it is time to cut my losses.
Thanks for taking the time to work on this problem.

-----Original Message-----
What you want to do is certainly possible.
Make your FormID field numeric, type Long, set Required to Yes,
Duplicates to No.
Make your txtFormId unbound and not editable. Set Format property of
txtFormID to 00-00.
Put the following into the Current event of the form:

Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 4))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 2)) + 1
Me!FormID = Year(Date) & Format(lTemp, "00")
End If

Notice that it is up to you to decide how many forms will be allowed for
the year. If you may have more than 99, then use

Private Sub Form_Current()
Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 5))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 3)) + 1
Me!FormID = Year(Date) & Format(lTemp, "000")
End If
End Sub

and set format string to 00-000.
Cheers,
Pavel

On several previous occasions I have posted a variant of
this question, and have received a variety of answers, but
am unfortunately as far away from a solution as I was
several days ago. If what I am trying to do is not
possible, please let me know, and I will stop asking.
I have a form (frmMain). It contains the text box
[txtFormID]. The table (tblMain) that underlies the form
contains the field [FormID]. [txtFormID] in frmMain has
as its record source [FormID] in tblMain. The first form
of the year is to be numbered (in [FormID]) 04-01, the
second 04-02, etc. 04 is the year. I don't care how it
gets there, but I would like the numbering to start
automatically with 05-01 next year.
My idea was to create a field [Increment] in tblMain, and
to have the value of [FormID] be (Year)&"-"&[Increment].
I got the idea that I could use DMax to increment the
number. I tried attaching to On Current:
If Me.NewRecord Then
Me.txtFormID = Nz(DMax("[Increment]", "tblRFImain"), 0)
+ 1
End If

as a means of incrementing a number (not bothering with
the 04- part of the number for now), but it did not work.
When I used the same formula except with [FormID] in place
of [Increment], it worked, and [txtFormID] showed 1, 2, 3,
etc. on successive records.

I have seen variants of this formula attached to Before
Update. That works, but I would prefer that the user see
the form number when a new form is started. I believe I
can attach something to the On Error event to increment
the number in case there are two users at the same time,
assuming that I can get it to work in the first place.
Also, I have seen variants with Me.txtFormID at the
beginning of the code, and other with Me![txtFormID].
Both seem to work. What's up with that?
Thanks in anticipation of good news, but failing that, any
news.

.

 

[Pavel Romashkin]

Something didn't work right for you. The code I posted is from a
functional sample database with a table and a form. If you'd like, pick
it up here

http://www.ainaco.com/access/sample.mdb

I hope it will help you understand the issue better.
Cheers,
Pavel

It almost worked. However, it showed the first two
numbers as 00. I changed a line of the code as follows:

Me!FormID = Format(Date, "yy") & Format(lTemp, "00")
This got the necessary 04-01 onto the first record, but
when I tried to start a new record I got an Invalid Use of
Null at this line: Me.txt = CLng(Right(Me!RFI, 4))
I did not get that error message until I changed the date
formatting.
What all of this tells me is that I have no business
attempting to create a form to be used by others if I do
not begin to understand what is happening benind the
scenes. I had thought that all I had to do was to use
DMax to increment a number, concatenate it with the date,
and I would be done. I will need to use concatenated
fields elsewhere in this and other projects, and my
complete inability to do so tells me that the project is
doomed until I can learn much, much more than I know now.
I appreciate your help, but it is time to cut my losses.
Thanks for taking the time to work on this problem.

-----Original Message-----
What you want to do is certainly possible.
Make your FormID field numeric, type Long, set Required to Yes,
Duplicates to No.
Make your txtFormId unbound and not editable. Set Format property of
txtFormID to 00-00.
Put the following into the Current event of the form:

Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 4))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 2)) + 1
Me!FormID = Year(Date) & Format(lTemp, "00")
End If

Notice that it is up to you to decide how many forms will be allowed for
the year. If you may have more than 99, then use

Private Sub Form_Current()
Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 5))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 3)) + 1
Me!FormID = Year(Date) & Format(lTemp, "000")
End If
End Sub

and set format string to 00-000.
Cheers,
Pavel

On several previous occasions I have posted a variant of
this question, and have received a variety of answers, but
am unfortunately as far away from a solution as I was
several days ago. If what I am trying to do is not
possible, please let me know, and I will stop asking.
I have a form (frmMain). It contains the text box
[txtFormID]. The table (tblMain) that underlies the form
contains the field [FormID]. [txtFormID] in frmMain has
as its record source [FormID] in tblMain. The first form
of the year is to be numbered (in [FormID]) 04-01, the
second 04-02, etc. 04 is the year. I don't care how it
gets there, but I would like the numbering to start
automatically with 05-01 next year.
My idea was to create a field [Increment] in tblMain, and
to have the value of [FormID] be (Year)&"-"&[Increment].
I got the idea that I could use DMax to increment the
number. I tried attaching to On Current:
If Me.NewRecord Then
Me.txtFormID = Nz(DMax("[Increment]", "tblRFImain"), 0)
+ 1
End If

as a means of incrementing a number (not bothering with
the 04- part of the number for now), but it did not work.
When I used the same formula except with [FormID] in place
of [Increment], it worked, and [txtFormID] showed 1, 2, 3,
etc. on successive records.

I have seen variants of this formula attached to Before
Update. That works, but I would prefer that the user see
the form number when a new form is started. I believe I
can attach something to the On Error event to increment
the number in case there are two users at the same time,
assuming that I can get it to work in the first place.
Also, I have seen variants with Me.txtFormID at the
beginning of the code, and other with Me![txtFormID].
Both seem to work. What's up with that?
Thanks in anticipation of good news, but failing that, any
news.

.

 

[Bruce]

Thanks. I have downloaded the database at the link you
provided. I doubt I will be able to look at it for a
while, as the time I have put into attempting to solve
this problem has put me behind in a lot of other things,
but thanks for the help.

-----Original Message-----
Something didn't work right for you. The code I posted is from a
functional sample database with a table and a form. If you'd like, pick
it up here

http://www.ainaco.com/access/sample.mdb

I hope it will help you understand the issue better.
Cheers,
Pavel

It almost worked. However, it showed the first two
numbers as 00. I changed a line of the code as follows:

Me!FormID = Format(Date, "yy") & Format (lTemp, "00")
This got the necessary 04-01 onto the first record, but
when I tried to start a new record I got an Invalid Use of
Null at this line: Me.txt = CLng(Right(Me!RFI, 4))
I did not get that error message until I changed the date
formatting.
What all of this tells me is that I have no business
attempting to create a form to be used by others if I do
not begin to understand what is happening benind the
scenes. I had thought that all I had to do was to use
DMax to increment a number, concatenate it with the date,
and I would be done. I will need to use concatenated
fields elsewhere in this and other projects, and my
complete inability to do so tells me that the project is
doomed until I can learn much, much more than I know now.
I appreciate your help, but it is time to cut my losses.
Thanks for taking the time to work on this problem.

-----Original Message-----
What you want to do is certainly possible.
Make your FormID field numeric, type Long, set Required to Yes,
Duplicates to No.
Make your txtFormId unbound and not editable. Set

Format
property of

txtFormID to 00-00.
Put the following into the Current event of the form:

Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 4))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 2)) + 1
Me!FormID = Year(Date) & Format(lTemp, "00")
End If

Notice that it is up to you to decide how many forms

will
be allowed for

the year. If you may have more than 99, then use

Private Sub Form_Current()
Dim lTemp As Long, sTemp As String
Me.txtFormID = CLng(Right(Me!FormID, 5))
If Me.NewRecord Then
lTemp = Nz(DMax("FormID", "FormIDTable"), 0)
sTemp = CStr(lTemp)
lTemp = CLng(Right(sTemp, 3)) + 1
Me!FormID = Year(Date) & Format(lTemp, "000")
End If
End Sub

and set format string to 00-000.
Cheers,
Pavel

Bruce wrote:

On several previous occasions I have posted a variant of
this question, and have received a variety of

answers,
but

am unfortunately as far away from a solution as I was
several days ago. If what I am trying to do is not
possible, please let me know, and I will stop asking.
I have a form (frmMain). It contains the text box
[txtFormID]. The table (tblMain) that underlies the form
contains the field [FormID]. [txtFormID] in frmMain has
as its record source [FormID] in tblMain. The first form
of the year is to be numbered (in [FormID]) 04-01, the
second 04-02, etc. 04 is the year. I don't care how it
gets there, but I would like the numbering to start
automatically with 05-01 next year.
My idea was to create a field [Increment] in tblMain, and
to have the value of [FormID] be (Year)&"-"& [Increment].
I got the idea that I could use DMax to increment the
number. I tried attaching to On Current:
If Me.NewRecord Then
Me.txtFormID = Nz(DMax

("[Increment]", "tblRFImain"),
0)

+ 1
End If

as a means of incrementing a number (not bothering with
the 04- part of the number for now), but it did not work.
When I used the same formula except with [FormID] in place
of [Increment], it worked, and [txtFormID] showed 1,

2,
3,

etc. on successive records.

I have seen variants of this formula attached to Before
Update. That works, but I would prefer that the user see
the form number when a new form is started. I believe I
can attach something to the On Error event to increment
the number in case there are two users at the same time,
assuming that I can get it to work in the first place.
Also, I have seen variants with Me.txtFormID at the
beginning of the code, and other with Me![txtFormID].
Both seem to work. What's up with that?
Thanks in anticipation of good news, but failing

that,
any

news.
.

.