Capture system message

Topic

New Reply

Hi is there a more intuitive way I can display thee standard “The changes you requested to the table were not successful because they would create duplicate values in the index” replaced with a user message of my own?

Thanks, Darren.

Reply | Quote

Replies

You cannot do this at the table or query level, but you can use the On Error event of the form used for data entry. The error number of the duplicate index error is 3022.

For example, if there is a field ID that must be unique:

Code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Select Case DataErr
	Case 3022 ' duplicate index
	  ' Activate offending control
	  Me.ID.SetFocus
	  ' Display custom message
	  MsgBox "You have entered a duplicate ID value, you dummy!", _
		vbExclamation
	  ' Suppress built-in error message
	  Response = acDataErrContinue
	Case Else
	  ' Display built-in error message
	  Response = acDataErrDisplay
  End Select
End Sub

Reply | Quote

Hi is there a more intuitive way I can display thee standard “The changes you requested to the table were not successful because they would create duplicate values in the index” replaced with a user message of my own?

Thanks, Darren.

I do this in two ways:

[*]Use the Before Update event of the form to count whether any records already exist that would lead to a duplicate. If so display your own message, then Cancel the update, or
[*]If I am using combo boxes, only offer values that have not already been used, so that you can’t use values that would lead to duplicates.

Reply | Quote

Thanks guys, both most helpful. John I am intruiged with your combo box solution. What sort of code would I use to do this?

Cheers, Darren.

Reply | Quote

In an earlier post I asked if there wa sa way of trapping the index duplicate keys message that shows and I was givne this reply by John H…

I do this in two ways:

“Use the Before Update event of the form to count whether any records already exist that would lead to a duplicate. If so display your own message, then Cancel the update, or
If I am using combo boxes, only offer values that have not already been used, so that you can’t use values that would lead to duplicates.”

This looks cool but I am unsure how to code. Any guiance would be great.

Thanks, Darren

Reply | Quote

In an earlier post I asked …

I have moved your new topic into the original topic because it’s a follow-up question.

See the attached sample database. Both approaches are demonstrated in frmDemo.

The Before Update event of the form tests whether the value entered for UniqueNumber hasn’t already been used:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If DCount("*", "tblTest", "UniqueNumber = " & _
	  Me.UniqueNumber & " AND ID  " & Me.ID) > 0 Then
	Me.UniqueNumber.SetFocus
	MsgBox "The value " & Me.UniqueNumber & _
	  " has already been used.", vbExclamation
	Cancel = True
  End If
End Sub

The combo box for UniqueLookup has a row source that only displays available values:

SELECT ID, Description FROM tblLookup WHERE ID Not In (SELECT UniqueLookup FROM tblTest) OR ID = Forms!frmDemo!UniqueLookup

The combo box is requeried when the user moves to another record:

Code:

Private Sub Form_Current()
  Me.UniqueLookup.Requery
End Sub

Reply | Quote

As ever Hans, most obliged.

Darren.

Reply | Quote