Do Nothing if field is empty (1 Viewer)

Harry Taylor

Registered User.
Local time
Today, 00:40
Joined
Jul 10, 2012
Messages
68
I hope someone may be able to help.
I've written my first bit of VBA code (not sure how, but I got there).

Basically I want to copy the Telephone field text to the Microsoft clipboard when I click in the field.

Private Sub Telephone_GotFocus()
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.FieldName)
End Sub
Private Sub Telephone_Click()
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.Telephone)
DoCmd.RunCommand acCmdCopy
End Sub


The above code works, but when I click an empty cell I get an error.
How do I say 'if the field is empty do nothing'

Thank you :)
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
Add a check for null;
Code:
Private Sub Telephone_Click()
	
	If Len(Me.Telephone & "") > 0 Then	'This will check for both a null and empty string
		Telephone.SelStart = 0
		Telephone.SelLength = Len(Me.Telephone)
		DoCmd.RunCommand acCmdCopy
	End If
	
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:40
Joined
May 7, 2009
Messages
19,169
or use at the beginning of your Subs:

IF TRIM([Telephone] & "") = "" Then Exit Sub
 

Harry Taylor

Registered User.
Local time
Today, 00:40
Joined
Jul 10, 2012
Messages
68
Minty, Worked perfectly !!!
You are a star, thank you.

Arnelgp. I didn't try your suggestion as Minty's worked great. Thank you for your suggestion though.
 

MarkK

bit cruncher
Local time
Yesterday, 17:40
Joined
Mar 17, 2004
Messages
8,178
Arnel, I think Access automatically trims spaces from textboxes.
Mark
 

isladogs

MVP / VIP
Local time
Today, 00:40
Joined
Jan 14, 2017
Messages
18,186
Hi

You could also use this code

Code:
Private Sub Telephone_Click()
	
	If Nz(Me.Telephone,"") > "" Then	'This will check for both a null and empty string
		Telephone.SelStart = 0
		Telephone.SelLength = Len(Me.Telephone)
		DoCmd.RunCommand acCmdCopy
	End If
	
End Sub

However both the above & Minty's version have one MAJOR drawback.
The entire Telephone field is now selected and you can't edit it without deleting the existing data
You could of course move to another control & back to Telephone but doing so will again select the entire field.

For that reason I prefer to use a small Copy button next to the field with code like this:

Code:
Private Sub cmdCopy_Click()

    Me.Telephone.SetFocus
    If Nz(Me.Telephone,"") > "" Then	'This will check for both a null and empty string
	Telephone.SelStart = 0
	Telephone.SelLength = Len(Me.Telephone)
	DoCmd.RunCommand acCmdCopy
    End If
    
End Sub

HTH
 
Last edited:

Harry Taylor

Registered User.
Local time
Today, 00:40
Joined
Jul 10, 2012
Messages
68
Minty,

I'm using your code which works great.
As well as Telephone, I have a Mobile field in the same subform. Is it possible to do the same function (copy to MS clipboard) for Mobile?
I have tried the following which works, but when I change the record I get "runtime error 94 invalid use of null"

Option Compare Database
Private Sub Mobile_GotFocus()
Mobile.SelStart = 0
Mobile.SelLength = Len(Me.Mobile)
End Sub
Private Sub Mobile_Click()
If Len(Me.Mobile & "") > 0 Then
Mobile.SelStart = 0
Mobile.SelLength = Len(Me.Mobile)
DoCmd.RunCommand acCmdCopy
End If

End Sub
Private Sub Telephone_GotFocus()
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.Telephone)
End Sub
Private Sub Telephone_Click()
If Len(Me.Telephone & "") > 0 Then
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.Telephone)
DoCmd.RunCommand acCmdCopy
End If

End Sub
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
If you press debug which line is it highlighting? I can't see anything obviously wrong with the code?
 

Harry Taylor

Registered User.
Local time
Today, 00:40
Joined
Jul 10, 2012
Messages
68
Option Compare Database
Private Sub Mobile_GotFocus()
Mobile.SelStart = 0
Mobile.SelLength = Len(Me.Mobile)
End Sub
Private Sub Mobile_Click()
If Len(Me.Mobile & "") > 0 Then
Mobile.SelStart = 0
Mobile.SelLength = Len(Me.Mobile)
DoCmd.RunCommand acCmdCopy
End If
End Sub
Private Sub Telephone_GotFocus()
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.Telephone)
End Sub
Private Sub Telephone_Click()
If Len(Me.Telephone & "") > 0 Then
Telephone.SelStart = 0
Telephone.SelLength = Len(Me.Telephone)
DoCmd.RunCommand acCmdCopy
End If
End Sub
 

Minty

AWF VIP
Local time
Today, 00:40
Joined
Jul 26, 2013
Messages
10,355
Change it to

Code:
Mobile.SelLength = Len(Me.Mobile & "")

I would do the same for the telephone number to prevent error in the future.
 

Users who are viewing this thread

Top Bottom