By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

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

MatthewML
P: 1
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
Jun 21 '07 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub E_Mail_AfterUpdate()
  2. If Right(Me!E_Mail,1) = "@" Then
  3.   Me!E_Mail = Me!E_Mail & "binghamton.edu"
  4. End If
  5. End Sub
  6.  
or when no "@" is required:
Expand|Select|Wrap|Line Numbers
  1. Private Sub E_Mail_AfterUpdate()
  2. If NOT Instr(Me!E_Mail,"@") > 0 Then
  3.   Me!E_Mail = Me!E_Mail & "@binghamton.edu"
  4. End If
  5. End Sub
  6.  
Nic;o)
Jun 21 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. If E_Mail = " Like " * [@] * " " Then
  2.  
with
Expand|Select|Wrap|Line Numbers
  1. If E_Mail Like "*@*" Then
  2.  
Good luck.
Jun 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.