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.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
