Setting up an automatic but customized ID

[CodeGunnerLev1]

Good day guys...

I must say this forum is quite of useful one. I really appreciate what
you guys are doing and I hope it will continue on.
I have few questions that needs some help.

1. I want to know how do you create a primary key that increment
itself but with a specified format. Say s3075711 something like that.
I doesn't have to be like that but similar to it. And for distributed
database, is it possible to say, give a certain range of unique
primary key to a person and another range of primary keys to another
person? (Just to not make it overcomplex, just answer the first one..)
2. How do you design Access so that it can be used as a distributed
database? I have a group that is expanding in size (non-profit), and
it require more than one data populator to populate the database.

Thank you again for all your help. And if you can give me a reference
with which I can find the answers to my questions, I would be very
thankful.

Kind Regards
Erwin K

[salad]

CodeGunnerLev1 wrote:

Good day guys...

I must say this forum is quite of useful one. I really appreciate what
you guys are doing and I hope it will continue on.
I have few questions that needs some help.

1. I want to know how do you create a primary key that increment
itself but with a specified format. Say s3075711 something like that.
I doesn't have to be like that but similar to it. And for distributed
database, is it possible to say, give a certain range of unique
primary key to a person and another range of primary keys to another
person? (Just to not make it overcomplex, just answer the first one..)

Is is really necessary to make that field the primary key? You could
use an autonumber (always visible = false) and have another that is your
calculated key.

You would not want to use an autonumber field. Since it uses alphas, it
would need to be text.

I would create a table. Maybe call it TableKeys. In it have a field
called KeyNum. Init the field with some value. When you open the form,
you would 1) Get the current value, 2) store that value to a variable 3)
Increment the number, 4) update the TableKeys field with the new value,
then add whatever additional stuff you need to make it unique (like
adding the S).

Autonumbers, tho sequential in nature, may have breaks in them. This
can be caused by starting to add a record than aborting the add for example.

So you need to consider when you want to create the key. Perhaps when
the record is saved...in the form's BeforeUpdate event. That way you
can keep the record numbers sequential. Or you can create it when you
go to a new record. Then if you abort the add, check the tablekeys
table to see if the number is the same and decrement it.

I think if you want to give keys to others, you should do either one or
the other...sequent ial numbers for all or sequential numbers for users.
You don't want to be doing two sets of code.

You will need a method to get the user name/ID. See
http://www.mvps.org/access and check the API's for getting user name.
Or get the username if you have Access security set on.

If you give sequential numbers to users, you need a method to add a user
and his sequential number set.

Overall, I would use an autonumber field and make this calced key an
indexed field. A key is a key is a key. You can "prettify" the key for
them for presentation but use the autonumber as the real key.

2. How do you design Access so that it can be used as a distributed
database? I have a group that is expanding in size (non-profit), and
it require more than one data populator to populate the database.

What do you mean by "distribute d database"? Are the people on the same
network? Or is the app located at multiple locations but all share the
same data? Are you talking about a replicated database? I don't know
what a data populator is.

>
Thank you again for all your help. And if you can give me a reference
with which I can find the answers to my questions, I would be very
thankful.

The help file is a start.

Next...http://groups.google.com/advanced_search?q=&
In the Groups text box enter *access*
Then enter what words you want to search for.

http://www.mvps.org/access

Some access programmers have taken the time to put together some Access
tips to help. Here's two of them.
http://www.granite.ab.ca/accsmstr.htm
http://allenbrowne.com/tips.html

Kind Regards
Erwin K

[CodeGunnerLev1]

wow... I need time to digest that.. I guess.

I sort of get it first go, but I need to read it again. What I meant
by distributed database is more like the application is located in
multiple locations but all share the same data.

The ID numbers act as an identifier is like a membership number. It
doesn't necessarily need to have s or alpha's. It can be all numbers
only. I was thinking of using a code to generate the number in a form.
Would that work? And what sort of language do I need to know to make
that? :P VB or Macro?

Thank you for your reply.

[salad]

CodeGunnerLev1 wrote:

wow... I need time to digest that.. I guess.

I sort of get it first go, but I need to read it again. What I meant
by distributed database is more like the application is located in
multiple locations but all share the same data.

Do they run separately (iow on a PC not connected to the network or not
on a wan or whatever)? You might have to think about replication then
but I really don't know what your current situation is yet.

