Macro condition Form Value NOT IN Table

[gavine]

Hello,

I have a form where a user can specify a file name and upload it. I want to prevent users from loading the same file more than one time, creating duplicate records.

I am trying to prevent this with a macro using the CONDITION field. When a user loads a file for the first time, after successfully loading the file, the file name and current time stamp is added to a table tracking the files that have been loaded. I want to add a statement in the condition field of the first step which says the following:

IF [Forms]![SelectFile]![DataFileName] NOT IN [Data File Load History Table]![File Name]
THEM True, ELSE False.

I've tried the following two statements:
[Forms]![SelectFile]![DataFileName] NOT IN [Data File Load History Table]![File Name]

[Forms]![SelectFile]![DataFileName] <> "Data File Load History Table.File Name"

The first statement returns the error "the object doesn't contain the automation object"

The second statement returns no error but does not perform the correct validation.

Can anyone help me? Thank you!

 

[pbaldy]

You can't refer to a table value that way. I'd use DCount and test for > 0:

http://www.mvps.org/access/general/gen0018.htm

 

[gavine]

Thanks so much for the quick response!

I've now tried the following but the syntax is not correct. Can you help?

DCount("File Name","Data File Load History","Criteria= '" & [Forms]![SelectFile]![DataFileName] & "'")=0
I expect this statement to return TRUE if the DataFileName in the form does not appear in the File Name field of the Data File Load History table, and FALSE if it does.

Thanks again!

 

[pbaldy]

For starters, you need to bracket your object names because of the inadvisable spaces in them. Secondly, I don't think the word Criteria is appropriate, as that's supposed to be the name of the field to apply the criteria to. Try

DCount("*","[Data File Load History]","[File Name] = '" & [Forms]![SelectFile]![DataFileName] & "'")=0

Having File Name in the first argument was fine, but in this situation * is slightly more efficient.

 

[gavine]

Perfect! This solves my problem, thank you so much for the help!

 

[pbaldy]

Happy to help, and welcome to the site!