check for duplicate value before entering

S

Steve

Hi I have a small membership database.
I have a field called MNumber which is a unique number it is not the primary
key but it does have an index set to unique so cant have duplicate values.
I have a button on the form that will create a random number and fill in
this field but I dont have a way of checking if the random number is unique
before the button places the number in the field.
I would like to click the button and have the database check the number
against all the other membership numbers then if its not unique create a new
numbers untill it is unique then allow it to be placed in the field.
Any help most apreciated
Thanks
Steve
 
S

Steve Schapel

Steve,

Assuming you are generating the random number in code somehow, here are
the skeletons of a couple of possible approaches...

If DCount("*","YourTable","[MNumber]=" & NewRandomNumber) = 0 Then
Me.MNumber = NewRandomNumber
Else
< rerun the random generation >
End If

or...
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourTable")
rst.FindFirst "[MNumber]=" & NewRandomNumber
If rst.NoMatch Then
Me.MNumber = NewRandomNumber
Else
< rerun the random generation >
End If
 
S

Steve

Thanks Steve you are a legend
I will try this straight away.
I have been away, so I was late looking at this. I just had a mental block
but you have me back on track and I thank you.

Steve ( Australia )

Steve Schapel said:
Steve,

Assuming you are generating the random number in code somehow, here are
the skeletons of a couple of possible approaches...

If DCount("*","YourTable","[MNumber]=" & NewRandomNumber) = 0 Then
Me.MNumber = NewRandomNumber
Else
< rerun the random generation >
End If

or...
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourTable")
rst.FindFirst "[MNumber]=" & NewRandomNumber
If rst.NoMatch Then
Me.MNumber = NewRandomNumber
Else
< rerun the random generation >
End If

--
Steve Schapel, Microsoft Access MVP
Hi I have a small membership database.
I have a field called MNumber which is a unique number it is not the primary
key but it does have an index set to unique so cant have duplicate values.
I have a button on the form that will create a random number and fill in
this field but I dont have a way of checking if the random number is unique
before the button places the number in the field.
I would like to click the button and have the database check the number
against all the other membership numbers then if its not unique create a new
numbers untill it is unique then allow it to be placed in the field.
Any help most apreciated
Thanks
Steve
Click to expand...
Click to expand...
 
Top