Text and autonumber

[Peter B]

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,

(e-mail address removed)

 

[Rebecca Riordan]

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

--
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.

 

[John W. Vinson]

oOn Fri, 30 Jan 2004 05:11:07 -0800, "Peter B"

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.