asked on

How do I stop error messages from popping up?

In Access 2000, I run an import process (you may have seen this in a previous question). Anyway, during the import, if some of the data isn't exactly right, I get an error message that says:

Microsoft Access was unable to append all the data to the table

The contents of fields in 0 records.......etc. etc. etc.

Then I'm given the Yes No or Help buttons. I always click Yes for these.

Is there a way I can code the import so that it automatically clicks Yes for this message?

Here is the import process code:

'Public Function ImportText(szcurrentdir As String) As Boolean
' Dim szfile As String, szType As String
' Dim szSep As String
' Dim GetOpenFile As String 'Dim szcurrentdir As String,
'Dim szMacro As String
'Dim sFile As String

' this better be where you want to be
'szcurrentdir = CurDir()
'MsgBox szcurrentdir

'Application.DoCmd.OpenForm

' the path separator is always "\" in DOS/Windows
szSep = "\"

' get the first file...note this call will fail if
' you are in a root...Les if you want to check the current directory
' to fix this bug be my guest
szfile = Dir(szcurrentdir & "*.txt")
szdir = szcurrentdir & szfile
'szfile = szcurrentdir & "*.txt"

'Set gfrmEDI = Form_frmSAEDI
'gfrmEDI.SetFocus

'DoCmd.TransferText acImportFixed, "USA Import Specification", "External Report", "Q:\SA\Text Import\09-21-00\Usa1201.txt"
'Application.RunCommand (acCmdSaveAs)

Do While szfile <> ""
'MsgBox (szfile)
'concatenate the selected directory and the text file in question
'GetOpenFile = szcurrentdir & szfile

If Len(szfile) > 11 Then
szType = Left$(szfile, 4)
szType = Right$(szType, 1)
'concatenate the selected directory and the text file in question
'GetOpenFile = szCurrentDir & "\" & szFile
szdir = szcurrentdir & szfile
Select Case szType
Case 5
'MsgBox "UCCL0885"
DoCmd.TransferText acImportFixed, "UCCL0885 Import Specification", "UCCL0885", szdir
Case 6
'MsgBox "UCCL0886"
DoCmd.TransferText acImportFixed, "UCCL0886 Import Specification", "UCCL0886", szdir
Case 7
'MsgBox "UCCL0887"
DoCmd.TransferText acImportFixed, "UCCL0887 Import Specification", "UCCL0887", szdir
Case 8
'MsgBox "UCCL0888"
DoCmd.TransferText acImportFixed, "UCCLO888 Import Specification", "UCCL0888", szdir
Case 9
'MsgBox "UCCL0889"
DoCmd.TransferText acImportFixed, "UCCLO889 Import Specification", "UCCL0889", szdir
Case "u"
'MsgBox "UCCL088u"
DoCmd.TransferText acImportFixed, "UCCL088u Import Specification", "UCCL088u", szdir
Case Else
MsgBox "Unrecognized file type."
End Select
Else
'MsgBox "UCCL088a"
DoCmd.TransferText acImportFixed, "UCCL088 Import Specification", "UCCL088a", szdir
End If

' get new file
szfile = Dir()

Loop
End Function

Sub FormatICD9()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
'MsgBox (Len(rs!Icd9_1))
If Len(rs!Icd9_1) = 5 Then
rs.Edit
rs!Icd9_1 = Left$(rs!Icd9_1, 3) & "." & Right$(rs!Icd9_1, 2)
rs.Update
ElseIf Len(rs!Icd9_1) = 4 Then
rs.Edit
rs!Icd9_1 = Left$(rs!Icd9_1, 3) & "." & Right$(rs!Icd9_1, 1)
rs.Update
ElseIf Len(rs!Icd9_1) = 3 Then
rs.Edit
rs!Icd9_1 = rs!Icd9_1
rs.Update
Else
rs.Edit
rs!Icd9_1 = ""
rs.Update
End If
rs.MoveNext
Loop

End Sub

