Solved: you canceled the previous operation

[mpearce]

Sorry for the length of this code, but when the below code runs everything up to the export routine is run. when the export routine is run i get:

run-time error '2001'
you canceled the previous operation.

I am not sure why i am getting that error. Here is my code:

Option Compare Database
Option Explicit


Private Sub btnImport_Click()
Dim objdialog As Variant
Dim objdialog2 As Variant
Dim intresult As Integer
Dim yestreferral As String
Dim todayreferral As String
Dim intresult2 As Integer
Dim refdate As String
Dim ech_records As Integer
Dim ermc_records As Integer
Dim mmc_records As Integer
Dim mhh_records As Integer
Dim stbhc_records As Integer

Set objdialog = CreateObject("UserAccounts.CommonDialog")
Set objdialog2 = CreateObject("UserAccounts.CommonDialog")
Call open_(objdialog, objdialog2, intresult, yestreferral, todayreferral, intresult2)
Call deleteReferrals
Call import(yestreferral, todayreferral)
Call export(refdate, ech_records, ermc_records, mmc_records, mhh_records, stbhc_records)
End Sub
Sub open_(objdialog As Variant, objdialog2 As Variant, intresult As Integer, ByRef yestreferral As String, ByRef todayreferral As String, intresult2 As Integer)

objdialog.Filter = "Text Files|*.txt|All Files|*.*"
objdialog.InitialDir = "x:\uhs south tx\referrals"
objdialog2.Filter = "Text Files|*.txt|All Files|*.*"
objdialog2.InitialDir = "x:\uhs south tx\referrals"


Do While intresult = 0
intresult = objdialog.ShowOpen
MsgBox "Select a file to continue", vbCritical, "!!ERROR!!"
Loop
yestreferral = objdialog.FileName
MsgBox yestreferral

Do While intresult2 = 0
intresult2 = objdialog2.ShowOpen
MsgBox "Select a file to continue", vbCritical, "!!ERROR!!"
Loop
todayreferral = objdialog2.FileName
MsgBox todayreferral

End Sub

Sub deleteReferrals()
DoCmd.RunSQL "DELETE UHS Referrals Yesterday.* FROM [UHS Referrals Yesterday];"
DoCmd.RunSQL "DELETE UHS Referrals Today.* FROM [UHS Referrals Today];"
End Sub
Sub import(yestreferral As String, todayreferral As String)

DoCmd.TransferText acImportDelim, "UHS import specification", "UHS Referrals Yesterday", yestreferral, -1
DoCmd.TransferText acImportDelim, "UHS import specification", "UHS Referrals Today", todayreferral, -1
End Sub

Sub export(ByVal refdate As String, ByVal ech_records As Integer, ByVal ermc_records As Integer, ByVal mmc_records As Integer, ByVal mhh_records As Integer, ByVal stbhc_records As Integer)
refdate = InputBox("Enter the date that these referrals were received in the format MM-DD-YYYY", "Referral Date", "MMDDYYYY")

'here is where the problem starts
ech_records = DCount("*", "ECH")
Select Case ech_records
Case Is > 0
MsgBox refdate
DoCmd.TransferText acExportDelim, "UHS Export Specification", "ECH", "X:\UHS South TX\Referrals\ready for import\ECH " & refdate & ".txt", False
Case Is < 1
DoCmd.TransferText acExportDelim, "UHS Export Specification", "ECH", "X:\UHS South TX\Referrals\Files with no referrals\ECH no referral " & refdate & ".txt"
End Select

ermc_records = DCount("*", "ERMC")
Select Case ermc_records
Case Is > 0
DoCmd.TransferText acExportDelim, "UHS Export Specification", "ERMC", "X:\UHS South TX\Referrals\ready for import\ERMC " & refdate & ".TXT", False
Case Is < 1
DoCmd.TransferText acExportDelim, "UHS Export Specification", "ERMC", "X:\UHS South TX\Referrals\ready for import\ERMC no referral " & refdate & ".txt"
End Select

mhh_records = DCount("*", "MHH")
Select Case mhh_records
Case Is > 0
DoCmd.TransferText acExportDelim, "UHS Export Specification", "MHH", "X:\UHS South TX\Referrals\ready for import\MHH " & refdate & ".TXT", False
Case Is < 1
DoCmd.TransferText acExportDelim, "UHS Export Specification", "MHH", "X:\UHS South TX\Referrals\ready for import\MHH no referral " & refdate & ".txt"
End Select

