More on Dmax?

[solbane]

Hi there,

I've looked through the archives and I am still struggling. So sorry if this is a re-post.

I am having trouble getting dmax command to work correctly.

I currently have a Form called
Input table
and a Table called
Store

On my input form is where users will enter their required fields... e.g.
Field 1
Field 2
Field 3
Field 4
Field 5
before hitting a button that allows the transfer of information to the Stores table.

What I do not want them to see is a unique ID number generated using dmax until such time that they have clicked the button and the record is stored.
This should be in "SUN0000000001" +1 increments

I have currently assigned the following to the button:

DoCmd.GoToRecord , , acNewRec
DoCmd.OpenTable "Store", acViewNormal, acReadOnly
DoCmd.GoToRecord acTable, "Store", acLast
DoCmd.RunMacro "Macro1", 1

Some of that is irrelevant but it gives you the idea. So once they have filled in the information required they click the button and it adds the record and at the same time generates the unique number.

But now... every time I try add code to allow the dmax command to create a SUN0000000001 +1 number it swears at me.
I'm no big VBA fundie so if you could help me out here I'd appreciate it.
I hear that adding year and custom code allows for more unique number but all I am worried about for now is getting it working be it with or without dates.

Muchly appreciated
Cheers

solbane

 

[boblarson]

Do NOT put the numeric and the text part together. Store your number as a separate numeric field and increment as needed. Just display it with the SUN part on it and format the number. Don't worry about storing the extra zeroes.

 

[solbane]

It's a bit messy that way
I'm still coming to terms with VBA but there HAS to be a way around it yes? What happens when you want to import/export the data to or from somewhere and you need the prefix to be present (Other than manual ctrl+c & ctrl+v work that is)?

Is there no possible way to insert a text string in the row and allow +1 increments to the numbers themselves other than creating a separate field for each for eg. linking it to a table with a value in e.g.
"[xyz]" [dmax_number] +1
Or would it at all be possible to combine the seperate fields as one at a later stage?
e.g.
Row1=[string1]&[string2] (after dmax has done the job that is)

For now I've just used number as long integer and used =DMax("[UniqueNo]","Store")+1 and done away with the "S.U.N." prefix which was not the intended idea

Sorry to persist. Again, I'm new to VBA and although I am finding this facinating I'm beginning to feel it's really limiting at the same time.

 

[boblarson]

It's a bit messy that way

Messy? Messy? No, the way you're trying to do it is messy. The CORRECT way is to use a field for the number because it is a NUMBER (not text) and then increment that. If you want to concatenate something else in there to view that is your perogative. But, you should not be concatenating things to make a primary key. Just use a unique number for that. In fact, I would go so far as to say use an autonumber for the primary key and then you can also create your DMax + 1 to display to people.

Building your own primary keys is a messy business and can cause you more pain than it is worth. There are very rare natural primary keys that one can use without having a potential issue arising. Even social security numbers are not completely unique. They do get recycled.

Or would it at all be possible to combine the seperate fields as one at a later stage?

Yes, you can do this - But FOR DISPLAY ONLY - not in the tables.