Do Nothing if field is empty

[Harry Taylor]

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]

Add a check for null;

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]

or use at the beginning of your Subs:

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

 

[Harry Taylor]

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]

Arnel, I think Access automatically trims spaces from textboxes.
Mark

 

[isladogs]

Hi

You could also use this 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:

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

 

[Harry Taylor]

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]

If you press debug which line is it highlighting? I can't see anything obviously wrong with the code?

 

[Harry Taylor]

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]

Change it to

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

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

 

[Harry Taylor]

Minty,

You are an Access Wizard !!

Hugh thanks to you.