Text and autonumber

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter B - 30 Jan 2004 13:11 GMT

I am quite new to Access and I cant figure out how I can have a field in at table that have at short text and then a autonumber. I would use this for a primary key and the field should have the two first letter og the surname, then a "-" and a 4 digit autonumber at the end. So for the first person with a surname Smith would have Id number SM-0001. Is there any way I can make a field that automatically fills in this then I enter the Surname field?

And I also have another question for OLE objects. I would like the "frame" in the forms "view" to adjust automatically to the size of the picture I insert in the OLE field. Is this possible or should I just make the frame very big so i am sure that the picture fits inside the frame.

Regards,

Peter.bekkestad@energipartner.no.removethis

Reply to this Message

Rebecca Riordan - 30 Jan 2004 16:51 GMT

You can't do it in the table itself, but that's okay, you _shouldn't_ do it
in the table itself. Instead, build a query with a calculated field:

Left(<surname>, 2) & "-" & [<autonumber>]

where you replace <surname> and <autonumber> with the appropriate field
names. Use the query as the basis of your forms and reports.

HTH

Signature

Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

> I am quite new to Access and I cant figure out how I can have a field in at table that have at short text and then a autonumber. I would use this for

a primary key and the field should have the two first letter og the surname,
then a "-" and a 4 digit autonumber at the end. So for the first person with
a surname Smith would have Id number SM-0001. Is there any way I can make a
field that automatically fills in this then I enter the Surname field?

> And I also have another question for OLE objects. I would like the "frame" in the forms "view" to adjust automatically to the size of the picture I

insert in the OLE field. Is this possible or should I just make the frame
very big so i am sure that the picture fits inside the frame.

> Regards,
>
> Peter.bekkestad@energipartner.no.removethis

Reply to this Message

John W. Vinson - 30 Jan 2004 17:35 GMT

oOn Fri, 30 Jan 2004 05:11:07 -0800, "Peter B"
<anonymous@discussions.microsoft.com> wrote:

>I am quite new to Access and I cant figure out how I can have a field in at table that have at short text and then a autonumber. I would use this for a primary key and the field should have the two first letter og the surname, then a "-" and a 4 digit autonumber at the end. So for the first person with a surname Smith would have Id number SM-0001. Is there any way I can make a field that automatically fills in this then I enter the Surname field?

This is called an "Intelligent Key" - and it is NOT considered a good
idea. Storing two pieces of information in a single field is bad
design; storing one piece of information redundantly is just as bad;
if the autonumber is already unique then adding initials to it doesn't
make it any "uniquer"; storing variable data (people *can* change
their names) as part of a primary key is certain to cause trouble; and
on and on...

An Autonumber is probably not a good idea in this case anyway.
Autonumbers have ONLY one purpose - to create a unique ID. They'll
have gaps, and can become random.

I'd suggest either just using a concealed Autonumber, or writing VBA
code to construct a "Custom Counter", and to leave the initials out of
the primary key altogether.

>And I also have another question foor OLE objects. I would like the "frame" in the forms "view" to adjust automatically to the size of the picture I insert in the OLE field. Is this possible or should I just make the frame very big so i am sure that the picture fits inside the frame.

Sorry, can't help you there - I'd suggest you repost this as a
separate question.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message