Solved

How do I stop error messages from popping up?

Posted on 2004-08-19
13
319 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:Ingx_Sub
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11842537
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11842549
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.

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11842590
For warnings related to UPDATE, INSERT, and DELETE, use DoCmd.SetWarnings to temporarily disable those prompts.
0
 

Author Comment

by:Ingx_Sub
ID: 11842640
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?
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11842751
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)
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11842772
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 27

Expert Comment

by:jjafferr
ID: 11843165
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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11843292
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11843345
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
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11843349
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.  
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 11843488
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.
0
 

Author Comment

by:Ingx_Sub
ID: 11844114
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?
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 11844384
NO, as the problem is not the file, but the contents.

Basically instead of of your 1 line, you will have:

DoCmd.TransferText acImportFixed, "NEWUSAImportSpecification", "tmpExternalReport", "Q:\SA\Text Import\09-21-00\Usa1201.txt"   'Import Txt file to temp Table
DoCmd.OpenQuery "qryAppendTempToFinal"                                        'import/convert data to proper field Number type, Double
DoCmd.OpenQuery "qryDeletetmpExternalReport"                                  'Delete Temp Table data

and continue with your Normal code.

If you think you will face any problem, maybe I can help you with these steps.

jaffer
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now