Sub FormatICD9_2()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
'MsgBox (rs!Icd9_2)
If Len(rs!Icd9_2) = 5 Then
rs.Edit
rs!Icd9_2 = Left$(rs!Icd9_2, 3) & "." & Right$(rs!Icd9_2, 2)
rs.Update
ElseIf Len(rs!Icd9_2) = 4 Then
rs.Edit
rs!Icd9_2 = Left$(rs!Icd9_2, 3) & "." & Right$(rs!Icd9_2, 1)
rs.Update
ElseIf Len(rs!Icd9_2) = 3 Then
rs.Edit
rs!Icd9_2 = rs!Icd9_2
rs.Update
Else
rs.Edit
rs!Icd9_2 = ""
rs.Update
'MsgBox (rs!Icd9_2)
End If
rs.MoveNext
Loop

End Sub

Sub FormatChargeAmt()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
'MsgBox (rs!ChargedAmt)
rs.Edit
rs!ChargedAmt = rs!ChargedAmt / 100
rs.Update
rs.MoveNext
Loop

End Sub

Sub FormatPaidAmt()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
'MsgBox (rs!ChargedAmt)
rs.Edit
rs!PaidAmt = rs!PaidAmt / 100
rs.Update
rs.MoveNext
Loop

End Sub

Sub FormatAllowedAmt()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
'MsgBox (rs!ChargedAmt)
rs.Edit
rs!AllowedAmt = rs!AllowedAmt / 100
rs.Update
rs.MoveNext
Loop

End Sub

Sub FormatDates()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF

rs.Edit
rs!begin = Mid$(rs!begin, 5, 2) & "/" & Right$(rs!begin, 2) & "/" & Left$(rs!begin, 4)
rs!End = Mid$(rs!End, 5, 2) & "/" & Right$(rs!End, 2) & "/" & Left$(rs!End, 4)
rs!PaidDate = Mid$(rs!PaidDate, 5, 2) & "/" & Right$(rs!PaidDate, 2) & "/" & Left$(rs!PaidDate, 4)
rs.Update
rs.MoveNext
Loop

End Sub

Sub AddPlaceholderDate()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF

rs.Edit
rs!BeginDate = Right$(rs!begin, 4) & "/" & Left$(rs!begin, 5)
rs.Update
rs.MoveNext
Loop

End Sub

Sub Capitation()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("UCCL088a")

MsgBox ("are you sure you want to procede?")

Do Until rs.EOF
If rs!Ex_Array = "CC" Then
rs.Edit
rs!PaidAmt = rs!AllowedAmt
rs.Update
End If
rs.MoveNext
Loop

End Sub

jadedata

Greetings Ingx_Sub!

Use this as a "go-by" for error trapping in your routines.
Include Error trapping in all routines that have even the slighest possibility of throwing an error.

Public Function FunctionName

on error goto FunctionName_Err:
code
code
code

FunctionName_Exit:
on error resume next
exit function

FunctionName_Err
select case err
case 2501 ' action cancelled
resume FunctionName_Exit
case else
msgbox Error$, vbcritical+vbokonly,"ERROR"
resume FunctionName_Exit
resume
end select

end function

regards
jack

jadedata

in the case of errors in queries....

use the db.EXECUTE method to run the query. This method gives you far better control/notification over errors occuring in queries than any other method.

Steve Bink

For warnings related to UPDATE, INSERT, and DELETE, use DoCmd.SetWarnings to temporarily disable those prompts.

Ingx_Sub

ASKER

Jadedata and routinet. Doing either of these won't affect the actual importing process though...right?

I mean the process is importing hundreds of text files, and on some of them I get that error message, but I still want it to import the rest of that file. I just click Yes and the process keeps going.

So...this will continue to happen...correct?

jadedata

the sql for the import is unchanged from what you use now. only the method of executing it change... for the better I might add (IMHO)

jadedata

You should consider a loop in this process and include the writing of a PROCESS LOG to keep track of those files that imported successfully and those that didn't.

When I'm doing batch imports I also will change the name of a file that Imported successfully to take it "off the menu" for subsequent import runs. Usually I just add an "X" in front of the original file name and this will remove if from files that match a specific naming pattern.

jjafferr

Hi Ingx_Sub,

I went through what you are describing, so what I did was to divide the process in 2 parts, and
it not only works without Error messages, but it is faster too.

If I am right, then the message you are getting is from your DoCmd.TransferText acImportFixed line:
DoCmd.TransferText acImportFixed, "USA Import Specification", "External Report", "Q:\SA\Text Import\09-21-00\Usa1201.txt"

