If, Then, Else error handling 2

[Turb]

Hi all!
I've been away from Access for a long while now, so please bear with me.
I've been searching the FAQ's and threads but cannot seem to find this answer; please pardon me if this has been answered elsewhere and feel free to point me to the correct thread.

I am using this code on an Access 97 database front-end (old, I know - but it still functions well) form, under a command button.

Private Sub Save_Button_Click()
On Error GoTo Err_Save_Button_Click

   Dim strMsg As String
    strMsg = "You MUST double check your work." & vbCrLf
    strMsg = strMsg & vbCrLf
    strMsg = strMsg & "Have you checked the information on the form for correctness?" & vbCrLf
    strMsg = strMsg & "Click Yes to continue or No to check your work."
    If MsgBox(strMsg, vbQuestion + vbYesNo, "HAVE YOU CHECKED YOUR WORK?") = vbYes Then
        DoCmd.OpenQuery "BackUpQuery"
        DoCmd.PrintOut acSelection
        DoCmd.Close acDefault
        DoCmd.RunMacro "Form1_Reopen"
    Else
        'do nothing
    End If

Exit_Save_Button_Click:
    Exit Sub

Err_Save_Button_Click:
    MsgBox Err.Description
    Resume Exit_Save_Button_Click
End Sub

I have only just recently become aware that it allows the DoCmd.PrintOut function to run even if the DoCmd.OpenQuery "BackUpQuery" function fails.
The query would fail if the data entered into the form is already in the table as in "Indexed, (Yes - No Duplicates)".
I am trying to come up with a way to halt the process if the SQL "BackUpQuery" fails.
Something like:

DoCmd.OpenQuery "BackUpQuery"
            If DoCmd.OpenQuery "BackUpQuery" = 0, Then
            'do nothing

Any help would be appreciated!!


- Turb

 

[MajP]

If the DoCmd.OpenQuery "BackUpQuery" function fails, then I see no way the printout would occur. Do you get an error message on the failure of the backupquery?

However, you should not use errors to control program flow. Instead simply check for a duplicate

Public function isDuplicate () as boolean
dim strWhere as string

strWhere = " someNumericField = " & Me.someControl & " AND someOthertextField = '" & me.someOtherControl & "' And ..."
debug.print strWhere
if dcount("someField","sometable",strWhere) > 0 then
msgbox "Record already Exists"
isduplicate = True
end if
end Function


In your main procedure
if not isDuplicate() then
DoCmd.OpenQuery "BackUpQuery"
DoCmd.PrintOut acSelection
DoCmd.Close acDefault
DoCmd.RunMacro "Form1_Reopen"
end if

 

[Turb]

MajP,
Thank you for your quick response.

No, I don't get any error message on failure of the BackUpQuery function; the EventProcedure keeps right on running and the printout runs.

It never occured to me to first check for a dupicate...
I'll give your suggestion a try and post back.

Thanks again!


- Turb

 

[Turb]

MajP,
Um... as I said, it's been awhile... where does this go? In a new database module or in a class module for the form?

Public function isDuplicate () as boolean
 dim strWhere as string


 strWhere  = " someNumericField = " & Me.someControl & " AND someOthertextField = '" & me.someOtherControl & "' And ..."
 debug.print strWhere
 if dcount("someField","sometable",strWhere) > 0 then
  msgbox "Record already Exists"
 isduplicate = True
 end if
end Function

Thanks again,


- Turb

 

[MajP]

if you plan to use
Me
as in
Me.someControl

It would have to go in the form. If use want to put in a standard module then.

Public function isDuplicate () as boolean
dim strWhere as string
dim frm as access.form
set frm = forms("yourFormName")


strWhere = " someNumericField = " & frm.someControl & " AND someOthertextField = '" & frm.someOtherControl & "' And ..."
debug.print strWhere
if dcount("someField","sometable",strWhere) > 0 then
msgbox "Record already Exists"
isduplicate = True
end if
end Function

 

[Turb]

MajP,
Ok, I've pasted this into the Form's Class Module

Public Function isDuplicate() As Boolean
Dim strWhere As String

strWhere = " SERIALNUMBER = " & Me.SERIALNUMBER & ""
Debug.Print strWhere
If DCount("SERIALNUMBER", "TestTable", strWhere) > 0 Then
   MsgBox "Record already Exists"
   isDuplicate = True
End If
End Function

And have altered my main procedure like this:

    If MsgBox(strMsg, vbQuestion + vbYesNo, "HAVE YOU CHECKED YOUR WORK?") = vbYes Then
        If Not isDuplicate() Then
            DoCmd.OpenQuery "BackUpQuery"
            DoCmd.PrintOut acSelection
            DoCmd.Close acDefault
            DoCmd.RunMacro "Form1_Reopen"
        End If
    Else
        'do nothing

But now I'm getting a message that states:
The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'ser-1055204''.


- Turb

 

[randy700]

Looks like your serial number is not numeric.
How about...

strWhere = " SERIALNUMBER = [b][red]'[/red][/b]" & Me.SERIALNUMBER & "[b][red]'[/red][/b]"

Randy

 

[MajP]

or if numeric drop the last quotes
" SERIALNUMBER = " & Me.SERIALNUMBER & ""
to
" SERIALNUMBER = " & Me.SERIALNUMBER

 

[Turb]

randy700,
Thank you for your post!
Your post actually fixed the issue and the code now works for me (since my serial numbers are alpha-numeric), but I think I'll have to give the star to MajP this time; it was his code and he took the time to work with me through this from the very beginning (I know that if he was only 4 minutes sooner on his reply, he would have had the same advice for me that you did).

Gentlemen, thank you both so very much!!



- Turb

 

[randy700]

I completely agree.

Randy

 

[Turb]

randy700,
I'm glad.

Thanks agian, my friend, for your timely help!!


- Turb

 

[Turb]

Shoot!
I've just realised that although I have solved the original problem (duplicate serial number on the form as in the table allowing the print function even though the save function fails), I have created another like it (NO serial number on the form is allowing the print function with no save).

I played some with making

If DCount("SERIALNUMBER", "TestTable", strWhere) > 0 Then

check for the empty field by this change

If DCount("SERIALNUMBER", "TestTable", strWhere) >= 0 Then

but this will not allow ANYTHING to be saved or printed...

How could I incorporate a check on the form field (something like 'ME.SERIALNUMBER > 0' maybe?) within the public function? Or am I barking up the wrong tree?

Thanks in advance!



- Turb

 

[PHV]

Replace this:
If Not isDuplicate() Then

with this:
If Not (isDuplicate() Or Trim(Me!SERIALNUMBER & "") = "") Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Turb]

PHV,
Thank you for your post!

This works beautifully! Thank you.
But... it issues no message for the halt.
Is there a way to do something like this, but have it pop up the message box from the Public Function when there is no entry?


- Turb

 

[PHV]

Why not simply this ?

If MsgBox(strMsg, vbQuestion + vbYesNo, "HAVE YOU CHECKED YOUR WORK?") = vbYes Then
  If Trim(Me!SERIALNUMBER & "") = "" Then
    MsgBox "NO Serial Number !"
  ElseIf DCount("SERIALNUMBER", "TestTable", "SERIALNUMBER='" & Me!SERIALNUMBER & "'") > 0 Then
    MsgBox "Record already Exists"
  Else
    DoCmd.OpenQuery "BackUpQuery"
    DoCmd.PrintOut acSelection
    DoCmd.Close acDefault
    DoCmd.RunMacro "Form1_Reopen"
  End If
Else
        'do nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Turb]

PHV,
I don't know; it works nicely!

Thank you!


- Turb