Sequential Number

[chopper57 via AccessMonster.com]

What I would like to do is auto add 1 to my report numbers, not sure if it's
possible the way I have things set up. I have a database for construction
projects, a table that list Projects and another for daily reports.

Presently I have a Main Form for selecting a project from a drop down list
box and a Sub Form for Daily Reports. Each Project has it's own Daily
Reports with Sequential Number entered manually.

If I use the =DMax("[ReportNo]","qryDailyReport")+1 it totals all reports
from all projects and adds one

Presently there are 431 reports for all Projects, each project has different
number of reports due to when they started.

Is there a way to accomplish a Sequential Number for each Projects Reports.

 

[Arvin Meyer [MVP]]

Try:

DMax("[ReportNo]", "qryDailyReport", "[ProjectNo]= " & Me!cboProjectNo)+1

cboProjectNo is the project number selected from your dropdown on the form.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Keith Wilby]

What I would like to do is auto add 1 to my report numbers, not sure if
it's
possible the way I have things set up. I have a database for construction
projects, a table that list Projects and another for daily reports.

Presently I have a Main Form for selecting a project from a drop down list
box and a Sub Form for Daily Reports. Each Project has it's own Daily
Reports with Sequential Number entered manually.

If I use the =DMax("[ReportNo]","qryDailyReport")+1 it totals all reports
from all projects and adds one

Presently there are 431 reports for all Projects, each project has
different
number of reports due to when they started.

Is there a way to accomplish a Sequential Number for each Projects
Reports.

Yes, one way would be to have qryDailyReport's "project" field reference the
projects combo box and use DCount instead of DMax.

HTH - Keith.
www.keithwilby.com

 

[chopper57 via AccessMonster.com]

Arvin, I got your suggestion to work with:

=DMax("[ReportNo]","qryDailyReport","[ProjectNo]=" & "Forms![frm_MainForm]!
[Combo1]")+1

With the formula above, the last is 0112, and the next is 113, would like it
to be 0113.

It works good, but I would also like to have a four digit number and the Nz
for when a new Project is added.
I tried the Nz, but when I click for a new record I get a number 1 and not
the a sequence number, what I mean is that the last number is 0112, the next
should be 0113, but I get 1.

Thanks for your help.

Try:

DMax("[ReportNo]", "qryDailyReport", "[ProjectNo]= " & Me!cboProjectNo)+1

cboProjectNo is the project number selected from your dropdown on the form.

What I would like to do is auto add 1 to my report numbers, not sure if
it's

[quoted text clipped - 14 lines]

Is there a way to accomplish a Sequential Number for each Projects
Reports.

 

[BruceM]

Would DCount create a problem if a previous record is deleted? Unless I am
missing something that would create a duplicate value, or if several records
are deleted it could result in a new record being assigned the same number
as a deleted record (for instance, if the first and the second-to-last of
ten records are deleted, there are eight records, in which case the new
record would be numbered 9, the same as the just-deleted second-to-last
record).

What I would like to do is auto add 1 to my report numbers, not sure if
it's
possible the way I have things set up. I have a database for
construction
projects, a table that list Projects and another for daily reports.

Presently I have a Main Form for selecting a project from a drop down
list
box and a Sub Form for Daily Reports. Each Project has it's own Daily
Reports with Sequential Number entered manually.

If I use the =DMax("[ReportNo]","qryDailyReport")+1 it totals all reports
from all projects and adds one

Presently there are 431 reports for all Projects, each project has
different
number of reports due to when they started.

Is there a way to accomplish a Sequential Number for each Projects
Reports.

Yes, one way would be to have qryDailyReport's "project" field reference
the projects combo box and use DCount instead of DMax.

HTH - Keith.
www.keithwilby.com

 

[Keith Wilby]

Would DCount create a problem if a previous record is deleted? Unless I
am missing something that would create a duplicate value, or if several
records are deleted it could result in a new record being assigned the
same number as a deleted record (for instance, if the first and the
second-to-last of ten records are deleted, there are eight records, in
which case the new record would be numbered 9, the same as the
just-deleted second-to-last record).

Hi Bruce.

You're right of course but deleted records is a whole other topic for me, I
never (in my line of work) allow deletions but I do provide a "deleted"
flag. The reason I suggested DCount was so that, if you started the project
numbering uniquely across all projects and then switched to the new system,
then it would still work ... or so I thought, but it's since occurred to me
that it won't because, for example you could have 4 records across 4
projects, the records numbered 1 to 4. If you then went to the unique
number per project system you could get, for example, a duplicate number 2.

Confused? I know I am, either way DCount was a bum steer, apologies to the
OP.

Regards,
Keith.

 

[BruceM]

Thanks for replying. It adds another piece to my understanding of Access.

 

[Arvin Meyer [MVP]]

Wrap it with the Format function:

=Format(DMax("[ReportNo]","qryDailyReport","[ProjectNo]=" &
"Forms![frm_MainForm]![Combo1]")+1, "0000")
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Arvin, I got your suggestion to work with:

=DMax("[ReportNo]","qryDailyReport","[ProjectNo]=" &
"Forms![frm_MainForm]!
[Combo1]")+1

With the formula above, the last is 0112, and the next is 113, would like
it
to be 0113.

It works good, but I would also like to have a four digit number and the
Nz
for when a new Project is added.
I tried the Nz, but when I click for a new record I get a number 1 and not
the a sequence number, what I mean is that the last number is 0112, the
next
should be 0113, but I get 1.

Thanks for your help.

Try:

DMax("[ReportNo]", "qryDailyReport", "[ProjectNo]= " & Me!cboProjectNo)+1

cboProjectNo is the project number selected from your dropdown on the
form.

What I would like to do is auto add 1 to my report numbers, not sure if
it's

[quoted text clipped - 14 lines]

Is there a way to accomplish a Sequential Number for each Projects
Reports.

 

[chopper57 via AccessMonster.com]

Arvin, thanks again for your help, I got it to work after I deleted the
Validation Rule. Also got the Nz to work , now when I add a new Project it
starts at 0001. Thanks again for your help.

Wrap it with the Format function:

=Format(DMax("[ReportNo]","qryDailyReport","[ProjectNo]=" &
"Forms![frm_MainForm]![Combo1]")+1, "0000")

Arvin, I got your suggestion to work with:

[quoted text clipped - 27 lines]