macro to set primary key

[valley8338]

I am trying to write a macro (I do not have much experience with this at all) that will set my primary key. here is what I need it to do...

format of field will be yymm-##
I need it to populate the 2 digit year and month, then increment the last 2 numbers. I am trying to write the macro in the save and close button. This way when they are done with the form, the primary key will be assigned.

I have the field on the form at the end of the tab order (after the save and close button) and I have it hidden.

If anyone could help me with clear instructions on how I write this (which actions to use/what to put in the item field), it would be truly appreciated.

additional info if needed:
field is ProjectID located on the Project Info table. Thanks!!!

 

[WildHare]

Ok, let's think about this for a minute. Do you want your numbers to start back over at 01 when the month changes? And how are you arriving at what month value to use? This month? The month the project started? Do all new projects 'start' this momth?

See what I'm getting at? It's easy enough to get the YY part, and the MM part, but you need to tell us the logic behind the assignment of the ## part.

At the very least, you'll probably need a table to hold counter/increment values, so the function (oh, yeah, this will probably need to be some code, no macro on earth will do this...) knows how to increment the various components of your key value.

Get back to us, and we'll get a hammerlock on this question.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[valley8338]

The month that the project is requested would be fine, there would be no way to determine what month the project was started when they are requesting, and I need this to be my primary key for lookup fields on my forms.

I would want the ## to start from 01 each new month. The reason for the ## at all is to have a unique number, and maybe down the line track how many projects are requested a month. If you could help me with the code and where to put it, that would be great. I am really rusty at all of this, and then they threw this project at me... I really appreciate all of your help.

 

[WildHare]

Ok, you can do what you want without having to resort to this "key-building" stuff, which I tend to shy away from.

Store the date the project is requested as a standard old date field. You can even default it to DATE() in table design if you want.

Use autonumber to create a unique, sequential number for each project that will be your key.

You can always find out how many projects were requested per month using standard Access Date manipulation functions like MONTH and so on.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[valley8338]

I have to have the projectID in the format of yymm-##. This is not up to me to change.

In searching through old posts, I found something about using a form current event, but I didn't understand the instructions. If soemone could help me with this please...

 

[WildHare]

You can always jerry-up a "Project ID" field for queries and reports by using string concatenation techniques. Only you and I will know that your database is storing a date and a sequential number separately. Don't tell the boss.

I'm serious about this - I can see why people who don't know anything about databases would want a project number like "0802-57", because they can see at a glance that this is the 57th project this month.

Field values should be "atomic" - discrete values that depend on no outside event or occurance. Entry of a value in a record should NOT be based on a chunk of code running somewhere that "calculates" it.

I'm sure others will chime in here with differing viewpoints, and I can respect them. But I shy away from this sort of thing as a general rule.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[valley8338]

Here is the problem that I already ran into by not using the correct projectid.

Originally, I had an autonumber field as my primary key, and the projectid was being entered manually, after the original record was created. One part of the database tracks where projects are (editing, cover design, etc.). I have a form that allows us to enter the project tracking. I have an auto lookup field to enter the information by projectid. It is the only field I can use because any of the otehr fields can be duplicated. I then need to be able to do a report that shows all of the tracking for a project. when I tried to do this with not having the projectid as the primary key, things went haywire. I have to give them ids that we use already, which is the format outlined above.

 

[WildHare]

Hmmm...might it be better to create a one-to-many relationship between a PROJECT and its zero, one or many "STATUS"es, (e.g. Design, Edit, MarkUp, etc), with a Foreign key back to the Project table? I think this might solve your problem a little more cleanly.

I sympathize with your situation, if we really need to do it "their" way, I can probably whip up a small database example for it given a few minutes...

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm