Custom Error message 3022

[jdraw]

I looked at your database --not sure what I'm suppose to do to repeat the error.
I try adding a record and get error 438 Object doesn'r support this property or method?????

If i try to copy a record directly in the table Access tells me about a duplication in key ....

We need instructions on what to do to enter a duplicate record via your form...............................

More checking update:
If, when using the form, I cancel out of the error 438, I get the custom error 3022.
see attached

 

[jamesgtierney]

I just need the custom error message 3022 to say " this appointment is already taken"
in stead of the default access one.
if I could get this to work my troubles would be over.
I am totally lost.

 

[jdraw]

Please see my previous post=== I updated it while you were posting.

So we have to find out what object and what property or method is involved.

Perhaps sneuberg can look as well.

I looked at your relationships, you have tblTime linked to tblAppointments with
TreatmentId linked to TimeID??????????????????????????????

This doesn't sound correct.

 

[sneuberg]

This line

MsgBox Err.Num & " " & Err.Description.

Should be

MsgBox Err.Number & " " & Err.Description

But since this table has an Autonumber a duplicate record shouldn't be occurring anyway. This form needs a lot of work. You need to make most of those text boxes combo boxes with row sources from the applicable table. I'd get that put together before trying to add the error handlers.

 

[jamesgtierney]

when I use the button now i get an error as in screenshot


James

 

[sneuberg]

when I use the button now i get an error as in screenshot


James

If you fix the problem I identified in post 24 that will go away, but you'll probably be getting the nebulous "Can't to to record" error then. You need combo boxes so the related data in this form is entered correctly,i.e., restricted to the values in the related tables.

 

[jamesgtierney]

I understand what your saying and i am going to work on the database to make it look professional.
Its just i was so please when i got the double booking working i wanted to change the error mess to a custom one.

After changing the line you suggested I am getting a response as in the new screenshot.

is this because of this piece of code
Response = 0

James

 

[jdraw]

I change err.num to err.number, then tried to add a duplicate record. Now I get error 2105 can not go to specified record.

It seems you are filling in a form, and then when you click the button to go to new record a series of errors are identified
--you can't save the record because it violates some index
--you can't go to a new record because the info you provided causes a problem
--the record you attempted to add <---your custom message

