Custom autonumbering in Access XP-Expert help needed!

[shortone]

Hello all!
As usual, the powers-that-be think that I can produce a rabbit out of my hat in less than a week. Here is my problem (sorry it's wordy, but it's necessary):
I have a database that tracks projects that come in from different school districts throughout the state, each with their own District Code ("DistCode&quot and each school has their own Code ("SchoolNo&quot. I'd developed a wonderful autonumbering system (so I thought, at least it was easier to code!), that concatenated the District Code, School Code, Fiscal Year and tagged a unique identifier at the end. Now the higher ups want to change hwo the number is generated. For each (would that be a clue as to how to proceed?) School in a particular District, they would like to start the first parent project of the year with "1000" (e.g., 1234-050-04-1000). For each subsequent project for THAT SCHOOL, the last four digits would be "2000', then "3000", etc. The kicker is that under each parent project, there could be other sub-projects, designated by specific numbers, i.e., "100" = Temporary Facilities, "200"= Land acquisitions, etc. My problem is how to enter this data and have the last four digits look at the previous projects for that district & school and give it the next number. I really seem to be stuck here, and the new Fiscal Year has already begun (well, as soon as the State leaders pass the budget, I guess). Can anyone give me a clue as to how to proceed? I can get the first record for each school, I'm stuck on how to go back and look at what's in the table to assign the next number. Thanks so much in advance!!



JayeD
"I'm sure of two things: there IS a God, and I'm NOT Him!" - R. Williams

 

[Norris68]

I haven't got time to do much in depth, but I would have thought you woud want a public function.

Public Function NewProjectCode(District As String, School As String, FiscalYear As String) as string

Dim HighestCode As String
Dim R AS ADODB.Recordset
Set R = New ADODB.Recordset
R.Open "SELECT MAX(ProjectCode) AS HCode FROM YourTable WHERE ProjectCode LIKE '" & District & "-" & School & "-" & FiscalYear & "-* ORDER BY ProjectCode", CodeProject.Connection
If Not R.EOF Then
HighestCode = R("HCode&quot.Value
NewProjectCode = Left$(HighestCode,12) & CStr(CLng(Mid$(HighestCode,13,1))+1) & "000"
End If
R.Close
Set R = Nothing
End Function

This assumes you keep a consistent format. The thirteenth digit will be the project code. Take that number, add one and stick three zeros after it.

 

[shortone]

Norris68,
Yes, you're correct in assuming I'd want a public function. I worked for 11 hours yesterday, so I apologize for not being more specific. I'll give it a try and let you know of the results. Thanks for your speedy reply!

God bless you!

JayeD
"I'm sure of two things: there IS a God, and I'm NOT Him!" - R. Williams

 

[Norris68]

I've just realised I missed off the closing single quote after the wildcard asterisk in the WHERE clause. You will be searching on ProjectCode LIKE '1234-050-04-*' in your example.

 

[shortone]

Hi again!
I think that this would work, except that I am using DAO recordsets and keep getting "Type Mismatch". How would I change the code to reflect DAO objects? My thinker is hurting... (Sorry for asking for such simple help, I'm a one person IT department here!).

Thanks again!




JayeD
"I'm sure of two things: there IS a God, and I'm NOT Him!" - R. Williams