Sequential numbering based on information in a combo field

[stacy505]

Creating a form agianst a table with the following fields
EC
Project
Originator
Title
Full description
Effectivity
Etc....

Easy enough to create the Project combo box, problem comes with trying to systematically assign the next EC number.

When I select a Project from a combo box I want an EC assigned with format ECXXXX-### where XXXX is the project number from the combo box and ### is a sequential number. ECs should start at -001 for all projects.

Do I need to add an EC suffix field to hold just the ###? Then what?

 

[stacy505]

Ok I think I figured this out.
I added a filed suffix and now after I select the project combobox i do
Private Sub p_AfterUpdate()
If IsNull(Me!) Then
Me![suffix] = Format(Nz(DMax("[suffix]", "[Table4]", "[project]='" & p & "'"), 0) + 1, "00000")
End If
Me! = Format([suffix], "00000")
End Sub

Then I concantenate the "EC" & Project & "-" & suffix into a field called EC
The problem with this is even thought the format of suffix is a 4 digit number when I concantenate them together it drops teh leading 0. Any ideas?

 

[MarkK]

I would never construct a code like that as an ID field for a row. You might want to google--or search this site--for "meaningless key" or "meaningful vs meaningless key."
Maybe construct some fancy code like that at retrieval time, AFTER everything is entered, validated, and squared away, but it's just a liability to have to do that at input time. In Access use an Autonumber Long Integer as a key.

 

[Galaxiom]

I would never construct a code like that as an ID field for a row..

I also would not use the EC field as a primary key. Long strings make cumbersome keys.

Using a separate field to store the suffix is a good practice. Use a numeric field to store it. The leading zeros are just formatting.

Do not include the EC field in the table at all since it can be generated on demand by concatenating the project and suffix fields. Don't store the "EC" part of the expression either since this can be added in the concatenation.

Autonumber could be used as the PK but I think there is a reasonable case here to use a composite natural PK on Project and Suffix. This means that every related table would have these two fields too. The advantage of this is that the related tables can be queried on either of those fields without joining the main table.

Either way, a compound unique index on those two fields should be included on the main table to ensure that duplicates are impossible.

 

[jdraw]

stacy505,

I recommend you read this material re: Database Principles.

 

[stacy505]

Ok I have read and given thought to what you all recommend.
If I make the following changes
Add Record ID as autonumber PK
Keep Project Field
Keep Suffix Field
Remove EC# since it can be concantenated from project field

Index the table so Project and Suffix are unique

I still have the same question.
How do I assign the next suffix number in the form based on which project is selected? For example I could have

RecordID Project Suffix
1 2222 001
2 2222 002
3 2228 001
4 2222 003
In the form if I enter Project 2222 I would expect suffix 004, but if I select 2228 I would expect 002

I would like the suffix to be assign before update and a message box to display with the EC that was assigned (concantenated field "EC"&[Project]&"-"&[Suffix])

 

[Galaxiom]

Put something like this in the AfterUpdate of the the Project control on the form to assign it immediately after the projectID is entered. Or in the BeforeUpdate of the form:

If Me.NewRecord Then Me.suffixcontrolname = Nz(DMax("[suffix]","[tablename]","Project=" & Me.projectcontrolname)) + 1

Put the concatenation of the COntrolSource of the text box where you want to display it on the form.

I am sure you can manage the MessageBox requirement.