Go to end of field and insert new line and date

[veg_racer]

Hi all, sorry if this has already been asked, I've had a quick skim through the previous posts but have been unable to find a post that answers my question.
I have set up a customer database for our small electronics company and i have built a form called contact history. In the form i show a few details about the customer (name phone number etc) but the bulk of the form is a large text box for a contact log.
I would like to have a button on the form that takes me to the contact log field, inserts todays date and allows me to update whats been said to the customer etc. I have it working using setfocus and date functions however it erases any information already in the field.
Is there a way to skip to the end of the field and insert a new line, then add the date and allow me to begin typing?
I would like the end result to look like the following.

16/02/2013: Introduced Myself and the company, spoke to joe bloggs and agreed to call back on 28/02/2013.
28/02/2013: returned call to Joe Bloggs but was out of office, will call back 01/03/2013.
01/03/2013: Spoke to Joe Bloggs and have arranged for him to visit us on 10/03/2013.

 

[pr2-eugin]

What CODE do you currently have inside? Try something like..

Private Sub [COLOR=Blue]yourButtonName[/COLOR]_Click()
    Me.[COLOR=Blue]notesTextBox [/COLOR]= Me.[COLOR=Blue]notesTextBox [/COLOR]& vbCrLf & Date() & " - "
    Me.[COLOR=Blue]notesTextBox[/COLOR].SetFocus
End Sub

Change the highlighted bits to suit your needs ! Hope this helps.

 

[veg_racer]

Ok, thanks. That code works a little better, it no longer overwrites what was there previously but it still adds the date immediately after what was written last. Is there a way to add a new line before it inserts the date? I have no code other that what you have just given me, with the highlighted bits changed of course!

 

[CJ_London]

If your field is text, you will soon run out of characters - max 255 so would change it to a memo field.

You may also want to consider normalising your table - ideally you should have a separate record for each contact event

 

[pr2-eugin]

but it still adds the date immediately after what was written last. Is there a way to add a new line before it inserts the date?

I edited the code while you were replying, try the edited code in Post#2.

Also, CJ, has pointed out two critical aspects of the Design. You might as well consider a small re-design.

 

[veg_racer]

Hi CJ, thanks for your reply, field is indeed memo. I'm very new to this and I'm afraid i don't understand what normalising is and what the benefits are? If there is a separate record for each contact event, wont that make the database massive? or is that a dumb question?
I had originally wanted one field for the contact log so anyone could scroll through it and see what had been said and when.

 

[veg_racer]

Yes, thank you Pr2-eugin, the new code works much better, my only small gripe now would be that all text in the field is selected following inserting the date, so if i begin typing it overwrites it, is there a way to deselect the previous contents so i just get a blinking cursor following the date insert?

 

[pr2-eugin]

It should not be very hard, Create a new table with the following Structure.

tblNotes
noteID - Autonumber (PK)
clientID - Number
noteDate - Date/Time (Default value - Date())
notesTxt - Text (Or) Memo

On the Client Form, Create an UnBound ListBox, with the following as the RowSource.

SELECT tblNotes.noteDate, tblNotes.notesTxt
FROM tblNotes
WHERE tblNotes.clientID = Forms!yourFormName!yourClientIDControlName;

Or simply use the ListBox wizard to do the same, much neater TBH.

Now to enter information into the Notes table. Create a Form based on the table tblNotes. Set the default value of the clientID as, = Forms!yourFormName!yourClientIDControlName

Add a button in the form too. This button (lets name it, closeBtn) will simply close the Form and requery the Client form's listbox.

Then on click of the close button on the form you created, just have,

Private Sub closeBtn_Click()
    DoCmd.Close acForm, Me.Name
    Forms!yourClientForm!listBoxName.Requery
End Sub

In the client Form, button click..

Private Sub yourButtonName_Click()
    DoCmd.OpenForm "theFormName", DateMode:=acFormAdd
End Sub

Regarding,

is there a way to deselect the previous contents so i just get a blinking cursor following the date insert?

Try something like..

Private Sub yourButtonName_Click()
    Me.notesTextBox = Me.notesTextBox & vbCrLf & Date() & " - "
    Me.notesTextBox.SetFocus
    Me.notesTextBox.SelStart = Len(Me.notesTextBox)
    Me.notesTextBox.SelLength = 0
End Sub

 

[CJ_London]

just to interject - it think your rowsource for your listbox should be

WHERE tblNotes.clientID = [yourClientIDControlName]

Would also suggest that tblNotes should also have a field for the username (which can also be defaulted) so you know who made the note