Trying to auto generate an id based on company number 2

[evr72]

Hello,

I have an employee table that has 5 fields companyname, empid, empname, emplastname, notes
and another table called companies it has companyname and companynum
I am trying to create a form when the user enters companyname, this will autogenerate an empid depending on the companynum.
for example company abc has companynum 123, so when I enter
company employee id employee name employee last name notes
abc 1230001 Joe Smith **

the empid 123001 will get created.

I am a novice in Access forms any help is much appreciated.

Thank you!!

 

[TheAceMan1]

How are ya evr72 . . .

Before I get into this ..... be sure to critique the following link: Fundamentals of Relational Database Design

Getting on with your request ... from a table design view, it would be better if you included companynum instead of companyname. This would make companynum directly available and incite a usable form/subform conbination quite readily (companies table as parent main form with companynum as primarykey and employee table as child subform ... with companynum as foreignkey).

I've setup my own secnario for this and its proven true ..... it usee DMax+1 to set the next empID.

Are you intrested? ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[evr72]

Yes, that sounds like the exact thing I want to do.

 

[TheAceMan1]

evr72 . . .

I need the actual names of the tables and fields, if you please ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[evr72]

Sure,thanks

I have two tables "employee" and "BusinessUnit"

employee table
ID autonumber
EmployeeID this is the one that needs to be Businessid + 000 +1
MiddleName
Suffix
EMailAddress
BusinessUnitName
BusinessUnitID
Notes

BusinessUnit Table
ID Autonumber
BusinessUnitName
BusinessUnitID

 

[Golom]

Hmmmm ... You want an employee number composed of a BusinessID concatenated to a number for the employee but I don't see a BusinessID field anywhere in your tables. I do see an autonumber field called ID in the BusinessUnit table. Is that the BusinessID?

It is usually considered unwise to build field values that have some internal structure and particularly if you intend to parse out part of that structure to derive some meaning from it. You may be better off to include BusinessID as a separate field in the employee table and make the combination of the BusinessID and Employee Number fields the Primary Key.

In a related issue, the employee number is just an identifier in the sense that it's only meaning is that no other employee has that number. If you assign new employee numbers sequentially, regardless of business, then you never run into the problem of transferring Joe from one Business to another and finding that he now needs a new number because his old one with the new BusinessID is already in use. If Joe gets a new number then all information about Joe that may exist in other tables becomes inaccessible because it references his old number ... not the new one.

I also note that BusinessUnitName and BusinessUnitID appear in both tables but neither of those are attributes of the entity "employee". The basic rule in relational databases is that a field should be an attribute of the key, the whole key and nothing but the key.

 

[TheAceMan1]

evr72 . . .

None of the table fields you described in your post origination are players in your post 4 Oct 12 14:20. It appears (I could be wrong), you are making this up as you go along and the good reason why Golom has already been thrown. I would love to help ... but what I've described is not a cool thing ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

... Golom has already been thrown.

Golom must plead guilty to "making it up as he goes along" from time to time. He also must ruefully admit that he has paid dearly for that sin. At this design stage it is a simple matter to change your mind. After you have enshrined your decisions in table structures and code, revisions become the work of days and weeks rather than moments.

Heed the advice of PHV and TheAceMan1 (both of whom are more expert than I) to avoid the pitfalls that await you.

 

[TheAceMan1]

evr72 . . .

I uploaded my example to 4Shared. You can download with the following link. The names are a little different but it does exactly what you want. Note: The new ID is generated via the On Dirty event of the subform. If you need any explanations, let me know.

AutoGenID.mdb (2003 format)

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lionelhill]

I wouldn't even use a combination of the two fields as a primary key in this instance. I'd let each employee have an autonumber ID, and each company have an autonumberID, and I'd simply concatenate them anywhere where I was asked to display the "employee-number" on a form, probably with a divider-symbol. This simulates a number with the structure you want, while the database still identifies each employee by a single number. The only "downside" is that a new company (123) will see its employees starting at employee number 123-00067145 rather than 123-00000001, because other numbers are already taken.

Incidentally, I don't like generating primary keys by the max+1 method, because if two people do it at the same time, it's conceivable they'll both get the same number, and they'll try to create two entries with the same key, one getting an error. If I feel obliged to do this (through fear that one day Microsoft's Autonumbers will stop being incremental and go all random), I wrap the max+1 in an "on error" that repeats the process a couple of times before showing the user an error.

 

[TheAceMan1]

How are ya lionelhill . . .

I agree with you and Golom. I was just showing the op it could be done their way.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[evr72]

TheAceMan1,

sorry it has taken me a while to reply, been a bit under the weather.
thank you very much for the example. I appreciate all the input.

When I downloaded the db you created and I kind of understand the code (very little) I get a bit lost if I keep the same fields in the TblEmployee, and add a couple of more fields like email and country the frmCompany does not work. I only see that you are referencing the empID, the CoID and the TblEmployee in the vb script is that correct?

 

[TheAceMan1]

evr72 . . .

Yes thats correct ... with some conversions between numeric & string values.

For you, CoID is BusinessUnitID and empID is EmployeeID (you never gave the actual table names). Note that a relationship exist between CoID of both tables. So if you change them to BusinessUnitID, you'll have to redo the subforms master/link properties with the proper names. I was initially going to correct the tables, however it was just faster to make my own names for the example.

Your Thoughts! . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[evr72]

TheAceMan1,
But even if I don't change the names in any of the tables. When I add an extra field to the TblEmployee the form stops working even if I recreate both forms, it does not seem to like the extra field that I added.

 

[TheAceMan1]

evr72 . . .

The db is in 2003 format and I have no problems adding fields. What version you using?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[evr72]

I am using 2007

 

[TheAceMan1]

evr72 . . .

I don't have 2007 ... so if there's a way to convert to 2007 from your end, give it a try. This may be all thats needed.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]