custom pop-up Message when one attempts to add a duplicate record

[LindaRuble]

Hello,



I would like to create a custom pop-up Message when one attempts to add a duplicate record. Currently, I have a default pop-up message when you try to add a duplicate record. I set the field as Indexed= yes(No Duplicates) which generates a default pop-up message but it is technical and wordy. I would like to be able to simplify the wording to just let the user know not to add a duplicate record. It would be greatly appreciated if you could tell me how to accomplish this with VBA.

Thanks



Linda

[June7]

Is this on a form?

Use BeforeUpdate event of the textbox bound to the field. Try:
If Not IsNull(DLookup("fieldname","tablename","fieldname= '" & Me.textbox & "'")) Then
MsgBox "Value " & Me.textbox & " already used."
Cancel = True
Me.textbox = Null
End If

[LindaRuble]

Is this on a form?

Use BeforeUpdate event of the textbox bound to the field. Try:
If Not IsNull(DLookup("fieldname","tablename","fieldname= '" & Me.textbox & "'")) Then
MsgBox "Value " & Me.textbox & " already used."
Cancel = True
Me.textbox = Null
End If

Will this code also suppress the default pop-up message from popping up?

Linda

[June7]

It should because this deals with possible error before the error happens.

Be sure to use your field, table, textbox names in the code.

[LindaRuble]

Hello June7,

I have changed your code to work with a drop-down combo box. It also takes care of another error.....
Run-time error '451':
Property let procedure not defined and property get procedure did not return an object

Here is the code:

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++

Private Sub Combo75_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Combo75_BeforeUpdate
If Not IsNull(DLookup("Client_Division", "ClientDiv", "Client_Division='" & Me![Combo75].Column(1) & "'")) Then
MsgBox "Value " & Me![Combo75].Column(1) & " already used."
Cancel = True
Me![Combo75].Column(1) = Null
End If
Exit_Combo75_BeforeUpdate:
Exit Sub

Err_Combo75_BeforeUpdate:
Resume Exit_Combo75_BeforeUpdate
End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++

I am still getting the following validation error:

The value violates the validation rule for the field or record.
For example, you might have changed a validation rule without verifying whether the existing data matches the new validation rule.
Click Undo to restore the previous value, or enter a new value that meets the validation rule for the field or record.


How do I avoid this validation error?


Thanks


Linda

[June7]

Show the validation rule.

Won't fix the error but don't reference the Column property.
Me![Combo75] = Null

or try

Me.Combo75.Undo

[LindaRuble]

Hi,

I need to reference the Column property or I'll get the following error:

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the filed of fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++

All I want is to get a simple message: "Value XXX already used"

I've tried using "Me.Combo75.Undo" also and it does not work.

Any other suggestions?

Thanks

Linda

[June7]

Interesting, never used the Column property when setting combobox to Null. But then I have my db set up so users can't even try to enter a duplicate key value. I use code that automatically generates the next value so users aren't bothered with it and I don't have to handle an error situation.


Could try Me.Undo to undo the entire record. However, few times I tried to use that didn't seem to work as expected.

[LindaRuble]

Hi June7,

"Me.Undo" did have unexpected results. Now I can't add anything eventhough there are no duplicates.

I think I have to supress a validation error. I can't capture it like an error.

Any ideas on how to do that?



Linda

[June7]

Show the expression from the Validation property. If you want to provide db for analysis, follow instructions at bottom of my post.

[LindaRuble]

Show the expression from the Validation property. If you want to provide db for analysis, follow instructions at bottom of my post.

Hello June7,

I am sending you the DB per your instructions.

Please look at Form "RTIClientTracker-Linda." The field on the form that I am having the validation issue is
"Combo75." I checked the Validation property for field "Combo75" on the Property Sheet and there is no expression for "Validation Rule."
I also checked the Validation property for the table "ClientDiv" field name "Client_Division" where form "RTIClientTracker-Linda" drop-down combo box "Combo75" is getting its data from and there is no expression for the Validation property there either.

Thanks for your help.

Linda

[June7]

Personally, I wouldn't create a form for each person, I would just have one form and use code to apply filter criteria to the form when it is opened. Have to create a new form for a new employee or if the form design needs to be changed then have to edit multiple duplicate forms. Besides, user can remove the filter using the navigation bar.

I NEVER set lookups in tables. Review http://access.mvps.org/access/lookupfields.htm

Explain relationship of ClientDiv and RTIClientTracker. Why can't there be more than one ClientID in RTIClentTracker? Right now these tables have a one-to-one relationship - could just be one table.

If you don't want to allow users to select a ClientDiv record that is already in use, then restrict the combobox to only unused IDs. Try this SQL as the RowSource for the combobox:
SELECT ClientDiv.ID, ClientDiv.Client_Division, ClientDiv.Priority, ClientDiv.Region_Div, RTIClientTracker.Client_Division
FROM ClientDiv LEFT JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((ClientDiv.MR_Assoc)="Linda") AND ((ClientDiv.Inactive)=False) AND ((RTIClientTracker.Client_Division) Is Null))
ORDER BY ClientDiv.Client_Division;

[LindaRuble]

Personally, I wouldn't create a form for each person, I would just have one form and use code to apply filter criteria to the form when it is opened. Have to create a new form for a new employee or if the form design needs to be changed then have to edit multiple duplicate forms. Besides, user can remove the filter using the navigation bar.

I NEVER set lookups in tables. Review http://access.mvps.org/access/lookupfields.htm

Explain relationship of ClientDiv and RTIClientTracker. Why can't there be more than one ClientID in RTIClentTracker? Right now these tables have a one-to-one relationship - could just be one table.

If you don't want to allow users to select a ClientDiv record that is already in use, then restrict the combobox to only unused IDs. Try this SQL as the RowSource for the combobox:
SELECT ClientDiv.ID, ClientDiv.Client_Division, ClientDiv.Priority, ClientDiv.Region_Div, RTIClientTracker.Client_Division
FROM ClientDiv LEFT JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((ClientDiv.MR_Assoc)="Linda") AND ((ClientDiv.Inactive)=False) AND ((RTIClientTracker.Client_Division) Is Null))
ORDER BY ClientDiv.Client_Division;

Hi,

Thank you for looking at my code. What I really need to know is how do I suppress the validation pop-up box when I try to add a duplicate? I can capture and suppress an error/customize an error message box but not a validation pop-up box. That is what I really need to know. Any ideas about how to accomplish that?

Thanks

Linda

[June7]

I am at a total loss on that. No idea why the validation warning is triggered. I've never had to deal with this situation.

Could make the Client_Division field the primary/foreign key instead of the autonumber ID field. That along with my suggestion for the restricted combobox RowSource should eliminate the issue.

Will every ClientDiv record eventualy have one and only one related record in RTIClientTracker? If so, why not just one table?