the reason is, the DoCmd.TransferText acImportFixed command is not forgiving, and it needs any reason to stop,
So what I did is the following,
A-
Do the Import part again manually, and specify all the fields as TEXT (as it is the only Data Type which will accept everything, thus NO Errors),
In the Advnce, Save As the Import specification. I don't know where Access saves "USA Import Specification", otherwise edit it and make the fields as TEXT,
Save it to a Temporary Table, say tmpExternalReport,
Now you can use DoCmd.TransferText acImportFixed, "NEWUSAImportSpecification", "tmpExternalReport", "Q:\SA\Text Import\09-21-00\Usa1201.txt" Without Errors.

B-
Table "External Report" should have the Data Type fields properly set, like Integers, Doubles , Memo, etc...,
Make an Append Query, to Append data from Temporary Table tmpExternalReport TO your Final Table "External Report",
You Should NOT be getting any errors here (at least I didn't get any), and the process will be smooth,
Make a Delete Query to Delete all the data in the Temporary Table tmpExternalReport, so that it is ready and cleam for the next import.

C-
Now you can continue your code as usual,
although you might be able to skip the Append Query and do your code on the Table tmpExternalReport, But I would advise not to, so that you don't alter your code.

Drawbacks of this method:
Since you imported to data to a Table then deleted this data, Access will NOT release the Diskspce (the mdb file size), unless you Compact and Repair,
So you might as well check/select the menu bar > Tools > Options > General > Compact on Exit, so that your mdb file goes to normal.
The file I was dealing with had 93,000+ Records and 7 columns, which made Access add 7MB each time I did the process.

I hope this helps,

jaffer
I hope this helps.

jaffer

jadedata

If you import the data into a separate MDB you don't need to be concerned with "bloat" in your production database.

IMHO always work temporary table issues in a fresh, expendable, separate database away from the production data.

jjafferr

Hmmm, since I had 2 "I hope this helps," and 2 "jaffer"

I think I better give another comment (Not anyones favorite, and I request ALL to hold their guns please), it Sendkeys

Do your error traping and On Error put a code to send an Enter key press, like this (assuming the Yes buton is the highlighted button of the message)

SendKeys "{ENTER}", False

Steve Bink

Using DoCmd.SetWarnings will only turn off the prompts for "Are you SURE you want to delete these records" or "You are about to append blahblahblah". As its name implies, it will only handle warning messages. It will not suppress actual error messages related to field values that are either invalid or required but missing. It will also not suppress any dialogs where actual input (versus just clicking a button) is required from the user. Here's the relevant section from the help file for this method:

-------------------------------------------------------------------------------------------------------------------------------------------------------------
You can use this action to prevent modal warnings and message boxes from stopping the macro. However, error messages are always displayed. Also, Microsoft Access displays any dialog boxes that require input other than just choosing a button (such as OK, Cancel, Yes, or No) — for example, any dialog box that requires you to enter text or select one of several options.

Carrying out this action with the Warnings On argument set to No has the same effect as pressing ENTER whenever a warning or message box is displayed. Typically, an OK or Yes button is chosen in response to the warning or message.
-------------------------------------------------------------------------------------------------------------------------------------------------------------

For errors related to INSERT/UPDATE/DELETE, follow jadedata's advice. The .Execute method gives you a larger degree of error trapping ability. It sounds like the error box you are encountering is for record validation failure. For example, you could be missing a value for a required field, or have a blank string in a field that does allow them. In that case, your remedies are pretty much limited to a) correcting the data (involves error-trapping and coding for validation of the problem fields), b) removing the field limitations, or c) continuing to press the button.

jjafferr

The field I was importing was a Double, but the negative sign was on the right as oppose to the normal place, the left
I started getting errors because of this,
but when I imported as Text, then append to Double , No errors, and the negative signs where correctly placed by Access.

I agree with both Jack and routinet, BUT
with the DoCmd.SetWarnings, you will have No error messages, BUT
Not all the Records will be imported, as those with the errors will be taken to the error Table,

where as in my way, Not only you don't get errors, but also you get All your Records.

Ingx_Sub

ASKER

whoa!! That's a lot of stuff. I need to sift through it.

jjaffer, the files I'm importing are already .txt files. Does that make your directions any different?

ASKER CERTIFIED SOLUTION

jjafferr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial