creating an error message for an update query 1

[Drisconsult]

Hello All

Previously, I have been helped by the people who know what they are doing when I asked for help in providing an error message if the user enters and incorrect ID in a paramter query. I have successfully implemented the advice given to me.

Now I have a similar problem with an update query. Is it possible to have a message that informs the user that the ID is incorrect or doesn't exist, if the user has entered an incorrect ID into an update query?

Regards
Terence
London

 

[dhookom]

Can't you limit your users from entering the incorrect ID? Aren't you using a control on a form for selecting the ID?

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello dhookdom

Good to hear from you again, it appears that you are destined to be my saviour.

At the moment I have a Delete Menu where a teacher can delete a number of options, such as a whole class, a subject or a teacher.

When the user clicks on the Delete a Class option from the Delete Menu, a form pops up that gives the user an opportunity to either carry on or abandon the deletion.

If the user decides to delete a class, I have the following messages in the Criteria window of the query.

"Please Enter Class For Deletion"

The use then enters a class, such as 1A. But this procedure only activates a query, the Delete A Class query, there is no form involved at this stage (or should there be?). So if the teacher enters a class that doesn't exist, how do I create a message to that effect and return the user to the Delete Menu?

Regards
Terence
London

 

[dhookom]

You should not be using parameter prompts faq701-6763. That's what controls on forms are for. You should have a combo box that displays all classes for your user to select.

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello dhookdom

Many thanks for the advice. I will start to work on this right away.

Previously I had been given help on returning an error message when an incorrect ID or class had been entered on a Report. I have been using the following code:

Private Sub Report_NoData(Cancel As Integer)
MsgBox ("You have entered an incorrect ID/Class or the Report is empty ")
Cancel = -1
End Sub

This works very well. There is only one problem, if the user tries another ID or class name and that is incorrect as well, an error message pops up that states:

"The OpenReport action was cancelled"

How can I prevent or remove this message box?

Regards
Terence

 

[dhookom]

You can remove the last error message with error handling code in the procedure that opens the report. You error handling might look something like:

Select Case Err
   Case 2501
     'user canceled so do nothing
   Case Else
     Msgbox "Error opening report: " & err.Number & _
       vbCrLf & err.Description, vbOkOnly, "Error"
End Select

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello Dhookom

Many thanks for your help in this matter.

I am having problems with the code that you supplied. At the moment, this is the code inside the On No Data Event Procedure:

Private Sub Report_NoData(Cancel As Integer)
MsgBox ("You have entered an incorrect Class or the Report is empty ")
Cancel = -1
End Sub

This works very well but after two tries I get the error message mentioned previously.

Am I supposed to substitute this code for your code? I have tried this and the result is very strange. If I enter an incorrect class, I receive the required error message, but then the Report is loaded blank after the error message closes down.

Where have I gone astray?

Regards
Terence
London

 

[dhookom]

You missed my statement "... error handling code[red] in the procedure that opens the report[/red]". It doesn't go in the report. You should have code somewhere with the line:

DoCmd.OpenReport....

That is the module that needs the error handling.

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello dhookdom

I think my problem is that I am not yet up tp speed to be able to deal with the VBA problems I am encountering, which in turn leads to you wasting a great deal of your time dealing with me. This is the code that opens the Report:

=======================
Option Compare Database

'------------------------------------------------------------
' mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST
'
'------------------------------------------------------------
Function mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST()
On Error GoTo mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Err

DoCmd.Close acForm, "frmVIEW SUBJECT PRINT CLASS LIST"
DoCmd.Close acForm, "frmPRINT ALL SUBJECTS MENU"
DoCmd.OpenReport "rptPRINT EXAM TEACHER SUB101 LIST", acViewPreview, "", ""

mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Exit:
Exit Function

mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Err:
MsgBox Error$
Resume mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Exit

End Function
============================================

I have inserted your code in every place possible, but to no avail. Where should I have inserted the code your created for me?

Regards
Terence

 

[dhookom]

Option Compare Database

'------------------------------------------------------------
' mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST
'
'------------------------------------------------------------
Function mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST()
On Error GoTo mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Err

    DoCmd.Close acForm, "frmVIEW SUBJECT PRINT CLASS LIST"
    DoCmd.Close acForm, "frmPRINT ALL SUBJECTS MENU"
    DoCmd.OpenReport "rptPRINT EXAM TEACHER SUB101 LIST", acViewPreview, "", ""

mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Exit:
    Exit Function

mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Err:
[blue]   Select Case Err
      Case 2501
        'user canceled so do nothing
      Case Else
        Msgbox "Error opening report: " & err.Number & _
          vbCrLf & err.Description, vbOkOnly, "Error"
   End Select
[/blue]
   Resume mcrOPEN_PRINT_EXAM_TEACHER_SUB101_LIST_Exit

End Function

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello dhookdom

I am a 75 year old retired university lecturer who taught education and curriculum development for twenty years, then took up computing in the 1908s. I too loved dBASE11 and eventually dBASEV with it's wonderful compiler. I had no trouble with dBASE code but never really took to VBA.

I spent most of my career in Nigeria, Ghana, Tanzania, Uganda and Kenya. The database I have prepared is for teachers in Kenya who live in remote areas and do have access to electricty at certain times of the day.

I just wanted it to be right for them and you have provided this for me. Your solution worked beautifully.

Regards
Terence
London

 

[dhookom]

Although I'm 20+ years younger, my background is also not computers or programming (other than programming recreation and athletic events). I also did most of my early programming in dBASE II and the progressed through Clipper. I have actually replaced some FoxBase code with VBA by copying and pasting into a module and then editing. This of course doesn't take advantage many of the nice features available in Access and JET.

Glad you got things right.

Duane
Hook'D on Access
MS Access MVP

 

[Drisconsult]

Hello dhookdom

I didn't get things right, you did. I'm sure that any other amatuer like me watching this thread has derived great benefit from your expertise.

I will be back.

Sincere regards
Terence
London