limit of records

[Chey]

can you limit how many records for a specific number
For example. We use Travel Authorization numbers. I want to only allow 2
Agent fees per TA Number.
Thanks
chey

[Larry Linson]

"Chey" wrote

> can you limit how many records for a specific number
> For example. We use Travel Authorization numbers. I
> want to only allow 2 Agent fees per TA Number.

Write a "key generator" to generate the AgentFeeID, starting with 1, and set
the Validation for the AgentFeeID in the Table to <3. Key your Agent Fee
Records on the combination of TA and AgentFeeID. Here's the vital code,
assuming the Agent Fee table is "tblAgentFee" and the TA number is displayed
in the current form in a Control named "txtTA."

strCriteria = "[TA] = " & Me.txtTA
DMAX ("AgentFeeID","tblAgentFee", strCriteria)

Larry Linson
Microsoft Access MVP

[Chey]

where do I paste the code at?

[Chey]

What is a "Key Generator?

[Tom Wickerath]

Hi Chey,

Are you using a subform to display the Agent fees? If so, this should be
fairly easy to do. Try opening the sample Northwind database. Add the
following code behind the form named "frmCategoriesSubform". This example
limits the number of related records to 14 (originally written to simulate
two weeks worth of data entry for each pay period). Of course, you can change
the 14 in the code to 2 for your situation:

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

Dim rs As DAO.Recordset
Dim intRecordCount As Integer

Set rs = Me.RecordsetClone

If rs.RecordCount = 0 Then ' We have no records in subform
Me.AllowAdditions = True

Else ' We have some records
rs.MoveLast
intRecordCount = rs.RecordCount

If intRecordCount < 14 Then
Me.AllowAdditions = True
Else
Me.AllowAdditions = False
End If

End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________