limit entries in a subform 1

[Cillies]

Hi all,

I have a database in microsoft access based on a video store. What I need to be able to do is limit the number of entries/ videos that a customer can take out i.e set a limit of maximun 3 videos per night.

I have built a unbound text box in a form that the user can input the customerID, click open which opens a form with the customer details, like name, address, etc plus a subform with viedo name, certificate etc. What I need is to be anle to limit the number of possible videos entered to a maximun of three. i.e. once the third video has been entered Access won't allow another. Plus is there a simple way of doing this without using VBA.

Hope someone can help. Mank thanks in advance

 

[Remou]

If you create a customer / video table like so:

CustomerID ) Combined as primary key
VideoCount )

VideoCount Validation Rule: >0 And <=3

It should not be possible to add more than three records per customer.

 

[TheAceMan1]

How are ya Cillies . . .

In the subforms [blue]OnCurrent[/blue] event try this:

[blue]   If Me.NewRecord And Me.Recordset.RecordCount > 2 Then
      MsgBox "No More Than 3 Video's  Please!"
      Me.Recordset.MoveLast
   End If[/blue]

See Ya! . . . . . .

 

[Cillies]

Hi Guys!

Thanks for your responses both worked perfectly, but I have now discovered that it would be better if the limit of three video worked by relevance to the date taken out. I.E. the customer can only take three videos per night. Therefore the video store can keep a record of all the videos taken out but will allow only three per night.

Sorry if I'm being a pain.

Many thanks

 

[Remou]

With my suggestion, add date to the key fields. That is:
[tt]CustomerID ) Combined as primary key
VideoCount )
OutDate )[/tt]

 

[Cillies]

Hi Remou,

I tried that but it will only let me enter three videos regardless of date, do you know of any other way of doing this.

"I have a Loan table with three primary Keys as you recommended, the VideoCount has a type of Number which I enter manually."

Thank you very much for your help so for.

 

[Cillies]

Remou!

scratch that I was being silly. Its working fine.

Thanks again for your help, I was thinking it was an automatic solution, as in when you enter three dates the same, a message box would appear informing the empolyee that only three video's per night are allowed.

 

[Remou]

Hi Cillies
I tried the idea in a table:
[tt]CustID OutDate VideoNo
1 14/05/2006 1
1 14/05/2006 2
1 15/05/2006 1
1 15/05/2006 2
1 15/05/2006 3
2 15/05/2006 1
2 15/05/2006 3[/tt]

Key: CustID + OutDate + VideoNo

Is it possible that the form you tried this on still has coding suggested by AceMan? If you wish to use such code, you will need to modify it to take acount of the date allowance.

 

[Remou]

Oops, missed your post. Here is a version of AceMan's idea:

Private Sub Form_BeforeUpdate(Cancel As Integer)
strsql = "Select * From tblTable Where " _
   & "CustID=" & Me.CustID & " And OutDate=#" _
   & Format(Me.OutDate, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(strsql)

If Not rs.EOF Then rs.MoveLast
If rs.RecordCount > 2 Then
      MsgBox "Tut, Tut."
End If

End Sub

 

[Remou]

You can add a customized message when the index violation occurs by using Form_Error. Roughly:

Private Sub Form_Error(DataErr As Integer, response As Integer)
   Const INDEX_VIOLATION = 3022
   Select Case DataErr
   Case INDEX_VIOLATION
      MsgBox "Only three videos per night per cutomer."
      response = acDataErrContinue
   
   End Select
   Debug.Print DataErr
End Sub