command button to update and delete fields 3

[cthai]

Hi - I working on a form that was already build and what i would like to do is, when a user click on a button it will move fields case_status, case_compDate, and CaseID to txtNotes, with a note that say something like 'today date: case_status (ie.denied) for depart on case_compDate (ie.4/1/11) by CaseID (ie.John Doe).


and it will also clear out the those 3 case fields (cas_status, case_comDate and CaseID)

 

[randy700]

What is txtNotes?


Randy

 

[PHV]

A starting point (VBA code):
Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
Me!case_status = Null
Me!case_compDate = Null
Me!CaseID = Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cthai]

Hi PHV -

Thanks for the reply - the code works well, but it deleted all my current notes in the text box. how do I add the notes to the txtnotes.. rather then deleting the current notes that is already there?

thank you for your help

 

[kjv1611]

cthai,

Are you sure you want to go that route? How large is or will the underlying data set be? You might want to consider keeping separate note records instead of just pasting a bunch of stuff together. For instance, if you already have some notes typed in, do you really want to tack more stuff on the end? Perhaps it's relevant, maybe not. What if the notes are 3 months old, and the new notes relate to something different?

What I'm suggesting is that you build a notes table that stores a NoteID for each note, the text of the note, who added the note, and when the note was added. Then on your form, you just add a subform that is a continuous form for listing all the related notes.

But if you want to do it the way you mention (and you did not specify leaving the existing text before PHV answered), you'd need to do like this:

If IsNull(Me!txtNotes) OR Me!txtNotes = vbNullString Then
   Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
Else
   Me!txtNotes = Me!txtNotes.Text & Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
End If

Me!case_status = Null
Me!case_compDate = Null
Me!CaseID = Null

Well, if you can narrow down whether it is a nullable field or just a field that allows zero length strings, you can specify whether to use IsNull or check for equal to vbNullString - I'm thinking the vbNullString will work here, but don't remember off-hand for certain.

 

[cthai]

hi kjv1611 -

It is require that we keep all the notes... there are notes in there that is 5 years old.. and we still have them in the text box. Not sure why this was design this way... but it is what it is


when i use .text i get an error "2185 - You can't reference a property or method for a control unless the control has focus" I change .text to .value but this still doesnt add the new text rather it deleted all the text and add a 0. if there is text in the text box... if text box is null the value was added without an issue.


I didnt think about the current notes before posting the question, and after I test out the code that PHV provided I realize - we need to keep the old notes, and add this to it. Sorry PHV

 

[randy700]

How about this?

Me!txtNotes = [red]Notes[/red] & " " & Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID

If necessary, replace [red]Notes[/red] with the correct field name.

However, I agree with kjv. You really should consider a separate Notes table.


Randy

 

[cthai]

replace Notes with the field name (txtnotes) and it's still clearing out the old text and replacing it a 0

If IsNull(Me!txtNotes) OR Me!txtNotes = vbNullString Then
   Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
Else
   Me!txtNotes = txtNotes & Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
End If

Me!case_status = Null
Me!case_compDate = Null
Me!CaseID = Null

 

[kjv1611]

As to your error, I didn't think about that... simple fix, though, if you really want to go that route:

txtNotes.SetFocus
If IsNull(Me!txtNotes) OR Me!txtNotes = vbNullString Then
   Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
Else
   Me!txtNotes = Me!txtNotes.Text & Me!txtNotes = Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID
End If

Me!case_status = Null
Me!case_compDate = Null
Me!CaseID = Null

And at the end, you can set the focus to whatever control you want after that, if you want to move focus from the text box to something else. If want to go to the previous control, then it'd be best to create a variable to contain the previous control or the previous control name, so you can go back to it.

As to a way to deal with your notes history delimna, one quick and dirty fix would be this:
1. Create your notes table
2. Append all the current notes to the table, and just give them Now() for the date appended, and just either add an "about this note" text field and append a note there that says "prior to this date, added from historical notes" or whatever.
3. Then set your database up to always add a new record for a new note line, capturing who entered the note (Windows user) and when they added it. That really should be a VERY high priority if your'e keepign soemthing this long... and if it's really important. This is PARTICULARLY the case if it would be important for any legal matters.

Proper documentation can make or break a business in a heart beat.

 

[randy700]

Me!txtNotes = txtNotes & [s]Me!txtNotes =[/s] Date & ": " & Me!case_status & " for depart on " & Me!case_compDate & " by " & Me!CaseID

Randy