Macro not working when tables converted to SQL

[Jill]

I converted my tables to SQL and am now having issues with a macro that gets run when a new record is added to my clients table. The way I have it set up is when a new client name is added on the afterupdate property of this text field I have a macro running that searches for duplicates. It should open a form that contains all of the duplicate names , the address, and the date they were added to the database. The users like this because it is kind of a validation reference to make sure they're not entering the same person twice. BUT, if it doesn't find the same name, it's not supposed to open the 2nd form at all. This worked fine before I converted my table to SQL and now it opens the 2nd form EVERYTIME a new name is entered whether it's a duplicate or not. If it is a duplicate it is displaying the information correctly and if it's not a duplicate the form is just blank.

The only thing that I can find that changed is the allow zero length property went from No to Yes when I upsized the tables. I am working on trying to figure out how to fix that on the SQL end, but for now I'm wondering if I can fix the macro? I'm pretty sure it's happening because I have a condition in my macro where if a field IsNull then the action. I'm sure that it's not recognizing the IsNull because of this zero length property, but I've also tried changing my condition to field=0 and it gives me a type mismatch because it's a text field............

I have been trying like crazy to figure this out, so any suggestions will be greatly appreciated!!

Thanks!

 

[bjackson]

try changing condition to field=""

 

[Jill]

Thanks bjackson, but that didn't work. It still gives me that Type mismatch error.

Any other ideas?

 

[FoFa]

Depends on how you are checking, with SQL it is returning zero rows or a NULL recordset. You maybe able to use ISNULL check or what I would do is issue a COUNT (which always returns a count), then if > 0 display the form to show the data. You can use DCOUNT in the macro condition to do that.

 

[Jill]

Just wanted to say thanks for the help.

I solved the problem. I decided to use a DLookup on the before update of the name field. Then if it finds a duplicate it will open the form.