Question From Expression builder to Code builder (1 Viewer)

MrNooby · Mar 2, 2010

I have some code in "Expression builder" which checks if Email is already in database:

Code:

=IIf(DCount("*";"[Contacts]";"[ID]<>" & Nz([ID];0) & " And [E-mail] = '" & Replace(Nz([E-mail]);"'";"''") & "'")>0;"Email was already used once!";"")

Can someone "translate" this code that I can use it in "Code builder"?

MrNooby · Mar 3, 2010

Or in any other way, as long as it checks for already existing fields (email and name) in database from my form.

vbaInet · Mar 3, 2010

Did you write that expression yourself?

MrNooby · Mar 4, 2010

Nop, I'm still just a noob

This expression is in one of the examples in access 2007.

vbaInet · Mar 4, 2010

Then maybe you need to "express" yourself properly?

DCrake · Mar 4, 2010

=IIf(DCount("*";"[Contacts]";"[ID]<>" & Nz([ID];0) & " And [E-mail] = '" & Replace(Nz([E-mail]);"'";"''") & "'")>0;"Email was already used once!";"")

This code is syntactically incorrect and nonsensicle.

vbaInet · Mar 4, 2010

DCrake said:
This code is syntactically incorrect and nonsensicle.

Could be that the OP amended what was given in the original example.

MrNooby · Mar 4, 2010

DCrake said:
This code is syntactically incorrect and nonsensicle.

This code is placed on some label and whenever I open some record (contact) this label is empty if field value doesn't exist in some other record, but if it does it shows that warning.

This does work, but I would rather have code in VB where I could add some thing on my own.

I would have some button (Check fields) and on click event I should check if value in field named "E-mail Address" already exist in my table named "contacts" and field named "E-mail" at any of other record (contacts).

vbaInet · Mar 4, 2010

So you're using an Access template?

This is the syntax of an IIF statement in VBA:

Code:

IF [Something] > 0 Then 
    ....Do Something
Else
    ....Do Another Thing
End If

See if you can transform the expression to fit that structure. You know where the Click event of the button is right?

MrNooby · Mar 4, 2010

Yeah, I know the IF sentance and where/how to place the code, the problem for me is to check all records Email address in database...

Code:

IF [E-mail Address] <> *[Contacts][E-mail] Then ...

vbaInet · Mar 4, 2010

MrNooby said:
Yeah, I know the IF sentance and where/how to place the code, the problem for me is to check all records Email address in database...

Code:

IF [E-mail Address] <> *[Contacts][E-mail] Then ...

I'm not convinced. Maybe you could tell me where the code could go? In what event?

vbaInet · Mar 4, 2010

MrNooby said:
Yeah, I know the IF sentance and where/how to place the code, the problem for me is to check all records Email address in database...

Code:

IF [E-mail Address] <> *[Contacts][E-mail] Then ...

I'm not convinced. Maybe you could tell me where the code could go? In what event?

MrNooby · Mar 4, 2010

On some button click. So when user enters data, he will click check fields and he will get some message if Email is already used by someone else...

vbaInet · Mar 4, 2010

Since you say it works, I have just put it in vba code format:

Code:

    If DCount("*", "[Contacts]", "[ID]<>" & Nz([ID], 0) & " And [E-mail] = '" & Replace(Nz([E-mail]), "'", "''") & "'") > 0 Then
        MsgBox "Email was already used once!"
    Else
        MsgBox "Not in use"
    End If

MrNooby · Mar 4, 2010

Great, I think this is it

I'll test a bit more tomorrow when I'm home, but so far it works.

vbaInet · Mar 4, 2010

Goodie! Let us know tomorrow.

MrNooby · Mar 5, 2010

Works like a charm

Thank you!

vbaInet · Mar 5, 2010

Goodie. You're welcome.

Galaxiom · Mar 5, 2010

I believe:
DCount("*", "[Contacts]", "[ID]<>" & Nz([ID], 0)

cound be replaced with:

DCount("[ID]", "[Contacts]", "[ID]<> 0")

because nulls are ignored when counting a field.

Question From Expression builder to Code builder (1 Viewer)

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Remembered

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Super Moderator

Similar threads

Users who are viewing this thread