Override Access default message with custom messages

  • Thread starter Thread starter red6000
  • Start date Start date
R

red6000

Hi,

I have a table which is set to prevent duplicate entries and as such if the
user tried to enter duplicate data in the form they get the default warning
message:

'The changes you requested to the table were not succesful because they
would create duplicate values in the index, primary key etc etc '

Is it possible (using the Before Update event?) to surpess the default
message for my own which is more friendly and specifically tells the user
what they need to do (ie to use my 'retrieve' function that I have created).

Many thanks for any help.
 
Yes, you can use the form's BeforeUpdate event to do a DLookup, to ensure
that the record you are about to save is valid.

Alternatively, you can trap the error in the form's Error event and modify
the message.

The BeforeUpdate method is probably the more elegant solution.
 
Thanks Graham,

I'm trying the Dlookup in the beforeupdate event and its wort of working but
not quite as I intended.

If I'm trying to add a duplicate record then it works fine, but the before
update is then not allowing me to amend an existing record.

Any ideas

My current code is:

Dim Dup As Integer
On Error Resume Next
Dup = DLookup("DuplicateCheck", "DuplicateCheck")
If Dup > 0 Then
MsgBox ("A timesheet record already exists for this CSTR and Date." &
Chr(13) & Chr(13) & "Please retrieve the existing record an amend your hours
worked there.")
Exit Sub
End If

Where DuplicateCheck is a query that counts up records. I had to use the on
error resume next to allow it to proceed when my query return NULL records.





Graham Mandeno said:
Yes, you can use the form's BeforeUpdate event to do a DLookup, to ensure
that the record you are about to save is valid.

Alternatively, you can trap the error in the form's Error event and modify
the message.

The BeforeUpdate method is probably the more elegant solution.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

red6000 said:
Hi,

I have a table which is set to prevent duplicate entries and as such if
the user tried to enter duplicate data in the form they get the default
warning message:

'The changes you requested to the table were not succesful because they
would create duplicate values in the index, primary key etc etc '

Is it possible (using the Before Update event?) to surpess the default
message for my own which is more friendly and specifically tells the user
what they need to do (ie to use my 'retrieve' function that I have
created).

Many thanks for any help.
Click to expand...
Click to expand...
 
Firstly use Nz
Dup = Nz(DLookup("DuplicateCheck", "DuplicateCheck"),0)

Seccondly set:
Cancel = True
before the exit sub

HTH

Pieter




red6000 said:
Thanks Graham,

I'm trying the Dlookup in the beforeupdate event and its wort of working
but not quite as I intended.

If I'm trying to add a duplicate record then it works fine, but the before
update is then not allowing me to amend an existing record.

Any ideas

My current code is:

Dim Dup As Integer
On Error Resume Next
Dup = DLookup("DuplicateCheck", "DuplicateCheck")
If Dup > 0 Then
MsgBox ("A timesheet record already exists for this CSTR and Date." &
Chr(13) & Chr(13) & "Please retrieve the existing record an amend your
hours worked there.")
Exit Sub
End If

Where DuplicateCheck is a query that counts up records. I had to use the
on error resume next to allow it to proceed when my query return NULL
records.





Graham Mandeno said:
Yes, you can use the form's BeforeUpdate event to do a DLookup, to ensure
that the record you are about to save is valid.

Alternatively, you can trap the error in the form's Error event and
modify the message.

The BeforeUpdate method is probably the more elegant solution.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

red6000 said:
Hi,

I have a table which is set to prevent duplicate entries and as such if
the user tried to enter duplicate data in the form they get the default
warning message:

'The changes you requested to the table were not succesful because they
would create duplicate values in the index, primary key etc etc '

Is it possible (using the Before Update event?) to surpess the default
message for my own which is more friendly and specifically tells the
user what they need to do (ie to use my 'retrieve' function that I have
created).

Many thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Pieter is right about setting Cancel=True. This is how you signal to the
form that the record is not valid and no further attempt should be made to
update it.

I just want to say that it's not usually necessary to create a query
specially for this. You can just do a DLookup specifying the required
criteria right in the code. Also, it may be easier to use DCount instead of
DLookup. For example:

If DCount ("*", "YourTable", "[CSTR]=" & Me!CSTR & " and [DateField]=" _
& Format(Me![DateField], "\#mm/dd/yyyy\#") ) > 0 then
MsgBox ...
Cancel = True
End If

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

red6000 said:
Thanks Graham,

I'm trying the Dlookup in the beforeupdate event and its wort of working
but not quite as I intended.

If I'm trying to add a duplicate record then it works fine, but the before
update is then not allowing me to amend an existing record.

Any ideas

My current code is:

Dim Dup As Integer
On Error Resume Next
Dup = DLookup("DuplicateCheck", "DuplicateCheck")
If Dup > 0 Then
MsgBox ("A timesheet record already exists for this CSTR and Date." &
Chr(13) & Chr(13) & "Please retrieve the existing record an amend your
hours worked there.")
Exit Sub
End If

Where DuplicateCheck is a query that counts up records. I had to use the
on error resume next to allow it to proceed when my query return NULL
records.





Graham Mandeno said:
Yes, you can use the form's BeforeUpdate event to do a DLookup, to ensure
that the record you are about to save is valid.

Alternatively, you can trap the error in the form's Error event and
modify the message.

The BeforeUpdate method is probably the more elegant solution.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

red6000 said:
Hi,

I have a table which is set to prevent duplicate entries and as such if
the user tried to enter duplicate data in the form they get the default
warning message:

'The changes you requested to the table were not succesful because they
would create duplicate values in the index, primary key etc etc '

Is it possible (using the Before Update event?) to surpess the default
message for my own which is more friendly and specifically tells the
user what they need to do (ie to use my 'retrieve' function that I have
created).

Many thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top