Create Unique Key and Add +1

[Kevin_S]

Hi All - thanks for taking an interest!

I have a unique key that I am using as the backbone to a db I'm working on. It is composed of 3 different pieces that make a unique idea (I wanted to go with Autonumber but this is the code they have used in the past so we are sticking with it) The ID looks like this:

ALG03001

where there are 3 parts:

(1) - the location - 3 capital letters: ALG
(2) - the fiscal year - 2 digits: 03
(3) - a sequential number - 3 digit: 001, 002, 003, etc...

I have a form set up so the user can build a new ID through combo box selection so.... I have the location and fiscal year combo boxes all set and in code I add the two together (like ALG03 ) But what I need to do is get the next sequential number so that the key is unique. I have been playing around with the DMax function but I cannot get it to work. This what I have on the button click to submit the ID so far:

Private Sub cmdSubmitWIP_Click()
On Error GoTo err_desc_click

Dim strUnitYear As String

If IsNull(Me.cmboUnitCodes) Or IsNull(Me.cmboYear) Then
MsgBox "Incomplete Submission ID", vbOKOnly, "Add New Submission ID Cancelled"
ElseIf MsgBox("Confirm add new Submission ID", vbOKCancel, "Confirm Add New WIP") = vbOK Then

strUnitYear = Me.cmboUnitCodes & Me.cmboYear
DMax("[SubmissionID]","dbo_WIP_Main", "[SubmissionID]= 'strUnitYear'")

Else
Me.cmboUnitCodes = Null
Me.cmboYear = Null
Me.cmboUnitCodes.SetFocus
End If

err_desc_exit:
Exit Sub

err_desc_click:
MsgBox Err.Number & Err.Description
Resume err_desc_exit
End Sub

But the DMax function wont work...? Basically, I need to look at all of the IDs in the db that match the unit (ALG) and the Fiscal Year(03) and then go one higher than the previous entry and have it in the correct format (000). Am I attempting this wrong? should I use something else like a recordset? I have been starting at this for so long I am really confusing myself

Any all help is appreciated!
Kev

 

[Kevin_S]

Pat - I agree with your suggestion on using the Autonumber and I have read previous posts on this by you and others... unfortunitly I was overruled by the eventual users of the db so...

If I split the mushed key into 3 columns as you suggested and make a composite key does that then mean that I need to replicate all three fields in every table or can i still use the mushed key for this?

I guess following the logic:

tblMain:
(PK) Location
(PK) FiscalYear
(PK) Seq Num
(old mushed version) SubmissionID - linked to other tables...?

If, with the mushed setup I currently have this:

tblMain
(PK) SubmissionID 'mushed key

tblInternalReview
(PK) ReviewID 'Autonumber
(FK) SubmissionID

Joined: 1 to many between tblMain and tblInternalReview on SubmissionID.

Would I have to carry all three fields in every related table? this would seem to go against the rules of normalization...?

OR...

Is it possible to stick with the mushed key and find the result with a recordset?

Thanks,
Kevin

 

[samehkh]

Life would be perfect if users and clients understood database design, or Access limitations.
You dont seem to be given much choice.

I used to design applications that would print barcode labels for assets, and the client simply wanted the barcode to consist of three parts: origin, category and serial number. Same thing with blood bags, they wanted the barcode to show the blood type, donation date and location.

What I did is that I based all the relationships on a primary key ID (autonmuber) yet kept the barcode (text) unique and it printed on labels, but all my calculations and inventory reports were based on the ID. So the only time I used the barcode was when reading data from hand held machines after an inventory.
I would import the file in a table, link on the barcode and exract the ID.

 

[Kevin_S]

Any possible effects of using this Autonumber as a PK in SQL Server 2000? I have heard that Autonumbers do not behave in SQL Server the way they do in Access? Is this true and if so do either of you or anyone else know what these effects are?

 

[RichMorrison]

Kevin,

Tossing in my 2 cents' worth:

The concept of an "intelligent key" is dead as the dodo. Any clever plan to make one part mean this and another part mean that will always fall apart over time.