mmc_records = DCount("*", "MMC")
Select Case mmc_records
Case Is > 0
DoCmd.TransferText acExportDelim, "UHS Export Specification", "MMC", "X:\UHS South TX\Referrals\ready for import\MMC " & refdate & ".TXT", False
Case Is < 1
DoCmd.TransferText acExportDelim, "UHS Export Specification", "MMC", "X:\UHS South TX\Referrals\ready for import\MMC no referral " & refdate & ".txt"
End Select

stbhc_records = DCount("*", "STBHC")
Select Case stbhc_records
Case Is > 0
DoCmd.TransferText acExportDelim, "UHS Export Specification", "STBHC", "X:\UHS South TX\Referrals\ready for import\STBHC " & refdate & ".TXT", False
Case Is < 1
DoCmd.TransferText acExportDelim, "UHS Export Specification", "STBHC", "X:\UHS South TX\Referrals\ready for import\STBHC no referral " & refdate & ".txt"
End Select

MsgBox "Export Complete. Summary:" & vbCrLf & "There were " & ech_records & " new referrals for ECH" & vbCrLf & _
"There were " & ermc_records & " new referrals for ERMC" & vbCrLf & "There were " & mhh_records & " new referrals for MHH" & _
vbCrLf & "There were " & mmc_records & " new referrals for MMC" & vbCrLf & "There were " & stbhc_records _
& " new referrals for STBHC", vbInformation, "Results"

End Sub
Thanks in advance for any help!

[CreganTur]

The issue is because of your use of the wildcard character within the DCount function. If you look at the parameters for DCount, you will see that they are DCount(expr, domain, [criteria]), and the specification for expr is the name of the Field, whose records you want to count. Wildcard character is not a Field name, so the function fails.

Replace the wildcard character with the name of a primary key field within your table named "ECH". That will count every record within the table. If you want to refine your count, then you would add parameters for the Criteria parameter.

HTH:thumb

[mpearce]

The issue is because of your use of the wildcard character within the DCount function. If you look at the parameters for DCount, you will see that they are DCount(expr, domain, [criteria]), and the specification for expr is the name of the Field, whose records you want to count. Wildcard character is not a Field name, so the function fails.

Replace the wildcard character with the name of a primary key field within your table named "ECH". That will count every record within the table. If you want to refine your count, then you would add parameters for the Criteria parameter.

HTH:thumb

Good thinking on that. That makes perfect sense. I should have noted that "ECH" is a query and not a table.

Basically two text files get imported into two tables. After that an unmatched query is run. Other queries are run on the results of the unmatched query. This is where the "ECH" query comes in. The DCount function needs to determine if there are records in the results of "ECH" and if there are export those results to a text file.

Hopefully that makes sense.

[CreganTur]

You cna run DCount on a query. Just be sure to use Field names.

[mpearce]

I figured that one out. I think i made some changes to the field names which messed with the queries or something like that. Anyway long story short that problem has been fixed. But based on that code why would i get this error:


run-time error '3011':

The microsoft jet database engine could not find object 'ECH 04222009.txt' make sure the object exists and that you spell its name correctly.

That file is created with data that is exported so of course it isn't going to exist. Any thoughts?

[CreganTur]

i get this error:

run-time error '3011':

The microsoft jet database engine could not find object 'ECH 04222009.txt' make sure the object exists and that you spell its name correctly.

That file is created with data that is exported so of course it isn't going to exist. Any thoughts?

Where in the code does this error occur?

[mpearce]

DoCmd.TransferText acExportDelim, "UHS Export Specification", "ECH", "X:\UHS South TX\Referrals\Ready For Import\ECH " & refdate & ".txt", False


There seems to be an issue with that line. if i comment it out i get the same error on the remaining lines depending on the result of the DCount function.

[CreganTur]

Do all of the folders for these filepaths currently exist? If not, then that's why you're getting the error. TransferText relies on valid filepaths - it won't create folders if they don't exist.

[mpearce]

Do all of the folders for these filepaths currently exist? If not, then that's why you're getting the error. TransferText relies on valid filepaths - it won't create folders if they don't exist.

All of the folders exist, It only the files that do not exist and are created as a result of the transfertext

[CreganTur]

All of the folders exist, It only the files that do not exist and are created as a result of the transfertext

Hmmm...:think:

Then the problem must lie with your specification.

If you're just doing a regular comma delimited file, then you can leave the Specification blank.

[mpearce]

The export spec is where the problem was. At one point i had taken out underscores from the field names to resolve an earlier error. so the export spec has fields without under scores while the query results had fields with underscores. so i fixed the fields in the export spec and things are working beautifully!!

Thanks

[CreganTur]

Glad you figured it out!

Thanks for marking the thread as solved :thumb