String field returning blank space

[sunrunner4kr]

Hi everyone.

I am hoping someone can shed some light on a problem I am experiencing.

I have some code which emails some data from a Table loaded on an open Form.

The body of the email is determined from some strings which are getting their data from the fields of the table.

stMain = "Please find attached Incident Update for: " & stKlugTicket & " raised by " & [COLOR="Red"]stName[/COLOR] & "on " & stDate & vbNewLine & vbNewLine & "---------------------------------" & vbCrLf & vbCrLf & stContentData & vbNewLine & vbNewLine & "Kind Regards" & vbNewLine & vbNewLine & "Accrington System Support" & vbNewLine & "+44 125435 2110"

String stName gets it's data from the field [RECORDEE]

stName = Me.RECORDEE

But when I run the code, the body of the email gets up to the stName part, prints it, but then stops.

When I've have run a Debug.Print stName I am getting:

"Simon Gibbins "

for some reason I've noticed it is adding "space" to the end of the field, could this be a clue to the problem?

The only other thing that has changed with this code, is that the RECORDEE field was taken from a public function acquiring the PC's username. This returned a "." in the name which I thought was causing the problem, so I used a Replace function to eliminate this, but this is saved on the record before this current form is used.

Combo98 = Replace(StrWindowsUserName, ".", " ")

Here is the full code for the emailing Sub on the form:

Private Sub Save_Click()
On Error GoTo Err_Save_Click

    Dim stDocName As String
    Dim stSubject As String
    Dim stTicketID As String
    Dim stText As String
    Dim stDocReport As String
    Dim stMain As String
    Dim stName As String
    Dim stDate As String
    Dim stEmail As String
    Dim stBodyText As String
    Dim stContentData As String
    Dim stKlugTicket As String
    Dim stPriority As String
        
        
    Me!txtLastUpdated = Now()


    stDocName = "AppendKlugEmail"
    DoCmd.RunMacro stDocName
    Me.ENTRYTABLESubformKlugEmail.Requery
    Me.STATUS = "AWAITING KLUG"
    
    
    DoCmd.RunCommand acCmdSaveRecord
    

    stEmail = "helpdesk@klug-is.de"
    stCC = "simon.gibbins@express-gifts.co.uk; lyndsey.clarke@express-gifts.co.uk; catherine.tate@express-gifts.co.uk; jane.haigh@express-gifts.co.uk; joe.tattersall@express-gifts.co.uk"
    stTicketID = Me.[JOB REFERENCE]
    stContentData = Me.Text79
    stPriority = Me.PRIORITY
    stText = Me.[JOB TITLE]
    stKlugTicket = Me.[KLUG REF NUMBER]
    stName = Me.RECORDEE
    stDate = Me.[DATE RAISED]
    stSubject = "Klug Ticket [" & stKlugTicket & "] Job Ref: " & stTicketID & " PRIORITY: " & stPriority & "JOB: " & stText
    stMain = "Please find attached Incident Update for: " & stKlugTicket & " raised by " & stName & "on " & stDate & vbNewLine & vbNewLine & "---------------------------------" & vbCrLf & vbCrLf & stContentData & vbNewLine & vbNewLine & "Kind Regards" & vbNewLine & vbNewLine & "Accrington System Support" & vbNewLine & "+44 125435 2110"
    DoCmd.SendObject , , , stEmail, stCC, , stSubject, stMain
    
    DoCmd.Close

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
    
End Sub

Many thanks in advance for your help!

Simon

 

[CBrighton]

I don't see why the space would cause a problem.

If you debug.print / msgbox strMain is it the whole string or does it end after the name like the email body does?

 

[sunrunner4kr]

Thanks for the reply.

If I put a debug it returns everything fine.

If I run MsgBox stMain it returns only up to and including stName, exactly the same as the Email content.



Thanks

 

[CBrighton]

Try using the Trim() function on strName.

Ideally I'd like that to fix it but more realistically I'm hoping to see if it removes the trailing space or if there is some other non-printed character which follows it (meaning the space is not really trailing and therefore won't be removed by Trim)

:edit:

Also, add a space before "Job:" in the subject.

:edit2:

Obviously removing the trailing space will mean the next part of the code ("on ") will need a leading space before the word on.

 

[sunrunner4kr]

Haha thanks!

Yeah, that's how I noticed the space in the first place, I could see there was two spaces before the "on"

Email and Msgbox still the same.

Debug is still doing exactly the same too I'm afraid.

and stName still shows a space "Simon Gibbins "

just to confirm my code:

stName = Trim(Me.RECORDEE)

Thanks again!

 

[CBrighton]

Ok, interesting.

If it hasn't removed the whitespace at the end then logically it's not at the end.

Lets try manually dropping the last character:

stName = Left(me.RECORDEE,Len(me.RECORDEE)-1)

Keep in mind that once you have something that works, try it using other windows signons. This will all be for nothing if it's just one name which has the problem!

 

[sunrunner4kr]

You beauty!!!

That's perfect. I'll have to try with other users in the morning.

I was pulling my hair out all morning.

Thanks a lot!!

Simon

 

[CBrighton]

No problem.

A little bit a trial & error and we got there.

Glad it's sorted.