As Pat H mentioned, you can make a unique index from multiple fields and the users can use the value to look up a record. Meanwhile you use an Autonumber for the real primary key and use that value to join any related tables.

As to getting the next 3-digit sequential number, you can make a table with the highest value used. Then you write a simple function that:
1) reads the highest number
3) returns this number as the value of the function
2) adds 1 and replaces the number

I do this for invoice number tracking and it works fine. The Access Developer's Handbooks have examples of this.

RichM

 

[Kevin_S]

Thanks for the info Pat, Rich, and samehkh -

As I said before I wanted to use an autonumber in the first place and I think that with this additional info I am going to make a push to use autonumbers as primary keys again since their arguement that Autonumber primary keys don't bahave the same doesn't hold water. The major problem I run into on a constant basis is this hatred by our Department DBA's/DBO's for Access as a FE development tool for SQL Server/Oracle. We constantly have to stand our ground as they attempt time and time again to move us to Powerbuilder/VB/etc... development tools because, as they see it, Access is a three legged dog. This is built with the common misperception that because access spools entire recordsets across the network it is faulty, HOWEVER, if they would STOP BASING FORMS ON LINKED TABLES and start using queries with specific criteria and Stored procedures this wouldn't be a problem... sometimes it is so frustrating

Anyway, enough of my griping - back to work!

Thanks again,
Kev

 

[RichMorrison]

Kevin,

Sounds like a lot of the folks in your shop are bozos.

I use Access 97 and 2000 as a front end to Oracle databases and it works well.

The Oracle DBAs in my shop are good people and they set up stored procedures with parameters and I just use em for reports and/or forms.

Plus I use local Access tables for simple lookup fields.

For example, the user selects a "from" and "to" date range from 2 combo boxes. The combo boxes are based on local Access tables that use our standard reporting periods - quarter, month, year, etc. Code in the form translates the selected dates into the Oracle format and sends the dates off as parameters to a stored procedure. So the users need not care about Oracle date formats.

This is the type of "multi tier" architecture that is becoming standard practice in effective shops. And it all works just fine with Access.

Maybe you can politely ask your management to wake up and smell the coffee

RichM

 

[Kevin_S]

Maybe you can politely ask your management to wake up and smell the coffee

Rich - I work for the government.... its not that easy!

Thanks for the help,
Kevin

 

[samehkh]

I did all my database programming in Access. It happened to me once that the client said they didnt have Access and they wanted the application in VB (only to turn out later that they did have Access )
And from the very slight experience I had with VB I found that what you can do in Access in 2 seconds could take up to 5 minutes in VB. For example setting the recordsource of the form to a certain table and then dragging and dropping the fields onto the form versus setting a data control, adding textboxes and manually setting the data source of each textbox to a field in the data control, plus having to build an "add new record" button since the data control doesnt have a new button.
I always wanted to port my skills to VB because some employers and projects dictate that VB should be used, but I totally disliked it and always wondered how can I implement all the useful features built into Access to work in VB, like for example, right clicking in a form to apply filter or search, and all the powerful reporting and useful controls and master/child subforms that are still in their primitive stages in VB. I dont know if they got any better now but that time I was using VB 5 or 6.
I always wondered what is the advantage of VB that makes some people so fond of it to that extent, besides generating EXE and being more secure than Access MDB ?!!

 

[RichMorrison]

Kevin wrote
<<
Rich - I work for the government.... its not that easy!
>>

Must be state or local. My client (a federal agency) is more up to date.

RichM

 

[Kevin_S]

Must be state or local. My client (a federal agency) is more up to date.

State gov

Kevin

 

[samehkh]

I didnt mean I base all my forms on actual tables, I just meant that binding a form to a record source in Access is a snap, while in VB it is a job that takes some time, and I am just giving an example of the simplest thing to start with, let alone having to code "filter by form" and the search functionality....etc that is already buit in Access forms, besides the data grids and other controls in VB that are still in their primitive stages comapred to Access forms and controls. Also I remeber having to do some reports with crystal reports that came with VB 5, maybe I wasnt used to it, but it took me a lot of time and i felt it was retarded!

Thanks for the advice anyways.