Find the Qurey's Error.

[Scott Burke]

Here is some code:
DoCmd.SetWarnings False
.......
Call PPS_import([FILENM], 452, "Import UMC")
DoCmd.OpenQuery "fixPPS"
DoCmd.OpenQuery "fixPPS2"
DoCmd.OpenQuery "AppendPPSToImportTable"

The Problem:
PPS has a tendency to change the record lenght and they tend to put garbage
in the imput file. Record lenght should be 450 bytes, The garbage is
actually the command used to generate the file???? The garbage tends to be in
records 48 & 49.
Trust me, the PPS IT person does NOT know how to fix these problems.

The Ideal:
Take note, If PPS made a mistake then query "FIXPPS" will be the only query
that will give an error. IS is possible to lookup the "FIXPPS" query
results and see if it had an error? IF it did then I wont to open up a
special window that displays the "IMPORT TABLE". That way clearance
department can see if it just two bad records or the whole file that is bad.
Maybe give them a chance to delete the bad records...... Maybe.

I really don't wont the "error window" to open unless there is an error.

What do you think?
Scott Burke

 

[John Vinson]

Here is some code:
DoCmd.SetWarnings False
.......
Call PPS_import([FILENM], 452, "Import UMC")
DoCmd.OpenQuery "fixPPS"
DoCmd.OpenQuery "fixPPS2"
DoCmd.OpenQuery "AppendPPSToImportTable"

The Problem:
PPS has a tendency to change the record lenght and they tend to put garbage
in the imput file. Record lenght should be 450 bytes, The garbage is
actually the command used to generate the file???? The garbage tends to be in
records 48 & 49.
Trust me, the PPS IT person does NOT know how to fix these problems.

The Ideal:
Take note, If PPS made a mistake then query "FIXPPS" will be the only query
that will give an error. IS is possible to lookup the "FIXPPS" query
results and see if it had an error? IF it did then I wont to open up a
special window that displays the "IMPORT TABLE". That way clearance
department can see if it just two bad records or the whole file that is bad.
Maybe give them a chance to delete the bad records...... Maybe.

Souns to me like you should revise the code in PPS_import (which, of
course, we cannot see) to parse the input file as it imports it.

Failing that, you can use the querydef's Execute method - which lets
you trap errors. Try

On Error GoTo Proc_Error
Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim strWhere As String
........
Call PPS_import([FILENM], 452, "Import UMC")
Set db = CurrentDb
Set qd = db.Querydefs("fixPPS")
strWhere = "fixPPS"
qd.Execute dbFailOnError
Set qd = db.Querdefs("fixPPS2")
strWhere = "fixPPS2"
qd.Execute dbFailOnError
Set qd = db.Querydefs("AppendPPSToImportTable")
strWhere = "AppendPPSToImportTable"
qd.Execute dbFailOnError
.... <rest of your code>
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case <whatever> ' figure out what errors you want to trap
Select Case strWhere
Case "fixPPS"
<do something appropriate for error in fixPPS>
Case "fixPPS2"
<etcetera>
End Select
Case Else
MsgBox "Error " & Err.Number & " in PPS import:" _
& vbCrLf & Err.Description
End Select
Resume Proc_Exit
End Sub


John W. Vinson[MVP]
John W. Vinson[MVP]

 

[Scott Burke]

Hi John. Thanks for your imput. I will give this a try. However I do have
some quesitons.
1) is there only room for 1 error code or do you like to trap everything you
do one command at a time?

2) qd.Execute dbFailOnError. Is this what does the traping or does this
retreive the error and send you to "On Error GoTo Proc_Error"?

It is my first time error traping.
Scott Burke

 

[John Vinson]

Hi John. Thanks for your imput. I will give this a try. However I do have
some quesitons.
1) is there only room for 1 error code or do you like to trap everything you
do one command at a time?

That's what the Select Case lets you do. If you're running into errors
5110, 3055, 2258 you can put a Case 5110 line with the code for that
error, followed by a Case 3055 with code for that error - no limit
basically.

2) qd.Execute dbFailOnError. Is this what does the traping or does this
retreive the error and send you to "On Error GoTo Proc_Error"?

It causes the Error condition to be raised when the query is run; this
condition triggers the Goto Proc_Error to occur.

It is my first time error traping.

If you're serious about VBA... you should have error trapping in EVERY
nontrivial (read: more than one line) Sub and Function.

John W. Vinson[MVP]