RE: [AccessDevelopers] Anybody see a reason why this shouldn't work???????????

Tue, 14 Jun 2005 13:57:23 -0700

Try to pinpoint where in this code your error is occuring.  Set your error-handling to "Break On All Errors" and run it again.  Let us know which line it's breaking on. 
 

Tom Oakes
Personal PC Consultants, Inc.
[EMAIL PROTECTED]
503.230.0911 (O)
402.968.6946 (C)
713.583.7091 (F)



From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of les_claypoo1
Sent: Tuesday, June 14, 2005 1:49 PM
To: [email protected]
Subject: [AccessDevelopers] Anybody see a reason why this shouldn't work???????????

When I click on this DB it gives me the error message 'There was a
problem sending the command to the program.'  It works fine if I
hold down shift, open up the database, and then click on the form. 
In startup I have it set to load the form with this code when the DB
is opened.   Any ideas?????

--------------------------------------------------------------------
Option Compare Database

Private Sub Form_Load()

On Error GoTo Form_Load_Err
  
    'Turn warnings off.
    DoCmd.SetWarnings False

    Dim db As Database, rec As Recordset
    Dim intNullCount As Integer, strSQL As String
   
    'Calls the CreatePrimaryKeys function, which creates Primary and
Foreign Keys.
    Call CreatePrimaryKeys
   
    'Create error message and the error table
    DoCmd.OpenQuery "QryMakeError", acNormal, acReadOnly
    DoCmd.OpenQuery "QryNoMatchingTrace", acNormal, acReadOnly
   
    'SQL that will find all the records that don't have a matching
trace number.
    strSQL = "SELECT qryResults.[Report Date] AS Nulls FROM
qryResults WHERE ((qryResults.[Report Date2]) Is Null);"
   
    'Open the current database.
    Set db = CurrentDb()
   
    'Setup the record set with all accounts with no matching trace
number.
    Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Do Until rec.EOF
        rec.MoveNext
    Loop
    'Count the records in the recordSet.
    intNullCount = rec.RecordCount
   
    'Close the recordSet.
    rec.Close
   
    'Open the query that creates a transend file in a table called
traces
    DoCmd.OpenQuery "QryTransendFile", acNormal, acReadOnly
   
    'Close the query
    'DoCmd.Close acQuery, "QryTransendFile"
   
    'Converts the 'traces' table to a .csv and exports it if there
are no nulls export Traces
    Select Case intNullCount
    Case 0
        DoCmd.TransferText
acExportDelim, "", "Traces", "D:\MonarchExport\DDA-ODD DECISION
RETURNS(01)\DDA-ODD DECISION RETURNS(01)(ACH).csv", False, "", 20127
    Case 1 To 10
        DoCmd.TransferText
acExportDelim, "", "Traces", "D:\MonarchExport\DDA-ODD DECISION
RETURNS(01)\DDA-ODD DECISION RETURNS(01)(ACH).csv", False, "", 20127
        DoCmd.TransferText
acExportDelim, "", "Error_Accounts", "D:\MonarchExport\DDA-ODD
DECISION RETURNS(01)\DDA-ODD DECISION RETURNS(01)(ERRORS).csv",
True, "", 20127
    Case Else
        DoCmd.TransferText
acExportDelim, "", "Error_Message", "D:\MonarchExport\DDA-ODD
DECISION RETURNS(01)\DDA-ODD DECISION RETURNS(01)(ERRORS).csv",
False, "", 20127
    End Select
   
    'Exit the form
    DoCmd.Close acForm, "frmAutoRun"
   
    'Exit Access DB
    DoCmd.Quit acExit
   
Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
   
End Sub

Function CreatePrimaryKeys()

    Dim db As Database
    Dim tblAccount As TableDef, tblTrace As TableDef
    Dim idxAccount As Index, idxTrace As Index
    Dim idxAccountAmount As Index, idxTraceAmount As Index
    Dim fldAccount As Field, fldTrace As Field
    Dim fldAccountAmount As Field, fldTraceAmount As Field
      
    'Start by opening the database.
    Set db = CurrentDb()
   
    'Create a tabledef object.
    Set tblAccount = db.TableDefs("Account_Numbers")
    Set tblTrace = db.TableDefs("Trace_Numbers")
   
    'Create primary key index.
    Set idxAccount = tblAccount.CreateIndex("PrimaryKey")
    Set idxTrace = tblTrace.CreateIndex("PrimaryKey")
    Set fldAccount = idxAccount.CreateField("AutoID", dbLong)
    Set fldTrace = idxTrace.CreateField("AutoID", dbLong)
   
    'Create foreign key index.
    Set idxAccountAmount = tblAccount.CreateIndex("ForeignKey")
    Set idxTraceAmount = tblTrace.CreateIndex("ForeignKey")
    Set fldAccountAmount = idxAccountAmount.CreateField
("Account/Amount", dbText)
    Set fldTraceAmount = idxTraceAmount.CreateField
("Account/Amount", dbText)
   
    'Append index fields.
    idxAccount.Fields.Append fldAccount
    idxTrace.Fields.Append fldTrace
    idxAccountAmount.Fields.Append fldAccountAmount
    idxTraceAmount.Fields.Append fldTraceAmount
   
    'Set Primary property.
    idxAccount.Primary = True
    idxTrace.Primary = True
   
    'Append indexes if there aren't already indexes set
    If tblAccount.Indexes.Count = 0 Then
        tblAccount.Indexes.Append idxAccount
        tblAccount.Indexes.Append idxAccountAmount
    End If
    If tblTrace.Indexes.Count = 0 Then
        tblTrace.Indexes.Append idxTrace
        tblTrace.Indexes.Append idxTraceAmount
    End If

End Function






Please zip all files prior to uploading to Files section.




Please zip all files prior to uploading to Files section.


Yahoo! Groups Links

Reply via email to