Customising Jet Error messages

[not_rich_yet]

Hi all,

I have a main form with a number of subforms on it each representing an appointment time during a given day and each subform adds the Client, Stylist and Cut details to the Appointment table.

Since I want to force the user to enter all of the above details, I have the relationships and 'Required' properties in the table set to allow this to happen.

Thing is, the user is unlikely to understand the Jet error message

(The Microsoft Jet database engine cannot find a record in the table <name> with key matching field(s) <name>. (Error 3101))

and I'd like to replace this with a more meaningful one. I tried playing with the SubForms 'LostFocus' property but it didn't have any effect. Also, incase the user inadvertently clicks onto a subform and then off it again, I don't want to have any message.

Can anyone offer any advice or am I easiest just explaining the error message to the user(s)?

Thanks,

nry

 

[DBL]

What I do is put something on the BeforeUpdate event of the main form so that they cannot move to the subform until they have filled out all the required fields. Something like:

If IsNull (Me.ClientName) or Me.ClientName = 0 Then
MsgBox "Please enter the client's name before proceeding"

Cancel = true 'stops the save command from running
Me.clientname.setfocus
Exit Sub
End If

I tend to do that for each of the required fields needed to allow the user to moved to the subform. That can mean quite a bit of code and I'm sure there is an easier way to do it but I like this way because I can customise the error message for each required field.

 

[not_rich_yet]

Hmm, thing is the user is never updating the main form, they are only ever updating the sub forms.....I have 18 subforms linked to the main forms Date and Stylist field if that gives people an idea of the scale?

nry

 

[DBL]

I'm a bit confused. I don't think the error message is the result of a user error. I think there is a structure problem between the form and the subform. Something somewhere is looking for the one side of a many relationship that isn't there.

 

[not_rich_yet]

Hmm, there are 4 tables in question here:

Appointment

Stylist

Client

Cut

All 4 are related to each other as follows:

1 Stylist has many Appointments
1 Client has many Appointments
1 Cut is used in many Appointments

The main form in question has 2 main bits of data: The stylist who the appointments are for and the date the appointments are for.

There is a subform for each possible appointment time during each day (eg 9:00, 9:30 etc etc). Each subform is linked (via a query) to the Stylist on the main form and the Date on the main form.

Each appointment needs to have Client and Cut filled in. When filled in, this generates a record in the appointment table containing Client, Stylist, Date, Time, Cut with Stylist and Date being pulled from the main form.

As expected, if the user does not choose a cut or a client for an appointment, the database throws up the jet error mentioned in my first posting. I would like to customise this error message or replace it with one that is a bit more user friendly. The company who this database is for require each appointment to have a cut and a client so I want an error to appear I just don't want the standard Access/Jet one....

The current message isn't due to user error directly though it is due to the user not selecting something in the subform which the database needs before it can add the record...

Hope this is a bit more explanatory!

nry

 

[Rich]

You don't need separate forms for each time slot, use a combo box to select the time.

 

[not_rich_yet]

I do need a seperate subform for each time slot since I want all possible time slots to be visible on the screen regardless of whether or not the time slot has an appointment.

The screen layout needs to look as close as possible to the current paper based appointment book which has a list per day of time slots for appointments....

I know it would have been far easier for me to have a combo box for the time but this was a requirement of the company who the system is for.

nry