You may want to adjust the code to (instead of goto newrec)
(in before update of the form event)
fill in your form
validate the data
check for potential duplicate and if found give custom error
if validated and no duplicate then execute (an insert query or the sql directly using values from the form.

 

[jamesgtierney]

I really appreciate you guys taking the time to respond to this.
I know I am a complete newbie and this is a simple database.
Its probably so simple that i feel stupid.

I am sorry.

 

[sneuberg]

Please see my previous post=== I updated it while you were posting.

So we have to find out what object and what property or method is involved.

Perhaps sneuberg can look as well.

I looked at your relationships, you have tblTime linked to tblAppointments with
TreatmentId linked to TimeID??????????????????????????????

This doesn't sound correct.

Looks weird too. That's got to be a mistake. Obviously the intent must have been to link the TimeID of the tblAppointments to the TimeId of the tblTime. As it is to get a record in tblAppoints the TreatmentID would have to be in both the tblTime and the tblTreatments.

 

[jamesgtierney]

The shop is only open from 9.00 am to 9.00pm so instead of using the time function of access I have set up a simple entity for time.
I thought this was the best way.
the database works with this.
Its just this error message.
I can get it to work but as soon as i add the buttons for nav it doesnt use my custom message.
i thought it would be simple but I am such a newbie i am perplexed.

James

 

[jdraw]

I'm finding the use of Lookups at table field level.
I can't create a relationship between TimeID and [Time Id] because of datatype differences.

I do not use nor encourage the use of Lookup fields at table field level.

Access provides a date/time data type for Dates and Time all in one field.

In my copy I deleted the lookup, I made up some TimeId data for tblAppoinments in order to create the relationship between tblTime and tblAppointments.

Since you have used a number of Lookups at the table field level, I really don't want to change your underlying structure since you have a deadline. In my view you have taken some design approaches that will/could cause you issues.

 

[sneuberg]

I change err.num to err.number, then tried to add a duplicate record. Now I get error 2105 can not go to specified record.

It seems you are filling in a form, and then when you click the button to go to new record a series of errors are identified
--you can't save the record because it violates some index
--you can't go to a new record because the info you provided causes a problem
--the record you attempted to add <---your custom message

You may want to adjust the code to (instead of goto newrec)
(in before update of the form event)
fill in your form
validate the data
check for potential duplicate and if found give custom error
if validated and no duplicate then execute (an insert query or the sql directly using values from the form.

If you delete the bogus relationship that error goes away and you can add a record but you get messages from the error handler. The exit needs to be move up. The code should be:

Private Sub Command16_Click()

On Error GoTo Err_Command16_Click

DoCmd.GoToRecord , , acNewRec

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
If Err.Number = 3022 Then
    MsgBox "This appointment has already been taken, Please choose another."
    Resume Exit_Command16_Click
Else
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Command16_Click
End If

End Sub

 

[jamesgtierney]

i have changed it as per your suggestion
but now it doesnt add the record

this is the code now
Option Compare Database

Private Sub Command16_Click()

On Error GoTo Err_Command16_Click

DoCmd.GoToRecord , , acNewRec

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
If Err.Number = 3022 Then
MsgBox "This appointment has already been taken, Please choose another."
Resume Exit_Command16_Click
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Command16_Click
End If

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)


If DataErr = 3022 Then
MsgBox ("This appointment has already been taken, Please choose another.")
Response = 0
End If

End Sub



should i just leave the standard error message or is there anything i can do

James

 

[jamesgtierney]

this is the original code before i add the buttons



Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
MsgBox ("You have violated the primary key.")
Response = 0
End If

End Sub

James

 

[sneuberg]

i have changed it as per your suggestion
but now it doesnt add the record

I'm pretty sure your problem now is the relationship between tblTime and tblAppointment. If you delete that relationship for now I think you'll be able to add records as long as the other related data such as customer id is correct.

I get you some specifics on how to fix the relationship soon.

 

[jamesgtierney]

I've went back to the beginning


Option Compare Database

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox ("This appointment has already been booked")
Response = 0
End If
End Sub

This works if I add an appointment.

What do I do if I want to add a add new record button.
Is there a simple solution?

james

 

[sneuberg]

I've went back to the beginning


Option Compare Database

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox ("This appointment has already been booked")
Response = 0
End If
End Sub

This works if I add an appointment.

What do I do if I want to add a add new record button.
Is there a simple solution?

james

I don't know the Add button is working on my system if I put in the correct related data.

I now see, should have read jdraw's response more closely, that you have a lookup for the time in the tblAppointment. So you just need to delete the relationship between the tblTime and tblAppointments.

 

[jamesgtierney]

I've Started from scratch
coded the buttons and got the error message to work but am having problems with the button navagation.

Here is the code:

Private Sub cmdBack_Click()
On Error Resume Next
DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub cmdNew_Click()
On Error Resume Next
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub cmdNext_Click()
On Error Resume Next
DoCmd.GoToRecord , , acNext
End Sub

Private Sub Form_Current()
On Error Resume Next
If Me.CurrentRecord = 1 Then
Me.cmdBack.Enabled = False
Me.cmdFirst.Enabled = False
Else
Me.cmdBack.Enabled = True
Me.cmdFirst.Enabled = True
End If
If Me.CurrentRecord = Me.Recordset.RecordCount Then
Me.cmdLast.Enabled = False
Else
Me.cmdLast.Enabled = True
End If
If Me.CurrentRecord >= Me.Recordset.RecordCount Then
Me.cmdNext.Enabled = False
Else
Me.cmdNext.Enabled = True
End If
If Me.NewRecord Then
Me.cmdNew.Enabled = False
Else
Me.cmdNew.Enabled = True
End If
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
MsgBox ("This appointment has already been taken, Please choose another.")
Response = 0
End If
End Sub

 

[sneuberg]

Your code appear to be from http://www.fontstuff.com/access/afmc01.htm

Seem like it should work. What problems are you having?