ms-accessbuttontextboxvbams-access-2010

Button To Add Text and a new line


I am trying to make a button in Microsoft Access 2010 with VBA to add pre-set text to a textbox and add a line break after the text. However, whenever I click the button it seems to be ignoring my Then statement and jumping right to the Else statement, adding a line break to the empty textbox and then adding the pre-set text no matter what the state of the Notes textbox is.

The submit button was originally an embedded macro but I have since converted it to VBA code in order to make my Notes textbox have no value to hopefully have my Then statement honored but with no luck. Here is the coding for both buttons:

Private Sub statusBtn_Click()
Notes.SetFocus
If Notes = "" Then Notes = "lorem ipsum" Else Notes = Notes & vbCrLf & "lorem ipsum"
Me![Notes].SelStart = IIf(IsNull(Me![Notes]), 0, Len(Me![Notes]))
End Sub



Private Sub SubmitBtn_Click()
On Error GoTo SubmitBtn_Click_Err
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
    Beep
    MsgBox MacroError.Description, vbOKOnly, ""
End If
SubmitBtn_Click_Exit:
Exit Sub
SubmitBtn_Click_Err:
MsgBox Error$
Resume SubmitBtn_Click_Exit
Notes = ""
End Sub

Solution

  • Here is a different form of your one-line If ... Then statement, which is easier for me to discuss:

    If Notes = "" Then
        Notes = "lorem ipsum"
    Else
        Notes = Notes & vbCrLf & "lorem ipsum"
    End If
    

    Consider the code path when Notes is Null. The condition Notes = "" will not be True. It won't be False, either, but that doesn't matter. Since it's not True, control passes to the Else section. And that means you concatenate Null plus CRLF (carriage return and line feed) plus "loren ipsum".

    I suspect you actually want Notes to become just "loren ipsum" whenever the starting value of Notes is either Null or an empty string (""). In that case, change the If condition:

    If Len(Notes & "") = 0 Then