Defining Primary Key (Vendor ID & Product ID) as Current Date

[mspeppa19]

I am currently creating an access database that will be used for vendor tracking. I have a vendors table with the primary key as vendor_id. What I wanted to know was whether there is a way for me to define the vendor ID some sort of autonumber, but instead of it just being an incremental number it is the current date plus an incremental number. For example:

First vendor of the day...
Vendor ID: 0126041

Second vendor of the day...
Vendor ID: 0126042

01 = January
26 = today's date
04 = year 2004

and the following number is the incremented number. Any help/advice would be greatly appreciated. Thanks.

 

[cmmrfrds]

You could, but it might be easier to just use the hour and minutes along with the date for you key.

yourKey = Format(Now(),"mmddyyhhnn&quot

 

[mspeppa19]

i like that idea!! Also, how will this information be saved? I remember i used the now() function and whenever i reopened a record the ID would be updated due to the now() time being different then before.

 

[mspeppa19]

also, where would i put the Format(Now(),"mmddyyhhnn&quot

would it be in my Table: Vendors or within the actual Form: Vendors

and would it be a default value or format??

 

[cmmrfrds]

It can work in either place. It could be the default value in the table, or could be done in the before update event of the Form. Probably the simplest is the default value in the table.

 

[mspeppa19]

so how would I make sure that once i enter a new vendor, that it's vendor ID will not change if i decide to go in and either view the vendor information via the entry form, or if i decide to edit the information?

For example if the vendor id is:
0126041002
because i added the vendor at 10:02

will it change lets say to:
0128041125

if i decide to view or edit the information on the 28th of january??

how can i get the ID to stay its original value?

 

[mspeppa19]

also, my last question concerning this matter...

is there any way to have the default value be a set of characters and then the date. For example


Default Value: "ABC"=Format(Now(),"ddyyhhnn&quot

so that the ID would possibly come out...
ABC0126041010

Not really sure if that's possible because i am not sure of the formatting if it is. Thanks

 

[cmmrfrds]

The default value for a field in a table only gets assigned when the record is inserted. The update of the record unless you specifically change the field will have no effect. I am not sure on concatenating the 2 fields as the default, but my guess is that it should be okay.