Using a VBA "If... Then... Else" Statement in a Form's AfterUpdate Event Procedure

[MatthewML]

I am attempting to insert a custom AfterUpdate Event Procedure into a text box on a form that I am designing in MS Access 2000. This text box contains the e-mail address of the referenced contact, and my goal is to automatically insert the "@abc.xyz" portion of the e-mail data string if and only if the user does not specify this part of the data string. I am attempting to do this because 99% of the e-mail addresses that I will be entering into the database will reference the same e-mail server domain, and I do not want the user to need to type out the entire e-mail address each time they input a new contact. However, I need the code to recognize if the user specifies a unique domain (i.e. a form of @abc.xyz that differs from the other 99% of contacts in the database), and in the case that the user does do so, the code must not insert anything into the text string. I assume that the easiest way of doing this is to make the code detect the presence of the "@" sign in the text box, and therein determine what action it takes based on whether or not the "@" sign is found. I am attempting to write a short bit of VBA code to accomplish this task, but I have thus far met with little success. When I attempt to test the code, I keep on getting an error describing a "type mismatch" and I need to debug.

Below is the code that I have written in its current state:

Private Sub E_Mail_AfterUpdate()
If E_Mail = " Like " * [@] * " " Then
E_Mail = StrConv(E_Mail, vbLowerCase)
Else
E_Mail = StrConv(E_Mail, vbLowerCase) + "@binghamton.edu"
End If
End Sub

The debugger constantly detects a problem in the second line of the code, and I believe that my use of a "Like" statement here may be incorrect. I am a novice at writing VBA code, and any help that I could receive in doing so would be very useful.

- Matthew

[nico5038]

I wouldn't probably use this mechanism as it might confuse users.
Better perhaps to have separate email fields for own (with appending) and foreign email-addresses. Then you can use the BeforeUpdate event of the form to append the @ string and/or move the foreign emailaddress.

For the IF I would use:

Private Sub E_Mail_AfterUpdate()
If Right(Me!E_Mail,1) = "@" Then
  Me!E_Mail = Me!E_Mail & "binghamton.edu"
End If
End Sub
 

or when no "@" is required:

Private Sub E_Mail_AfterUpdate()
If NOT Instr(Me!E_Mail,"@") > 0 Then
  Me!E_Mail = Me!E_Mail & "@binghamton.edu"
End If
End Sub
 

Nic;o)

[FishVal]

I am attempting to insert a custom AfterUpdate Event Procedure into a text box on a form that I am designing in MS Access 2000. This text box contains the e-mail address of the referenced contact, and my goal is to automatically insert the "@abc.xyz" portion of the e-mail data string if and only if the user does not specify this part of the data string. I am attempting to do this because 99% of the e-mail addresses that I will be entering into the database will reference the same e-mail server domain, and I do not want the user to need to type out the entire e-mail address each time they input a new contact. However, I need the code to recognize if the user specifies a unique domain (i.e. a form of @abc.xyz that differs from the other 99% of contacts in the database), and in the case that the user does do so, the code must not insert anything into the text string. I assume that the easiest way of doing this is to make the code detect the presence of the "@" sign in the text box, and therein determine what action it takes based on whether or not the "@" sign is found. I am attempting to write a short bit of VBA code to accomplish this task, but I have thus far met with little success. When I attempt to test the code, I keep on getting an error describing a "type mismatch" and I need to debug.

Below is the code that I have written in its current state:

Private Sub E_Mail_AfterUpdate()
If E_Mail = " Like " * [@] * " " Then
E_Mail = StrConv(E_Mail, vbLowerCase)
Else
E_Mail = StrConv(E_Mail, vbLowerCase) + "@binghamton.edu"
End If
End Sub

The debugger constantly detects a problem in the second line of the code, and I believe that my use of a "Like" statement here may be incorrect. I am a novice at writing VBA code, and any help that I could receive in doing so would be very useful.

- Matthew

Hi!
Thanks for your question being detailed and clear.
Your problem is just a syntax issue.

Replace

If E_Mail = " Like " * [@] * " " Then
 

with

If E_Mail Like "*@*" Then
 

Good luck.