Validation on Access

A

ace-london-uk

How do I add a validation to allow a record to only duplicate itself maximum
5 times?
Because I have an Order ID field, and in the order there can be many items,
but the maximum items under one order is 5, how do I create this validation
under this Order ID field?
 
A

Arvin Meyer

Something like this ought to do it:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim RS As Recordset

Set RS = Me.RecordsetClone
If RS.RecordCount > 0 Then
RS.MoveLast
Else
RS.OpenRecordset
End If
If RS.RecordCount > 2 Then
Cancel = True
MsgBox "You are only allowed to enter 3 records!"
End If

RS.Close
Set RS = Nothing
End Sub
 
A

Arvin Meyer

Hit send way too soon. The other one was just pasted without changing the
numbers. This one should be correct:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim RS As Recordset

Set RS = Me.RecordsetClone
If RS.RecordCount > 0 Then
RS.MoveLast
Else
RS.OpenRecordset
End If
If RS.RecordCount > 4 Then
Cancel = True
MsgBox "You are only allowed to enter 5 records!"
End If

RS.Close
Set RS = Nothing
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
A

ace-london-uk

hi there.... it doesn't seem to work, I was on the form design view, right
clicked the Order ID field, chose build event> code builder, pasted the codes
in, and there was an error in the code somewhere,
the part where it says RS.openrecordset

how do I fix this? did I do the procedure correctly?
 
A

Arvin Meyer

You may need to set a reference to DAO. This was some older code that had
been in use in Access 97 where the references were automatically set to DAO.

From any code window, go to Tools ... References and uncheck the ADO
reference (if you don't need it) then find and check the one for DAO.
(Microsoft DAO 3.6 Object Library) If you are leaving the ADO reference,
make sure you disambiguate the Recordset by using an explicit dim statement:

Dim RS As DAO.Recordset

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top