autogenerating bundle number

[Jean-Francois Gauthier]

Hello,

In my document tracking database, I would like to have the system assign a
bundle number to an entry based on the department the document relates too
and the document type.

In real life, the user would basically have a pile of documents sorted first
by department and then by document type (ie. claims).

The way the database is being mounted, the user would be presented at
beginning of entry with a form that would ask to select the department and
the document type being entered, and upon hitting the open form button he
would be presented with a new form (3 options being the departments, based on
department choice made in preselection form) The 3 forms are each linked to
individual tables for each departments, which would contain the entries made
on each individual deparments.
If the user wants to change department and/or document type, he would need
to backout of the entry form and go back to the preselection form to reselect
the department and the document type.

In the the second entry form that is opened upon those choice, I have a
field called bundle#, which I would like to have autogenerate (simple
number). This number would be valid throughout the actually entry cycle.
(ie. if the user does 50 entries before backing out of the form, the bundle#
would be the same for all of them.) Basically the bundle number should stay
the same for any documents that are entered for an individual department and
document type, on a given day. Another catch here is that we may want to
limit the size of a bundle on a given document type (ie. claims bundle could
only contain 30 documents (entries).

I'm really not sure as to what is the best way to get this to happen, or how
to do it. If you have any idea, please let me know.

 

[Terry]

Hello JF
I am making the assumption that you are very new to Access; apologies if
this is not the case.
The individual departments should not be in individual tables but in a
single table with a field <Department>. Aggregate the tables into a single
table with the added field.
You need two main tables: <tblBundles> and <tblDocuments> which are linked
on a common field such as <BundleID>
In <tblBundles> you would have something like:
* BundleID - Autonumber - PrimaryKey
* Department
* DocType
* DateReceived - Date - Default (=Date())
* Any other fields you require
To this table add an additional index based on Department, DocType and
DateReceived and make it Unique.

In <tblDocuments> you would have
* DocumentID - Autonumber - Primary Key
* BundleID - Number - long
*Description
* Any other fields you require

In Relationships link BundleID to BundleID

In forms use the wizard to create a Main-Subform based on the two tables.
This can be used to display the data for the users.

The opening form (an unbound form) solicits the Dept, DocType & Date (You
can use the registry to set defaults for Department if each Dept does their
own). If the combination exists the user is given the correct 'page' of the
form which displays the Bundle Information in the main part of the form and
the Documents attached in the subform. If the combination does not exist you
can create the new Bundle and then open the form at the new record.

How are you at coding?

Another option, heavily reliant on coding:
Most of the above but main form has the following fields:
* Department (combo)
* DocType (Combo)
* DateReceived (Defaults to today)
* DocDesctription
Plus two buttons
* Close
* Save
In the code for <Save> you find the Bundle using Dept, DocType & Date
eg lngBundleID = nz(DLookup("BundleID","tblBundles","[Department] = "
&me!Department &" AND [DocType] = " &me!DocType &" AND [DateReceived] = #"
&me!DateReceived &"#"),0)
If the bundle is found a number greater than 0 is returned; if not found 0
is returned, so create the new record for the Bundle.
Add the record for the Document.

Clear the Description and other info relating to the doc and leave the
bundle info as default for the next document.

An additional (nice) touch is to display the last document details in the
footer. This can be used by the operator to check the last entry if s/he is
distracted by, say, a phone call.

This is fairly long already; yell if you need the code or more info
Terry