trying to use Dlookup to prevent records from importing twice

[chanman525]

Hey all. I have a database with some code already built in to import files from .txt files into the database. I'm trying to errorproof it so that if this is run twice in one day, it won't import the same information twice. I would like to use Dlookup to see if the date already exists in the "End Date" field, then don't run the import. But of course, I'm terrible at writing code so I'm looking for help. Here is my code....

Function import()
On Error GoTo import_Err


Dim receive As Database
Dim add_date As Field
Dim input_date As TableDef
Dim rec As Recordset
Dim path As String
Dim file_name As String
Dim import_filename As String
Dim month As String
Dim strFileExists As String
Dim year As String
Dim currentmonth As String
Dim test As String



Set receive = CurrentDb()
Set rec = receive.OpenRecordset("input_date")

path = "g:\prorep\tranhist\Receive\"


file_name = (rec!add_date & ".txt")
import_filename = path & file_name

strFileExists = dir(import_filename)
If strFileExists <> "" Then

year = Left$(file_name, 4)
month = Mid$(file_name, 5, 2)
currentmonth = year & month

If test = DLookup("[End Date]", "currentmonth", "[End Date]" = Date) Then

Quit acQuitPrompt

Else

import_filename = path & file_name
DoCmd.TransferText acImportDelim, "receive spec", currentmonth, import_filename, False, ""



End If





' Delete input_date table
Set rec = receive.OpenRecordset("input_date")
Do

rec.Delete
rec.MoveNext

Loop Until rec.EOF



import_Exit:

Exit Function


import_Err:
MsgBox Error$
Resume import_Exit


End If

End Function


A lot of the code was already there, basically my line is the Dlookup, but the code is going right through it. Can someone help me out?

 

[PHV]

what about this ?
If Not IsNull(DLookup("[End Date]", "currentmonth", "[End Date]=Date()")) Then
Quit acQuitPrompt
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MIKElaw]

Look at it the other way.
Allow them to be imported and then use a query with a Count function to delete all those that are duplicated. (See the Query Wizards for an example)

Or you can combine the two and import with some filter that is less than 100% reliable and then delete the duplicates

 

[chanman525]

PHV, just a quick question.

I imported this earlier today, and now after I enter this code, it says "You canceled the previous operation"....

Does this mean that the code is working the way it should?

Also, is there a chance that you could explain the If Not IsNull

I don't quite understand that part of the code. I'm interested in learning this stuff, just trying to get a grasp of it.

 

[PHV]

Does the currentmonth table have a Datetime field named [End Date] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

Yes, End Date is a date/time field. Basically I want it to say if 5/16/07 already exists, then don't import.

The field looks like this.... 05/16/2007 05:30:00 AM

 

[PHV]

You may try this:
If Not IsNull(DLookup("[End Date]", "currentmonth", "Int([End Date])=#" & Date & "#")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

I pasted that in and it still says I canceled the previous operation.

 

[PHV]

As you have a currentmonth variable I guess you don't have a table named "currentmonth" ...
what about this ?
If Not IsNull(DLookup("[End Date]", "[" & currentmonth & "]", "Int([End Date])=#" & Date & "#")) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chanman525]

Same result. Canceled the previous operation.

 

[chanman525]

Hey PHV.

I was wondering if there were any other ways to go about error proofing this import.

Like I said before, the last statement you asked me to try gave me that "Canceled previous operation" error.

I tried to step it to follow the code and it jumps right past that line to the msgerr line.

So my question is, am I going about this the right way? Is there a better way to code this process to make it run better?

I'm looking for any help I can get, and I appreciate your help on this.

 

[Remou]

Have you tried testing to see if the table exists? That is, is this the first run for the month?

 

[chanman525]

The table does exist, it created automatically when I first ran the code. That was before I put in the error check.

Since I've put in the error check, it's been giving the error.

 

[cautionbug]

i'm having the exact same error with the exact same type of statement:

If IsNull(DLookup("[field]", "table_name", filter1)) Then

filter1 is a variable generated by a form control.

For testing, i've deliberately entered a value that should return Null, but the error described above keeps happening. Does an empty DLookup return some other value than Null? VBA Help says it should return Null when there are no matches.

 

[cautionbug]

PS:

Have determined this appears to be DLookup's fault. i've tried several methods to get around it, including avoiding the IsNull function, using Nz to replace a Null return with something else, assigning DLookup's results to a variable and testing the variable, etc. Each effort has thrown the error on the line that calls DLookup. It seems like DLookup is failing to return anything when it should return Null. This is evidenced in the variable assignment. The debugger highlights the DLookup line, and shows the variable's value is still Empty, not Null.

 

[cautionbug]

PPS:
A case of mild retardation: i misspelled the lookup field. Works fine. Dammit!