Entering New Line Before Comment

[Sunny8760]

Hi Everyone,

I'm new to access and to vba for access. I found the following code that I have used in my modified Issues Template that MS offers with Access. The code allows me to add new comments to an existing list of comments, unfortunately I dont know how to make the code automatically add a blank line before adding a new comment.

I have tried eiditing the code by adding vbCrLf and vbNewLine to the bold portion of the code below

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "New Comment" Then
TempDataBox.Visible = True
TempDataBox.SetFocus


IndirectDataInput.Caption = "Save Comment"
Else
IndirectDataInput.Caption = "New Comment"
If IsNull(Me.Description) Then
If Len(Me.TempDataBox) > 0 Then
Me.Description = Now() & "" & Me.TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If
Else
If Len(Me.TempDataBox) > 0 Then
Me.Description = Me.Description & Now() & Me.TempDataBox
Me.TempDataBox = ""
TempDataBox.Visible = False
Else
TempDataBox.Visible = False
End If

End If
End If
End Sub

[pbaldy]

vbCrLf should work. How did you use it?

[Sunny8760]

Thank you for your reply. I tried multiple methods (because I'm not familiar with VBA, I messed around with it to see if I could get it to do what I wanted, but no luck).

Here is a sample of what I've tried:

Me.Description = Me.Description & vbCrLf & Now() & Me.TempDataBox

Result: this resulted in the following, which is space in front of the date, but not an entire blank row.

[pbaldy]

So add 2 of them.

[Sunny8760]

Thanks for the suggestion, but here's what happened.

The result of this code: Me.Description = Me.Description & vbCrLf & vbCrLf & Now() & "" & Me.TempDataBox

Was one blank row, however, entering a second comment did not begin with a blank row.

[pbaldy]

I'm confused, as it clearly added the line. You can put it in a different place if you want. Or is this because of previous tests and you need to start over?

[June7]

If comments were in a related table (each comment would be a new record), no VBA would be needed for data entry.

[ssanfu]

While you are typing in a text box, pressing "<ctl>m" (the control key and the letter m) adds a vbcrlf to the text. No code necessary.

[Missinglinq]

Or is this because of previous tests and you need to start over

I wondered about this, too! I wrote this code a few years ago, and just retrieved it and made the change you need, and it works as it should. I'd delete your current data from the Memo Field and start over; if it still doesn't function correctly post the entire Sub, as it is now written.

Linq ;0)>

[Sunny8760]

If comments were in a related table (each comment would be a new record), no VBA would be needed for data entry.

Thanks for the suggestion. I should provide a bit of background. I created this database to track tasks/issues/small projects at work. My company is not using a CRM system and being in a client facing role I need a system that will help me keep track of the various interactions I have with customers, internal staff, and others to ensure nothing falls through the cracks.

So far I have used an older version of the issues template that MS offers as a base case to learn from and modify for my needs. The latest copy is made for web databases and is difficult to dissect. I'm not very good with MS Access therefore I dont want to rely on someone else's code because when it breaks down I have no idea how to fix it.

I like your idea about using a separate table. I have uploaded my database to show you how I have set it up. Perhaps you can help me understand how to make the Comments table record a sequence anytime I enter a new comment relating to one project/task.

I learned a bit about how to add a separate table for comments from this video, but I'm not sure how to make it work with my input form. https://www.youtube.com/watch?v=gEUiousVDqY

[June7]

Consider a subform for Comments on the ProjectDetails form.

[ssanfu]

I would advise not using an autonumber field for the customer number. Create another field in the customer table for the customer number and use a "Custom Autonumber" to create the number.

See Custom Autonumbers/sequential numbers
http://baldyweb.com/CustomAutonumber.htm

Same for the Comments table. You don't need a compound PK. Just a custom autonumber for the SquenceNo field. (keep all of the PK - FK relationships the same)


My $0.02.........
Good luck with your project..

[Sunny8760]

I would advise not using an autonumber field for the customer number. Create another field in the customer table for the customer number and use a "Custom Autonumber" to create the number.

See Custom Autonumbers/sequential numbers
http://baldyweb.com/CustomAutonumber.htm

Same for the Comments table. You don't need a compound PK. Just a custom autonumber for the SquenceNo field. (keep all of the PK - FK relationships the same)


My $0.02.........
Good luck with your project..

Thank you for helping me with this. I think I'm almost there...

I removed the customers table entirely, I will use combo boxes as these records dont change much.

There are now 3 tables, tblContacts, tblMemos, and tblProjects.

The tblContacts with tblProjects is now joined using a 1-M relationship. I did the same for the tblProjects with tblMemos. In the tblMemos I added an AutoNumber key called MemoSequenceID. The layout looks great and functions much better because each Memo is in a separate row.

To make the form look cleaner I tried using this code from http://allenbrowne.com/func-concat.html. Unfortunately, I hit a bunch of snags, so I'm hoping someone can give some advice.

Does anyone have an alternative way of organizing the Memos on the form, or is the subform the best/only way to do this? In my case, the subform isnt the best way to present a Memo that contains 1 or more paragraph of text.

Thanks,

[June7]

Allen Browne's ConcatRelated function is intended to use with outputting data to a report, not a form.

Use form/subform for inputting data and report for manipulating output.

[Sunny8760]

Allen Browne's ConcatRelated function is intended to use with outputting data to a report, not a form.

Use form/subform for inputting data and report for manipulating output.

Great. Thanks for your help.

Regards,