Customizing Error Message

[Guest]

Using Access 97. Users are entering patients by their (OHC#). Required
field and no duplicates are allowed.

Problem: They don't know if OHC# is a duplicate until they have completed
entering the whole record and then move to next record. They get error
message #3314. Basically, telling them that there is a duplicate. Now they
have to find the original using the a different method.

I would like to have a code that runs after update on the OHC# to make this
a little easier. The code would force a save, capture the error (if any). I
would like to capture the error message (#3314) and substitute my own.
Something like "You have entered a duplicate OHC#. Do you wish to find this
patient now?" If they click OK then I would like the code to take the OHC#
they entered and find this record and return it to them.

I know what I want it to do but I am not sure how to capture the error
message and whether putting it on the after update event on the OHC# field is
the best place.

Appreciate all your input. It has been very helpful in the past.

 

[Guest]

One suggestion would be to put code like the following in the before update
event of the OHC #

Dim VarOHC as variant
Dim Response as integer
Dim sqlst As String
Dim con, rs As Object

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

sqlst = "SELECT * from yourtable where OHC# = " & Me![OHC#]

rs.Open sqlst, con, 1

If not rs.eof then
response = msgbox("Duplicate OHC#. Do you want to go to original
record?",vbYesNo)
If response = vbyes then
cancel = true
Me![OCH#].setfocus
Docmd.findfirst rs![ohs#]
end if
end if
rs.close
set rs = nothing

Not tested, but should get you close

 

[fredg]

Using Access 97. Users are entering patients by their (OHC#). Required
field and no duplicates are allowed.

Problem: They don't know if OHC# is a duplicate until they have completed
entering the whole record and then move to next record. They get error
message #3314. Basically, telling them that there is a duplicate. Now they
have to find the original using the a different method.

I would like to have a code that runs after update on the OHC# to make this
a little easier. The code would force a save, capture the error (if any). I
would like to capture the error message (#3314) and substitute my own.
Something like "You have entered a duplicate OHC#. Do you wish to find this
patient now?" If they click OK then I would like the code to take the OHC#
they entered and find this record and return it to them.

I know what I want it to do but I am not sure how to capture the error
message and whether putting it on the after update event on the OHC# field is
the best place.

Appreciate all your input. It has been very helpful in the past.

Here's how you can find the correct error and show your own message
for any of the form level errors.

First code the Form's Error event:

MsgBox "Error#: " & DataErr ' Display the error number
Response = acDataErrDisplay ' Display Default message

Then open the form and intentionally make that error.

The message box will display the error number and the default error
message.

Next, go back to the error event and change that code to:

If DataErr = XXXX Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "You entered a value that already exits in the table."
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If

where XXXX is the error number.

 

[Guest]

Thanks for this code but it is getting stuck at

rs.Open sqlst, con, 1

The error message I get is 3001 Arguments are wrong type.

The OHC# field is actually a number so I changed the type to integer.

Dim sqlst As Integer

and then I get a different error messsage... Error 13 : Type mismatch and
the code actually stops at

sqlst = "SELECT * from Cardiac Rehab Clients where OHC# = " & Me![OHC#]

Okay, what am I missing?

J.


--
Thanks, J.

One suggestion would be to put code like the following in the before update
event of the OHC #

Dim VarOHC as variant
Dim Response as integer
Dim sqlst As String
Dim con, rs As Object

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

sqlst = "SELECT * from yourtable where OHC# = " & Me![OHC#]

rs.Open sqlst, con, 1

If not rs.eof then
response = msgbox("Duplicate OHC#. Do you want to go to original
record?",vbYesNo)
If response = vbyes then
cancel = true
Me![OCH#].setfocus
Docmd.findfirst rs![ohs#]
end if
end if
rs.close
set rs = nothing

Not tested, but should get you close

Using Access 97. Users are entering patients by their (OHC#). Required
field and no duplicates are allowed.

Problem: They don't know if OHC# is a duplicate until they have completed
entering the whole record and then move to next record. They get error
message #3314. Basically, telling them that there is a duplicate. Now they
have to find the original using the a different method.

I would like to have a code that runs after update on the OHC# to make this
a little easier. The code would force a save, capture the error (if any). I
would like to capture the error message (#3314) and substitute my own.
Something like "You have entered a duplicate OHC#. Do you wish to find this
patient now?" If they click OK then I would like the code to take the OHC#
they entered and find this record and return it to them.

I know what I want it to do but I am not sure how to capture the error
message and whether putting it on the after update event on the OHC# field is
the best place.

Appreciate all your input. It has been very helpful in the past.

 

[Guest]

Dim sqlst as string

sqlst = "SELECT * from [Cardiac Rehab Clients] where [OHC#] = " & Me![OHC#]

Try these changes. With the spaces in the name and # the brackets are
needed. The sqlst needs to be a string.

Hope this helps

Thanks for this code but it is getting stuck at

rs.Open sqlst, con, 1

The error message I get is 3001 Arguments are wrong type.

The OHC# field is actually a number so I changed the type to integer.

Dim sqlst As Integer

and then I get a different error messsage... Error 13 : Type mismatch and
the code actually stops at

sqlst = "SELECT * from Cardiac Rehab Clients where OHC# = " & Me![OHC#]

Okay, what am I missing?

J.


--
Thanks, J.

One suggestion would be to put code like the following in the before update
event of the OHC #

Dim VarOHC as variant
Dim Response as integer
Dim sqlst As String
Dim con, rs As Object

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

sqlst = "SELECT * from yourtable where OHC# = " & Me![OHC#]

rs.Open sqlst, con, 1

If not rs.eof then
response = msgbox("Duplicate OHC#. Do you want to go to original
record?",vbYesNo)
If response = vbyes then
cancel = true
Me![OCH#].setfocus
Docmd.findfirst rs![ohs#]
end if
end if
rs.close
set rs = nothing

Not tested, but should get you close

Using Access 97. Users are entering patients by their (OHC#). Required
field and no duplicates are allowed.

Problem: They don't know if OHC# is a duplicate until they have completed
entering the whole record and then move to next record. They get error
message #3314. Basically, telling them that there is a duplicate. Now they
have to find the original using the a different method.

I would like to have a code that runs after update on the OHC# to make this
a little easier. The code would force a save, capture the error (if any). I
would like to capture the error message (#3314) and substitute my own.
Something like "You have entered a duplicate OHC#. Do you wish to find this
patient now?" If they click OK then I would like the code to take the OHC#
they entered and find this record and return it to them.

I know what I want it to do but I am not sure how to capture the error
message and whether putting it on the after update event on the OHC# field is
the best place.

Appreciate all your input. It has been very helpful in the past.