GENERATE ERROR FOR UNIQUE FIELD ON FORM

  • Thread starter Thread starter Sammie
  • Start date Start date
S

Sammie

I have a form used to enter new records in response to a not-in-list
event. It contains 2 fields which do not allow duplicates. Niether of
these two fields is the primary key. The problem is that the user
doesn't see that they have entered a duplicate until after they have
closed the form and they receive an error on the not-in-list event. How
can Access prevent the user from entering a duplicate when they enter
the field, so they know exactly why it is not an acceptable entry? I'm
having trouble writing error handling code on my not-in-list procedure,
when I think it's the duplicates on the other form that are causing the
problem.

Thanks.
Sammie
 
Sammie,

Here's one approach, for code on the BeforeUpdate event of the textboxes
on the "other" form...

If DCount("*","YourTable","FieldValue='" & Me.ControlValue & "'")>0 Then
MsgBox "You've already got one of those"
Cancel = True
End If
(assumes a text data type)
 
Steve said:
Sammie,

Here's one approach, for code on the BeforeUpdate event of the textboxes
on the "other" form...

If DCount("*","YourTable","FieldValue='" & Me.ControlValue & "'")>0 Then
MsgBox "You've already got one of those"
Cancel = True
End If
(assumes a text data type)
Click to expand...
Steve,
Thanks. That works just great. I still get the system "not in list"
message on the main form, however. (I do not get my own not-in-list
error message for some reason.) What can I do to exit this procedure if
the user has already read my new error message? Getting 2 error
messages is confusing to the user.
Thanks.
Sammie

Here is my not-in-list procedure:
'Ask the user whether to add a value to the list
On Error GoTo Err_Product_NotInList

Dim strMessage As String
strMessage = "Are you sure you want to add '" & NewData & "' to the
list of products?'"

If Confirm(strMessage) Then
'Open the frmProductsInvoiced form and add the NewData value.
strProduct = NewData
DoCmd.OpenForm "frmProductsInvoiced", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!product.UnDo
Me!product.Dropdown
End If
Exit_Product_NotInList:
Exit Sub

Err_Product_NotInList:
DisplayMessage "You can't enter the new product right now."
Resume Exit_Product_NotInList

Note: I am not getting the error message listed in this code. I'm
getting the error "The text you entered isn't an item from the list.
Selece an item from the list or enter text that matches one of the
listed items."

FYI: My new procedure on the "other form"
Private Sub PartNo_BeforeUpdate(Cancel As Integer)
If DCount("*", "ProductsSold", "PartNo='" & Me.PartNo & "'") > 0 Then
MsgBox "That part number is already used in the database. Please
press escape to cancel, and either use the existing record or correct
your part number."
Cancel = True
End If
End Sub
 
Sammie

I am not sure what is the meaning of Confirm(strMessage). I suppose
Confirm() is a user-defined function? Are you sure this is set up and
working correctly?
 
Steve said:
Sammie

I am not sure what is the meaning of Confirm(strMessage). I suppose
Confirm() is a user-defined function? Are you sure this is set up and
working correctly?
Click to expand...
Steve,

My Confirm(strMessage) is an OK/cancel user-defined function. It seems
to work ok. But when I come back to my not in list procedure after
closing the other form and not adding a record, it moves from Response =
acDataErrAdded (the value of acDataErrAdded = 2; I don't know what that
means) to End if, when it should go to Else Response = acDataErrContinue.

Going back to the procedure you suggested, in the line Cancel = True:
have I completely cancelled this record addition? I am asking the user
to press escape to cancel, and then they still have to manually close
the other form, but I would prefer to have the VB code cancel the
not-in-list procedure altogether at this point. Is this possible?

Thanks.
Sammie
 
Back
Top