prevent duplicate in a range between two feilds

[antar1]

I have a access table with two feilds starting sn and ending sn I would to prevent the range of numbers between those two feilds from being enterd in the future for example if the starting SN is 1 and the ending SN is 5 I would like to prevent 1, 2, 3,4 and 5 from being entered in the next record.

Please help

Thanks

 

[boblarson]

How are you entering the numbers? Are you using a form for entry?

 

[antar1]

yes it's an access form, it would be for example starting number in one cell 101 and the ending number would be 109 and that's one record in the new record the starting number has to be greater than 109 and so on.

Thanks

 

[xenou]

An autonumber field would always be greater than the highest existing number. Would that work?

 

[xenou]

It also looks like a validation rule works (assuming the field is not an autonumber field).
In my test the numeric field is "ID" and the table is "Products" so the validation rule is: >DMax("ID","Products")

<img alt="validation rule" src="http://northernocean.net/etc/mrexcel/20110413_validationrule.png" />

 

[boblarson]

NO, Autonumbers do NOT guarantee that

An autonumber field would always be greater than the highest existing number.

xenou:
That is very much incorrect. An Autonumber isn't necessarily greater than the highest existing number. Autonumbers ONLY (I repeat, ONLY) guarantee a UNIQUE number. It is NOT guaranteed, even if they are set to INCREMENT, to increment and they can be NEGATIVE numbers as well. So autonumbers are NOT one to rely upon if you need serial numbers.

 

[boblarson]

It also looks like a validation rule works (assuming the field is not an autonumber field).
In my test the numeric field is "ID" and the table is "Products" so the validation rule is: >DMax("ID","Products")

That would be preferred over an autonumber situation but the best way would be BESIDES the validation rule, to use a form for entry and use the form's BEFORE UPDATE entry to make sure of the validation so then you can capture it and give the user an even better response than you can simply by using the validation text here.

 

[xenou]

Good advice. Another example using a before update event. As before, the relevant data for my test is a field "ID" in a table "Products". The textbox here is named Text1:

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Text1_BeforeUpdate(Cancel [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset

    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset("SELECT Max([ID]) FROM Products;", dbOpenSnapshot)
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] CLng(Me.Text1.Value) <= rs.Fields(0).Value [COLOR="Navy"]Then[/COLOR]
            Cancel = True
            MsgBox "Error: ID must be greater than " & Format(rs.Fields(0).Value, "#,##0") & ".  "
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    rs.Close

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]