No Duplicates allowed

[Guest]

I have a Data Base that has a particular number field where No Duplicates are
allowed.
If I enter a number that is already allocated and move to the next record, I
get an error message stating that there is a duplicate (OK).

However, if I hit Exit, the data Base exits without any warning and when you
reopen the record is naturally missing. Is there a way that you can get an
error message to appear when you exit, informing the user that the record
will not be saved because of the duplicate?

Thanks for any help

 

[Guest]

Hi Roger

You may be better using an autonumber.

But if you already have the system in place you could use the AfterUpdate
event of the text box with the number on the form (note Air-Code so "not"
checked - change the field and control names to what they really are)

Private Sub BoxName_AfterUpdate()
If ((DLookup("[PrimeryField]", "[TableName]", "[NumberField] ='" &
Form!NumberControlOnForm '"))) Then
MsgBox "This number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

Hope this helps

 

[Guest]

Try to use the form BeforeUpdate event to check there are duplicates, and
prompt the user with the message


Dim Criteria as String
Criteria = "[NumberFieldName]=" & Me.[NumberFieldName in form]
If DCount("*","[TableName]",Criteria) > 0 Then
MsgBox "The Number exist"
Cancel = True ' will stop the process
End If

Note: If the field that you check on is text, you need to add single quote
Criteria = "[NumberFieldName]='" & Me.[NumberFieldName in form] & "'"

 

[Guest]

One more thing, its better to put the code in the BeforeUpdate event of the
text box in the form, that way the user will know before trying to close the
form

 

[Guest]

Thanks Wayne
Have tried the following without success:

Private Sub Envelope_Number_AfterUpdate()
If ((DLookup("[Autonumber]","[Main Table]","[Envelope Number} =" &
Form!ControlOnForm")))Then
MsgBox "Sorry, this number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

In your example you showed what appears to be 3 ''' after = and control on
form. Is this correct? Also, where it reads Form!NumberControlOnForm, what
should i substitute here.

Sorry for being a pain but your help is really appreciated.

Hi Roger

You may be better using an autonumber.

But if you already have the system in place you could use the AfterUpdate
event of the text box with the number on the form (note Air-Code so "not"
checked - change the field and control names to what they really are)

Private Sub BoxName_AfterUpdate()
If ((DLookup("[PrimeryField]", "[TableName]", "[NumberField] ='" &
Form!NumberControlOnForm '"))) Then
MsgBox "This number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

Hope this helps

--
Wayne
Manchester, England.

I have a Data Base that has a particular number field where No Duplicates are
allowed.
If I enter a number that is already allocated and move to the next record, I
get an error message stating that there is a duplicate (OK).

However, if I hit Exit, the data Base exits without any warning and when you
reopen the record is naturally missing. Is there a way that you can get an
error message to appear when you exit, informing the user that the record
will not be saved because of the duplicate?

Thanks for any help

 

[Guest]

Hi Roger,

In the AfterUpdate event of the field you can't use Cancel = True, so change
it to the BeforeUpdate event where you can use it.

And in the code you had few errors, like:
[Envelope Number} Not closed with square brackets, and more.
************************
Try:

Private Sub Envelope_Number_BeforeUpdate()
If DCount("*","[Main Table]","[Envelope Number] =" & Me. Envelope_Number) >
0 Then
MsgBox "Sorry, this number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub
************************
If the Envelope_Number is text type field try addidng single quote for the
string:

Private Sub Envelope_Number_BeforeUpdate()
If DCount("*","[Main Table]","[Envelope Number] ='" & Me. Envelope_Number &
"'") > 0 Then
MsgBox "Sorry, this number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

--
Good Luck
BS"D

Thanks Wayne
Have tried the following without success:

Private Sub Envelope_Number_AfterUpdate()
If ((DLookup("[Autonumber]","[Main Table]","[Envelope Number} =" &
Form!ControlOnForm")))Then
MsgBox "Sorry, this number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

In your example you showed what appears to be 3 ''' after = and control on
form. Is this correct? Also, where it reads Form!NumberControlOnForm, what
should i substitute here.

Sorry for being a pain but your help is really appreciated.

Hi Roger

You may be better using an autonumber.

But if you already have the system in place you could use the AfterUpdate
event of the text box with the number on the form (note Air-Code so "not"
checked - change the field and control names to what they really are)

Private Sub BoxName_AfterUpdate()
If ((DLookup("[PrimeryField]", "[TableName]", "[NumberField] ='" &
Form!NumberControlOnForm '"))) Then
MsgBox "This number is already in use", vbOKOnly, "Number checker"
Cancel = True
End If
End Sub

Hope this helps

--
Wayne
Manchester, England.

I have a Data Base that has a particular number field where No Duplicates are
allowed.
If I enter a number that is already allocated and move to the next record, I
get an error message stating that there is a duplicate (OK).

However, if I hit Exit, the data Base exits without any warning and when you
reopen the record is naturally missing. Is there a way that you can get an
error message to appear when you exit, informing the user that the record
will not be saved because of the duplicate?

Thanks for any help

 

[Guest]

ooops

Roger - Offer is right about this sorry for the error.