VBA input box cancel removes value

**Ruegen** · 03-13-2014, 05:01 PM

I have

Code:

If IsValidEmail([TeacherEmail]) Or IsNull([TeacherEmail]) Then
'do nothing
Else
[TeacherEmail] = InputBox(IIf(IsNull([MergedName]), "The Teacher's", "The teacher " & [MergedName]) & " email " & [TeacherEmail] & " is invalid, please update below", "Invalid Email Found", [TeacherEmail])
End If

however if the user clicks cancel - the email becomes null/""

What would be the correct way to do this as I can't be removing emails if the user cancels

**June7** · 03-13-2014, 05:14 PM

Set a variable to the InputBox. Then test the variable.

If strInput <> "" Then Me.TeacherEmail = strInput

**Ruegen** · 03-13-2014, 05:23 PM

Originally Posted by June7

Set a variable to the InputBox. Then test the variable.

If strInput <> "" Then Me.TeacherEmail = strInput

I have

If IsNull(emailUpdate) Or "" ThenElse
email1 = emailUpdate
End If

it's underneath

would it do the same thing as what you have?

**Ruegen** · 03-13-2014, 05:25 PM

I've made a module with

Code:

Function fixTeacherEmail(email1, Tname As Control)
Dim emailUpdate As String
If IsValidEmail(email1) Or IsNull(email1) Then
'do nothing
Else
emailUpdate = InputBox(IIf(IsNull(Tname), "The Teacher's", "The teacher " & Tname) & " email " & email1 & " is invalid, please update below", "Invalid Email Found", email1)
If IsNull(emailUpdate) Or "" Then
Else
email1 = emailUpdate
End If
End If
End Function

then I call

call fixTeacherEmail([TeacherEmail],[MergedName])

look right to you?

**Ruegen** · 03-13-2014, 05:42 PM

updated it to

Code:

Function fixTeacherEmail(TeacherEmail1, Tname As Control)
Dim strEmail1, strName1, emailUpdate As String
strEmail1 = IIf(IsNothing(TeacherEmail1), "", Tname)
strName1 = IIf(IsNothing(Tname), "", Tname)




If IsValidEmail(strEmail1) Or IsNull(strEmail1) Then
'do nothing
Else
emailUpdate = InputBox(IIf(IsNull(strName1), "The Teacher's", "The teacher " & strName1) & " email " & strEmail1 & " is invalid, please update below", "Invalid Email Found", strEmail1)
If IsNull(emailUpdate) Or "" Then
Else
TeacherEmail1 = emailUpdate
End If
End If
End Function

update: argh it's hard to keep track!

**Ruegen** · 03-13-2014, 05:53 PM

My problem is now that isnothing() needs a string

how do I get [TeacherName] as a string when I make the function have "as control" ?
is as control the correct one to use??

**June7** · 03-13-2014, 05:56 PM

Why a fixTeacherEmail function?

Why not just code in the original event?

Why an InputBox anyway? Why not bound textbox?

A string variable cannot hold null. Code will error if try to set to null. Only a variant can hold null.

**Ruegen** · 03-13-2014, 06:03 PM

Originally Posted by June7

Why a fixTeacherEmail function?

Why not just code in the original event?

Why an InputBox anyway? Why not bound textbox?

A string variable cannot hold null. Code will error if try to set to null. Only a variant can hold null.

I have to use this a lot for various forms.

A popup is easier.

Thanks - will see if variant will do.

**Ruegen** · 03-13-2014, 06:05 PM

I get

Click image for larger version.

Name: Capture.PNG
Views: 13
Size: 5.8 KB
ID: 15745

on
IsValidEmail(strEmail1)

for ref

Code:

Function fixTeacherEmail(TeacherEmail1, Tname As Variant)


Dim strEmail1, strName1, emailUpdate As String
strEmail1 = IIf(IsNothing(TeacherEmail1) Or IsNull(TeacherEmail1), "", TeacherEmail1)
strName1 = IIf(IsNothing(Tname) Or IsNull(Tname), "", Tname)


If IsValidEmail(strEmail1) Or IsNull(strEmail1) Then
'do nothing
Else
emailUpdate = InputBox(IIf(IsNull(strName1), "The Teacher's", "The teacher " & strName1) & " email " & strEmail1 & " is invalid, please update below", "Invalid Email Found", strEmail1)
If IsNull(emailUpdate) Or "" Then
Else
TeacherEmail1 = emailUpdate
End If
End If


