Limiting the number of transactions possible

[Ron]

Hi everyone,

I've got an ap that needs to print a preprinted form as a statement (forced
on me, not my choice!). The form has 5 lines for transaction details, plus
some general data stuff at the top, and underneath the detail area like who
the bill is for, their address, sender of the bill's name and address, and
other stuff.

I can't figure out a way to stop the user from entering more than those 5
lines on each bill. My solution was to show a screen for the form info,
then have an "add transaction" button and I could limit that to the number
(5) or if less than five exist and they're adding to an existing form, I
could still limit the additions to no more than 5 total. Unfortunately,
everyone using it thinks it's kludgy. Heck, I agree.

Anyone know of a way to limit the number of detail lines on a customer
invoice form?

TIA
ron

 

[Allen Browne]

Cancel the subform's BeforeInsert event if there are already 5 items in the
table.

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngCount As Long
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter a record in the main form first."
Else
strWhere = "InvoiceID = " & !InvoiceID
lngCount = DCount("*", "InvoiceDetail", strWhere)
If lngCount >= 5 Then
Cancel = True
MsgBox "You already have " & lngCount & " line items.", _
vbExclamation, "No more!"
End If
End If
End With
End Sub

 

[Ron]

FANTASTIC! Works like a champ! You have no idea how much time I used up
trying to figure this out on my own. All's well that ends well.

Thanks a bunch, Allen!
ron