auto numbers that aren't primary key

[Guest]

I have a database that tracks manufacturing test information. I have a form
that the user enters all of the test information (date, user, yada yada). The
primary key is "Test Number" which is a Auto Number. Within each test, there
are multiple samples. I have a subform that has the sample number and fields
to enter the statistics of the sample. What I want to be able to do is have
the sample number automatically be entered. For example, for Test Number 1,
there are 10 samples. I don't want the user to have to enter the sample
number in the subform. Is there a way to have that field auto enter without
making it a primary key? The reason for that is because Test Number 2, 3,
etc. will also have Samples 1, 2, 3, ..., n.

Any suggestions?
Thanks!

 

[Lynn Trapp]

Search the help file for DMax

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Allen Browne]

Use the BeforeInsert event of the form to lookup the highest sample number
used so far in the subform's table, for the Test Number in the main form.

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a main form record first."
Else
strWhere = "[Test Number] = " & ![Test Number]
Me.Sample = Nz(DMax("Sample", "MySubformTable", strWhere),0) + 1
End If
End With
End Sub

Note that that could give duplicates where multiple users are entering
values together. Using the BeforeUpdate event of the form would help avoid
that, but it could still happen.