Creating a custom primary key

I work for a small ambulance company and I'm Trying to build an application that will help us dispatch our runs and keep track of times for each call. I have recieved help on here already and I need to ask for more. I need to create a primary key for each run that will be able to reset each year on new years eve. What I mean is, it run gets a Run Number (the primary key) and they have to be different for each run so that we can track each run. I was thinking if I could combine an autonumber field with a year so that I could tell which year the run happened in. I'm open for suggestion. I also have several table that I need to combine into one and make it updateable......Plus, I to be able to look at just the runs for a day at a time, but I also need to be able to look to the future for run that are already schedule. I know I put alot on here and I don't expect help on all of it but any help would be great.

Ryan,

Before thinking it deeply, may I have a question.
Is there a strong reason for resetting the primary key on Jan. 1? Why is it important?

The State requires that your run numbers reset at midnight on jan 1 each year so that they know that they are getting the infomation for the current year.

Ryan,

In this case the developer cannot do anything but accept the reqiurement and do her/his best to fulfill it...

You were right thinking to combine the year and a number field. But this cannot be an AutoNumber because you cannot tell an AutoNumber to reset and count again.

What you can do is set up a table to store the number that will be the next ID for the new record in a table.

Create a table named tblNextKey. Fields would be:
TableName(Type: Text, Length: 50)
NextKey(Type: Long)

In this table, you can store all the table's next ID.

Lets say the table where you log the runs is called tblRun.

Then the first record in tblNextKey would be:
TableName="tblRun"
NextKey=1

I guess you register a run in the year when the emergency call received. I mean if a call received on 31/12/2003 it will be registered BEFORE 01/01/2004 00:00:00.

To make sure that RunNumber will reset when the first call in a year is entered, we need a function that checks if there is an existing call for the current year:
'************************
function IsFirstCallInYear(lngYear as long) as boolean
dim db as dao.database
dim rs as dao.recordset
dim mysql as string
set db=currentdb
mysql="select RunNumber from tblRun where val(left(RunNumber,4))=" & lngYear
set rs=db.openrecordset(mysql)
if rs.recordcount<1 then
IsFirstCallInYear=true
else
IsFirstCallInYear=false
endif
end function
'************************
When a new record is added to tblRun then include the following code into the BeforeInsert event handler of the form you use to enter the runs:
'************************
dim mysql as string
dim lngRunNumber as long
if IsFirstCallInYear(year(now)) then ' we call IsFirstCallInYear
lngRunNumber=1 ' if it's the first call in the year, then RunNumber will reset
else
lngRunNumber=DLookUp("NextKey","tblNextKey","[TableName]='tblRun'")) ' if this is not the first run in the year, we continue incrementing the RunNumber.
endif
me.RunNumber=str(year(now)) & "/" & str(lngRunNumber) ' you can use any separator character instead of "/".
mysql="update tblNextKey set NextKey=" & lngRunNumber+1 & " where TableName='tblRun'" 'we increment the value of NextKey.
docmd.runsql MySQL
'************************
The BeforeInsert event occurs when the user types the first character in a new record, but before the record is actually created.

Using this code the first record's RunNumber will be 2003/1, and the NextKey for tblRun will be 2. When the next record is added to tblRun, the RunNumber will be 2003/2, and the NextKey for tblRun will be 3 and so on.

Even if the year of the run is registered in RunNumber field, you need to have a field where you set the exact date/time of the run.

Please don't forget handling run-time errors. If a run-time error occurs somewhere in the code (before docmd.runsql mysql, for example) it will cause the NextKey will not be set correctly and the same number will be distributed when the next record is added. And this would also cause a run-time error, because RunNumber is the primary key and does not accept duplicates.

I haven't run a test for the code above, but I think it should work and shouldn't cause compilation error. But devil never sleeps...

I think, If you're just beginning to build this application, then the code above can only be just a small piece of it, but I hope it makes sense.

I keep getting an error saying that the sub or function is not defined.....I'm not sure I entered the code right can you check what I've done and see if I did it right?

Here is the code as I entered it:

Option Compare Database
Function IsFirstCallInYear(IngYear As Long) As Boolean
Dim db As dao.database
Dim rs As dao.Recordset
Dim mysql As String
Set db = CurrentDb
mysql = "select RunNumber from tblRun where val(RunNumber,4))=" & IngYear
Set rs = db.openrecordset(mysql)
If rs.RecordCount < 1 Then
IsFisrtCallInYear = True
Else
IsFistCallInYear = False
End If
End Function

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim mysql As String
Dim IngRunNumber As Long
If IsFistCallInYear(Year(Now)) Then ' we call IsFirstCallInYear
IngRunNumber = 1 'if it's the first call in the year, then RunNumber will reset
Else
IngRunNumber = DLookup("NextKey", "tblNextKey", "[TableName]='tblRun'") ' if this is not the first run in the year, we continue incrementing the RunNumber.
End If
Me.RunNumber = Str(Year(Now)) & "-" & Str(IngRunNumber)
mysql = "update tblNextKey set NextKey=" & IngRunNumber + 1 & "where TableName='tblRun'" 'weincrement the value of NextKey.
DoCmd.RunSQL mysql

End Sub

I entered it all in BeforeInsert event handler of the form I don't know if that was the problem.

Oh, sorry, I made a spelling mistake.

Replace the line:

If IsFistCallInYear(Year(Now)) Then ' we call IsFirstCallInYear

with:

If IsFirstCallInYear(Year(Now)) Then ' we call IsFirstCallInYear

Now I'm getting an error on this part of the code:

Option Compare Database
Function IsFirstCallInYear(IngYear As Long) As Boolean
Dim db As dao.database
Dim rs As dao.Recordset
Dim mysql As String
Set db = CurrentDb
mysql = "select RunNumber from tblRun where val(RunNumber,4))=" & IngYear
Set rs = db.openrecordset(mysql)
If rs.RecordCount < 1 Then
IsFisrtCallInYear = True
Else
IsFistCallInYear = False
End If

It say Complie error :
User-defined type is not defined
End Function

Figured out part of it somehow my DOA library got deleted but now I'm getting another error....Run-time Error:
Extra ) in query expression val'(runnumber,4))=2003'

I haven't changed the code so I don't know what the problem is?

Ryan,

The unrecognized types are probably DAO.Database and DAO.Recordset.

I guess the error occurs because DAO Object Library is not registered.

To register the library go to Tools->References... menu path in VB editor, click <Browse> and locate DAO350.DLL, or DAO360.DLL.
These files are usually stored in Program Files\Common Files\Microsoft Shared\DAO folder.

DAO350.DLL contains Microsoft DAO 3.5 Object Library
DAO360.DLL contains Microsoft DAO 3.6 Object Library

Register one of them, but preferably 3.6.

Then it should be OK.

Use Val(Left(RunNumber,4)).
However, I could not find Val(RunNumber,4)) in the code I originally sent you... so you may still have modified it...