I'm needing a primary key. This is for animal intake in an animal shelter. I
want the first part of the key ID to reflect the date such as 0702 and the
second to be a sequential number. The number to flip back to 001 the next
month. So I would have 0702001 for the first intake in Feb of 2007 and
0703001 for the first intake of March 2007. I want them to be able to input
the date part (which will also be part of the intake date) but to have the
rest autonumber.We need this to make it easy by looking at the ID to know
when the animal came in. This ID will go on charts, be used when the vet
calls, etc. Can I take 2 fields on the table and make them into one primary
key? Or should I just make the a standard sequential and make the ID not a
key?
Maybe try something like this. This uses two separate fields as a primary key.
This will probably only work with a single user database. I have read that
self-baked numbering sequences always fail in a multi-user database.
Copy sub code to a module of a new database and type
call CreateFeedings in the immediate window.
(not really tested)
Sub CreateFeedings()
With CurrentProject.Connection
.Execute _
"CREATE TABLE Animals" & _
" (animal_nbr INTEGER NOT NULL" & _
", animal_name VARCHAR (25) NOT NULL" & _
", PRIMARY KEY (animal_nbr));"
.Execute _
"CREATE TABLE Feedings" & _
" (intake_date DATETIME DEFAULT Now() NOT NULL" & _
", intake_sequence INTEGER DEFAULT 0 NOT NULL" & _
", animal_nbr INTEGER DEFAULT 1 NOT NULL" & _
", CONSTRAINT FK_Animals" & _
" FOREIGN KEY(animal_nbr)" & _
" REFERENCES Animals(animal_nbr)" & _
", CONSTRAINT PK_Feedings" & _
" PRIMARY KEY (intake_date,intake_sequence));"
End With
End Sub
form: frmFeedings
txtIntakeDate
txtIntakeSequence enabled and locked
txtAnimalNbr
Private Sub txtIntakeSequence_GotFocus()
If Me.NewRecord Then
Me.txtIntakeSequence = Nz(DMax("intake_sequence", _
"Feedings", "DateDiff('m',0, #" & Me.txtIntakeDate & "#)" & _
" = Datediff('m',0,intake_date)")) + 1
End If
End Sub
