check for duplicate value before entering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
Back
Top