Replication would be for something like going out into the field and
collecting data on a laptop and then updating the database with changed
data when you get back to the office. (Look at the mvps.org link and
click on the search button for Replication of that is needed.)

So do you need replication or am I not understanding how the data will
be updated or shared...since they share the same data...whatever same
data is.

The ID numbers act as an identifier is like a membership number. It
doesn't necessarily need to have s or alpha's. It can be all numbers
only. I was thinking of using a code to generate the number in a form.
Would that work? And what sort of language do I need to know to make
that? :P VB or Macro?

Yes, that would work. Macro would be worthless. You need to understand
VBA. Fortunately for you, there's not many commands you'd need to
learn. Left, Right, Mid, Trim, String functions are a few.

>

Thank you for your reply.

[CodeGunnerLev1]

Alright guys, I gotta be honest with you...

I've only been using Java and C++. And I am not quite confident of
using VBA on Access. Where can I find references with which I can
study.
In regards to the distributed database, yes I had replication in mind,
but there is a problem of primary key. If I could somehow make it work
(the primary key) how do I set the database so that, when - say two
database is joined - the primary keys wont be duplicated? One way of
doing it is to take some of the idea given by salad, that is making a
table to somehow control the designation of primary keys, and then
distribute it to different people. What do you guys think about this?

Thank you.

[salad]

CodeGunnerLev1 wrote:

Alright guys, I gotta be honest with you...

I've only been using Java and C++. And I am not quite confident of
using VBA on Access. Where can I find references with which I can
study.

Irrelevent. If you can program in Java and C++ then you should have a
clue about event programming. And even the most basic programmer can do
string manipulation.

In regards to the distributed database, yes I had replication in mind,
but there is a problem of primary key. If I could somehow make it work
(the primary key) how do I set the database so that, when - say two
database is joined - the primary keys wont be duplicated? One way of
doing it is to take some of the idea given by salad, that is making a
table to somehow control the designation of primary keys, and then
distribute it to different people. What do you guys think about this?

Thank you.

I don't want to be an asshole but I doubt you've taken any of my advice
and gone to any of the links I provided you. I think you are expecting
someone to come down from heaven and give you all the answers as you
lounge on a couch eating grapes. You really should do some research.
Do not expect others to do the work for you. When you do your research
and read the white papers on replication you can pose questions that
will help get you over the hump.

I have not done replication. It may be best you start another thread
asking for pointers in replication...a fter doing some research.

Michka Kaplan either wrote, designed, or had significant input at MS on
replication. He was a poster in this newsgroup for years. I suggest
you go to http://www.mvps.org/access and search on replication and get
his software tool.

Next, get to google groups and follow my instructions on how to search
for messages. Look for message on replication and string manipulation.

Next, open up Access, go to help, and enter Replication. Study the topics.

Next, go to http://support.microsoft.com and look for topics on
replication for Access in the help file.

You might want to go to a bookstore and see if they have any books on
Access that discuss replication. Best bet is the Access Develpers
Handbook by Getz.

Good luck.

[CodeGunnerLev1]

Wow mate I guess you're right.

I probably did expect something to come from heaven and help me out.
Its probably just my lack of confidence on these sort of stuff...
Anyways, I will follow your suggestions, and questions no more. Unless
of course I have a new problem unsolved...

And Salad, I very much thank you for your advice and pointers. Forgive
me for taking too much of your time and effort.

[salad]

CodeGunnerLev1 wrote:

Wow mate I guess you're right.

I probably did expect something to come from heaven and help me out.
Its probably just my lack of confidence on these sort of stuff...
Anyways, I will follow your suggestions, and questions no more. Unless
of course I have a new problem unsolved...

And Salad, I very much thank you for your advice and pointers. Forgive
me for taking too much of your time and effort.

No problem on time and effort.

Replication is what I would consider an advanced topic. My reasoning:
the majority of Access programmers have never done it. That is one
reason I suggest starting another thread using the word Replication in
the subject line...if that is what you need/want to do in your project.
Those that have done replication may respond. Besides, most look at
new/recent message threads and those after a day or two quickly get ignored.

BTW, in my brief perusings on replication, it appears that autonumber
keys normally are sequential but in replication are random.

A couple of more links.
http://support.microsoft.com/search/...2&mode=a&adv=1
http://support.microsoft.com/kb/190766/en-us

Good luck. May your project be interesting.

>