Prev: Question about finding specific record on a Form linked to a table
Next: Trouble with Me.Undo in microsoft split form
From: Clarkey on 11 Dec 2009 00:48
Hi there,

Needing some help with trying to generate and display an incrementing auto
number on a form. I have scoured some of the forums but haven't had any luck
with getting such a simple thing to work.

I have a student database which I am trying to create a unique student ID
number based on a set prefix each time I go to add a new student. For example:

Student ID - ABC 09 1

Where ABC = set prefix
09 = the current year
1 = would represent the autonumber (primary key)

The only luck I have been having with getting this to work is entering the
following syntax in the field's Default Value, however the ID number wont
appear.

="ABC" & Format(Date,"yy") & [TableName]![ID]

I know there are probably several different ways on doing this but not
having much programming experience I have managed to get this far and its
working to some degree. Obviously there is something wrong with picking up
the ID (primary key).

If anyone could shine some light on it for me that would be appreciated.

Cheers
From: Stefan Hoffmann on 11 Dec 2009 03:37
hi Clarkey,

On 11.12.2009 06:48, Clarkey wrote:
> Student ID - ABC 09 1
>
> Where ABC = set prefix
> 09 = the current year
> 1 = would represent the autonumber (primary key)
>
> The only luck I have been having with getting this to work is entering the
> following syntax in the field's Default Value, however the ID number wont
> appear.
>
> ="ABC"& Format(Date,"yy")& [TableName]![ID]
You shouldn't store this kind of ID as it is always constructable.

The autonumber ID is alread stored.

The current year must be correctly defined, but I assume you are using
the year of the creation of the student's entry, thus store this
date/time value. And the set prefix should either be stored directly in
the student's records table or may be normalized using DKNF, but be
aware of the update anomaly.


mfG
--> stefan <--
From: Mike Painter on 11 Dec 2009 14:29
Clarkey wrote:
>
> The only luck I have been having with getting this to work is
> entering the following syntax in the field's Default Value, however
> the ID number wont appear.
>
> ="ABC" & Format(Date,"yy") & [TableName]![ID]
>
The best way is to use a query for this. That way the query can be use in
the form and reports and you will not have to recreate it .
Your table should inclide an admission date which may default to Date()
Your Query would then contain a calculated field
StudentID: ="ABC " & Format(AdmissionDate,"yy") &" " & [TableName]![ID]
Note the space after ABC to give ABC 09 1

If the ABS is always the same there is no need for it in the field and you
could just as well put it in a label.
Chances are you will be using a last name to lookup student in any event.
If you wish to do it by the "Student ID" IGNORE the first part and just
build a combo box on the auto number, it is all that is needed.


 | 
Pages: 1
Prev: Question about finding specific record on a Form linked to a table
Next: Trouble with Me.Undo in microsoft split form