Custom Autonumber

[Kari]

Is there a way to custom autonumber that updates based on two tables?
I'm creating a database for school improvement projects and would like
to increment the project# by one based on the sitenumber and current
year. We have 84 sites.

Example: 136-01-04, 136-02-04, 201-01-04-, 201-02-04, etc. I have a
site table with sitenumber (pk), sitename, and project table with
project# and projectyear. I would like to open the project form and be
able to pick out a school site or number and get the new project
number.

What I'm doing now is going to Excel to get my numbers I manually
typed in, then go to Access and retype what I had typed in Excel! Any
help would be appreciated. Thanks

 

[Sandra Daigle]

Hi Kari,

You really need to break this information into three separate fields
(sitenumber, projectyear and projectNum). This is an important part of a
normalized database. You can create a calculated field in queries if you
need to show all three together. That said, the only counter or sequence
field you have is ProjectNum. Given the SiteNumber and Year, you can use the
Dmax function to return the current highest projectnum for the
SiteNumber/Year combination, then simply increment that number by one. All
of this can go into the beforeUpdate event for the Project:

Aircode -- may have typos

dim strCriteria as string
strCriteria="SiteNumber=" & me.SiteNumber & " And ProjectYear=" & year(Date)

me.ProjectNum=nz(dmax("ProjectNum","tblProjects",strCriteria),0)+1

This is not the best solution if you have multiple users who are likely to
be entering projects simultaneously but it works well otherwise.

 

[Kari]

Hi Sandra,

Thanks for your reply and code. I've been working on the code, but I'm
thinking as you stated previously, since I need to show all three
together, I will need to do a calculated query; not quite sure how do
that though. If I do a query, will I still need to an beforeupdate
event? Thanks for your help, I'm a novice at Access.

 

[Sandra Daigle]

Hi Sandra,

Thanks for your reply and code. I've been working on the code, but I'm
thinking as you stated previously, since I need to show all three
together, I will need to do a calculated query; not quite sure how do
that though. If I do a query, will I still need to an beforeupdate
event? Thanks for your help, I'm a novice at Access.

Yes the BeforeUpdate event is where you initally get the value for the
projectNum. One correction to the code I gave you earlier, you need to
branch around this code if the record is not a new record since you don't
need to assign a new projectNum to an existing record.

dim strCriteria as string

if me.newrecord then
strCriteria="SiteNumber=" & me.SiteNumber & " And ProjectYear=" &
year(ProjectDate)
me.ProjectNum=nz(dmax("ProjectNum","tblProjects",strCriteria),0)+1
endif

To put the 3 values back together in a the query designer for your query
type the following into the Field of an empty column:

txtProject: SiteNumber & "-" & ProjectNum & "-" & year(Projectdate)

txtProject is the name of the newly created calculated field. You can put
this field (calculation and all) into any query.