One input to output to two sources

[Steven.Ashby82]

Dear All,

I'm aware that this is bad practice but I need to have the follow code

Private Sub butSubmit_Click()
    If Me.cboCommentby.ListIndex = -1 Then
        Call MsgBox("You need to select a Staff member", vbInformation)
        Exit Sub
    End If
    
        
            If Len(Me.TempDataBox) > 0 Then
                Me.txtCommentHist = Me.txtCommentHist & vbNewLine & Me.cboCommentby & " " & Now() & " " & Me.TempDataBox
                Me.TempDataBox = ""
                TempDataBox.Visible = True
          
            End If

store the data as above but I also want the last entry only (not the history as above) to store in a field in my table for report purposes.

Currently everytime text is submitted it is stored in one field which is perfect but I do also need to have the option to only collect the very last input.

Thank you

 

[boblarson]

Use a query instead of saving to another table. You can get the very last entry made in the table by using the DLast function (notice I did NOT say LAST - that is an important distinction).

 

[Steven.Ashby82]

How would I do this? Thank you

 

[Steven.Ashby82]

If It helps I have uploaded a section of the form to demonstrate what I have.

The history side is essential for the form view and some reports but the need to output the last text entered into it's own field within a table is just as essential. I'm sorry to go on I just need to complete the overall database today lol :banghead:

 

[pr2-eugin]

Steven, I said I will stay out of this thread, Lol.. but I just cant help it.. Am sorry..

Well again, as Bob has also commented that there is no need to store this info.. What you need is a function that will return the last comment that is added in the Comments Memo field.. So the following function will serve that purpose..

Public Function lastLine(tmpMem As String) As String
    Dim tmpArr() As String
    tmpArr = Split(tmpMem, vbNewLine)
    lastLine = tmpArr(UBound(tmpArr))
End Function

The above function will go into a New module.. and when using in a Query, use as..

NewComment : IIf([Comments] Is Null, "", lastLine([Comments]))

The IIf is a bit important, as the function accepts only a String, which cannot be Null.. If it is a Null it will raise an Error... Check the attachment..

 

[Steven.Ashby82]

I really really appreciate everybody's advice. How do I implement this into my report?

 

[pr2-eugin]

Similar to the Query.. but you will have that in the Control source of the Field..

 

[Steven.Ashby82]

I love you!

 

[pr2-eugin]

I love you!

LOL Cheers..