Incrementing a compound key

[Linderick]

I am creating a simple DB to manage corresponence. Each letter or fax will need a unique ID. The UID will consist of a recipient code and a sequence numer. EG

DTI001
DTI002
HSE001
HSE002
HSE003

What is the preffered method for generating this UID automaticaly?

Thanks
Lin

 

[IMO]

Set the AutoNumber format to "DTI"000 or "HSE"000 in the tables

IMO

 

[Linderick]

Thanks for the reply. I should have explained that I only have one table so that the prefix for the autonumber would have to be driven by the value of [addresse] in the table.

Ummm

 

[IMO]

Is this the sort of thing you are looking for?

IMO

 

[Mark Wild]

Hi Pat,

A couple of questions of you dont't mind concerning your response to Linderick.

In his example (and my case) the counter (ie autonumber) is needed to run incrementally for each product group, which are all stored in one table. For example

SWAP001
SWAP002
LOAN001
LOAN002

Am i correct in saying that this could not be done using an autonumber?

In this case, how would one successfully number them? At the moment I am trying out some case statements that look at the product the user selects from a combo box, then the alpha part of the string and then the numeric, to which I add one. My problem is in ideintifying the last used numeric for the selected string. Any suggestions?

Imo, apologies if your sample covers this but I'm in 97 so can't open it.

Linderick, did you manage to resolve this?

Thanks and regards,

M

 

[FoFa]

Truthfully I would just write a VBA function to get the current MAX value for a string (IE DTI max in your example is 002). Increment this value and store the new row. Next call to the function would return 003 (if incrementing by 1). Or as another method not frequently used in this type of scenario is to get the count for the prefix (IE count for DTI is 2) add onr to that and insert new row. That way if one does not exist it returns zero and you can add one to that and you need no special code to check for none existant values. Just pass the UID to the function as a parameter for it to check.
Just my 2 cents worth.

 

[IMO]

Imo, apologies if your sample covers this but I'm in 97 so can't open it.

Sorry about that, here's the 97 version

IMO

 

[Mark Wild]

THanks to both of you. I'm just trying to implement it now.

 

[Linderick]

Hi Pat,

A couple of questions of you dont't mind concerning your response to Linderick.

In his example (and my case) the counter (ie autonumber) is needed to run incrementally for each product group, which are all stored in one table. For example

SWAP001
SWAP002
LOAN001
LOAN002

Am i correct in saying that this could not be done using an autonumber?

In this case, how would one successfully number them? At the moment I am trying out some case statements that look at the product the user selects from a combo box, then the alpha part of the string and then the numeric, to which I add one. My problem is in ideintifying the last used numeric for the selected string. Any suggestions?

Imo, apologies if your sample covers this but I'm in 97 so can't open it.

Linderick, did you manage to resolve this?

Thanks and regards,

M

Yes I have now resolved this problem, my key is a compmound key based on a txt field and a numeric field. When adding a new record the user supplies a value for the txt field. The user then double clicks on the numeric filed whereupon a query is triggered that retrives the currnet max value of the number based upon the user supplied text . This max value plus one is then used to populate the new numeric field. The query is run against the table that you are adding the records to. No VBA needed ! I like start off with the premise that VBA is only needed if your tables are normalised correctly. Good luck

 

[Linderick]

Yes I have now resolved this problem, my key is a compound key based on a txt field and a numeric field. When adding a new record the user supplies a value for the txt field. The user then double clicks on the numeric filed whereupon a query is triggered that retrieves the currnet max value of the number based upon the user supplied text . This max value plus one is then used to populate the new numeric field. The query is run against the table that you are adding the records to. No VBA needed ! I like start off with the premise that VBA is only needed if your tables are not normalised correctly. Good luck