CODEWITHSUNDEEP
ms-accessvba
dlofrodloh
dlofrodloh

Reputation: 1744

String only added to textbox when textbox already has text in it

The user enters text into txtAddNote then clicks the cmdAddNote button, the VBA runs and appends the note to txtNotes with a time and date stamp.

But it works only when there's already text in txtNotes. What do I have to do to make it work when txtNotes is empty?

Private Sub cmdAddNote_Click()
 Dim MyDate As String

 MyDate = Now()

 Form_ClientF.txtNotes = vbCrLf + MyDate + vbCrLf + vbCrLf + Form_ClientF.txtAddNote + vbCrLf + vbCrLf + Form_ClientF.txtNotes

 Form_ClientF.txtAddNote = ""

End Sub

Upvotes: 2

Views: 149

Answers (3)

HansUp
HansUp

Reputation: 97101

I'm assuming you will make sure you don't run cmdAddNote_Click unless txtAddNote contains something to add (see example code below).

If that is true, you can do this ...

Form_ClientF.txtNotes = vbCrLf & MyDate & vbCrLf & vbCrLf & _
Form_ClientF.txtAddNote & (vbCrLf + vbCrLf + Form_ClientF.txtNotes)

This approach takes advantage of the difference between + and & when used to concatenate a string with Null. Here is an example from the Immediate window illustrating the difference.

? "a" + Null
Null
? "a" & Null
a

Here is a screen capture of a form which uses this approach ...

enter image description here

And the code used by the form ... 

Option Compare Database
Option Explicit

Private Sub cmdAddNote_Click()
    Me.txtnotes = Format(Now, "mmm dd, yyyy h:nn:ss ampm") & vbCrLf & _
        Me.txtAddNote & (vbCrLf + vbCrLf + Me.txtnotes)
    Me.txtAddNote = vbNullString
    Me.txtAddNote.SetFocus
    Me.cmdAddNote.Enabled = False
End Sub

Private Sub Form_Current()
    Me.cmdAddNote.Enabled = (Len(Trim(Nz(Me.txtAddNote, vbNullString))) > 0)
End Sub

Private Sub txtAddNote_Change()
    Me.cmdAddNote.Enabled = (Len(Trim(Nz(Me.txtAddNote.Text, vbNullString))) > 0)
End Sub

Upvotes: 2

enderland
enderland

Reputation: 14135

Use the .Value method rather than directly referring to the textboxes.

For that matter, use it whenever referring to an Excel VBA object - checkboxes, etc. This will save you a lot of frustration later with issues like this.

Private Sub cmdAddNote_Click()
 Dim MyDate As String

 MyDate = Now()

 Form_ClientF.txtNotes.value = vbCrLf + MyDate + vbCrLf + vbCrLf + Form_ClientF.txtAddNote.value + vbCrLf + vbCrLf + Form_ClientF.txtNotes.value

 Form_ClientF.txtAddNote.value = ""

End Sub

Upvotes: 2

Gord Thompson
Gord Thompson

Reputation: 123409

Contatenating a Null value and a string could very well make the whole thing Null, so try this instead:

Form_ClientF.txtNotes = vbCrLf + MyDate + vbCrLf + vbCrLf + Nz(Form_ClientF.txtAddNote, "") + vbCrLf + vbCrLf + Nz(Form_ClientF.txtNotes, "")

Upvotes: 1

Related Questions