End Function

update *me thinks* IsValidEmail(strEmail1.Value)

**June7** · 03-13-2014, 06:15 PM

Which line triggers that error?

Are you aware that VBA requires every variable to be explicitly declared or they will default as variant?

Your Dim line actually sets only emailUpdate as a string variable. All others on that line are variant.

**Ruegen** · 03-13-2014, 06:22 PM

Originally Posted by June7

Which line triggers that error?

Are you aware that VBA requires every variable to be explicitly declared or they will default as variant?

Your Dim line actually sets only emailUpdate as a string variable. All others on that line are variant.

oh crap, I thought it did that for all of them

**Ruegen** · 03-13-2014, 06:31 PM

I'll start from the beginning

I have on the record a onclick event for a button

Code:

Dim strEmailSchool As String
Dim strEmailTeacher As String
Dim strSchoolName As String
Dim strTeacherName As String


strSchoolName = [SchoolName]
strEmailSchool = [SchoolEmail]


strTeacherName = [MergedName]
strEmailTeacher = [TeacherEmail]






'repair Emails first
Call fixSchoolEmail(strEmailSchool, strSchoolName)
Call fixTeacherEmail(strEmailTeacher, strTeacherName)

Then I made a function that would take the strings and run them through a function that I want to use a lot

Code:

Function fixTeacherEmail(TeacherEmail1, Tname As String)


Dim strEmail1 As String
Dim strName1 As String
Dim emailUpdate As String


strEmail1 = TeacherEmail1
strName1 = Tname


If IsValidEmail(strEmail1) Or IsNull(strEmail1) Then
'do nothing
Else
emailUpdate = InputBox(IIf(IsNull(strName1), "The Teacher's", "The teacher " & strName1) & " email " & strEmail1 & " is invalid, please update below", "Invalid Email Found", strEmail1)
If IsNull(emailUpdate) Or "" Then
Else
TeacherEmail1 = emailUpdate
End If
End If


End Function

Basically it doesn't come up with the error now but it doesn't popup either or give me any feedback

update it works now only I have to set the control to = the string (I would rather import the [fieldvalue] directly and have it change within the function)

**Ruegen** · 03-13-2014, 06:45 PM

Simplified

Code:

Call fixSchoolEmail([SchoolEmail], [SchoolName])
Call fixTeacherEmail([TeacherEmail], [MergedName])

Code:

Function fixTeacherEmail(strTEmail As String, strTName As String)


Dim emailUpdate As String


If IsValidEmail(strTEmail) Or IsNull(strTEmail) Then
'do nothing
Else
emailUpdate = InputBox(IIf(IsNull(strTName), "The Teacher's", "The teacher " & strTName) & " email " & strTEmail & " is invalid, please update below", "Invalid Email Found", strTEmail)
If IsNull(emailUpdate) Or "" Then
Else
strTEmail = emailUpdate
End If
End If


End Function

**Ruegen** · 03-13-2014, 07:10 PM

Oh I see what I am doing wrong here

fixTeacherEmail should be assigned...

**Ruegen** · 03-13-2014, 09:34 PM

ok I think I have it sorted

I have made

Code:

Dim strScEmailchange As String


strScEmailchange = fixEmail([SchoolEmail], [SchoolName])
[SchoolEmail] = strScEmailchange


strScEmailchange = fixEmail([TeacherEmail], [MergedName])
[TeacherEmail] = strScEmailchange

and

Code:

'fix an email
Function fixEmail(strSemailPull As Variant, strSnamePull As Variant) As String


Dim strEmail As String
Dim strEName As String
Dim strInput As String


strEmail = IIf(IsNull(strSemailPull), "", strSemailPull)
strEName = IIf(IsNull(strSnamePull), "", strSnamePull)


       
        If IsValidEmail(strEmail) = True Then
        fixEmail = strEmail
        
        Else
        
        strInput = InputBox(strEName & " has an invalid email, please correct below.", "Bad Email", strEmail)
        
            If strInput <> "" Then
            fixEmail = strInput
            End If
            
        End If


End Function

and it all seems to work - can you spot anything wrong?

VBA input box cancel removes value

Thread Tools

VBA input box cancel removes value

Similar Threads

Save or Cancel a record input to a table.. dirty event?

update query that removes extra spaces in the sentence

update query that removes extra letters

Trying to create a function that removes the carriage return from a string .

Adding table to Query removes every 4th record

Posting Permissions