update user and now()

[fishqqq]

I have a memo field that i would like the user to be able to update as
follows:

CURRENT USER - NOW()

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf) I know there is something wrong with my code but i'm not
certain what it is. access is telling me the problem is with this
portion " Note__invoice_notes_ = " but i can't tell what.


Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!
[fCurrentUser]![user now()] & " - " & "" & vbCrLf &
Note__invoice_notes_
Note__invoice_notes_.SetFocus
Note__invoice_notes_.SelStart = 28

any help is greatly appreciated.

 

[Stefan Hoffmann]

hi,

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf)

Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!

Try this

Dim Username As String
Username = functionToGetTheUsername() ' OrField
Me![Note__invoice_notes_] = Now() & " - " & Username & _
vbCrLf & _
Me![Note__invoice_notes_]


mfG
--> stefan <--

 

[John W. Vinson]

I have a memo field that i would like the user to be able to update as
follows:

CURRENT USER - NOW()

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf) I know there is something wrong with my code but i'm not
certain what it is. access is telling me the problem is with this
portion " Note__invoice_notes_ = " but i can't tell what.


Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!
[fCurrentUser]![user now()] & " - " & "" & vbCrLf &
Note__invoice_notes_
Note__invoice_notes_.SetFocus
Note__invoice_notes_.SelStart = 28

any help is greatly appreciated.

Using a single memo field to store multiple "signed and dated" notes is a
Really Bad Idea. It will make it all but impossible to search for notes from a
given user or on a given date, since this information is buried in the depths
of a big block of amorphous text.

Could you consider relating your Invoices table one-to-many to an InvoiceNotes
table, with fields for InvoiceNo (a link to your primary key), NoteBy
(defaulting to CurrentUser), and NoteDate (defaulting to Now())? This gives
you your desired result with *no code at all*, no ambiguity, easy searching
for notes by user or by date, and less risk of corruption from frequent
editing of (fairly fragile) Memo fields?

If you REALLY REALLY want to do it this way you will need to store the current
contents of the note field, remove it from the field, and concatenate it
afterwards, or (better) collect the user's input in an UNBOUND text field and
concatenate that field into the Notes field. It's complicated either way.

 

[fishqqq]

hi,

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf)
Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!

Try this

   Dim Username As String
   Username = functionToGetTheUsername() ' OrField
   Me![Note__invoice_notes_] = Now() & " - " & Username & _
                               vbCrLf & _
                               Me![Note__invoice_notes_]

mfG
--> stefan <--

Tks Stefan, this is getting too complicated for me to get my head
around.
How about a different approach.
How would I get the button to do the following once 'clicked' ?

1) go to [Memo field]
2) drop everything down one line
3) take the text from [text field] (this would be the Now() and user
name info)
4) place it on top of the data in the memo field

so the memo field would read something like this

BOB 5/8/2009 10:45:29 AM - Customer notified of delivery
BOB 5/8/2009 9:45:29 AM - Customer notified of delay

I think this may be easier for me to make work? I would greatly
appreciate your help though...
tks

 

[Dale_Fye via AccessMonster.com]

I agree with John.

I always do this as a separate table with individual records for each note.
I then generally use a subform, with the notes sorted newest to oldest to
display the data to the user.

This method gives you the added flexibility that would allow you to prevent
or limit editing of past records, so you have an audit trail.

 

[BruceM]

If this seems complicated, it will be more so when you append your new data
to the existing data. As has been mentioned elsewhere in this thread, a
related table is the way to go about this.

Maybe it would be something like this if you insist on the clunky approach:

Dim strOldText as String, strNewText as String, strUser as String

strOldText = Me.[MemoField]
strNewText = Me.[txtTextControl]
strUser = {function or field to identify current user)

Me.[MemoField] = strUser & Now() & strNewText & _
vbCrLf & strOldText

Me.txtTextControl = Null

This assumes strNewText is an unbound text box in which the user types
"Customer notified of delivery" or whatever needs to be said, and that you
have a way of identifying the user so that you can assign a value to
strUser. MemoField is the field in which you are storing this unruly
collection.

Once again, you should not do it this way, but if you are going to go ahead
with it I suppose this is a way to use a micrometer as a glue clamp. I
would be interested in knowing why you do not want to use a related table.
If it is a case of not understanding how to implement it, there are
resources to guide you.

hi,

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf)
Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!

Try this

Dim Username As String
Username = functionToGetTheUsername() ' OrField
Me![Note__invoice_notes_] = Now() & " - " & Username & _
vbCrLf & _
Me![Note__invoice_notes_]

mfG
--> stefan <--

Tks Stefan, this is getting too complicated for me to get my head
around.
How about a different approach.
How would I get the button to do the following once 'clicked' ?

1) go to [Memo field]
2) drop everything down one line
3) take the text from [text field] (this would be the Now() and user
name info)
4) place it on top of the data in the memo field

so the memo field would read something like this

BOB 5/8/2009 10:45:29 AM - Customer notified of delivery
BOB 5/8/2009 9:45:29 AM - Customer notified of delay

I think this may be easier for me to make work? I would greatly
appreciate your help though...
tks

 

[fishqqq]

If this seems complicated, it will be more so when you append your new data
to the existing data.  As has been mentioned elsewhere in this thread, a
related table is the way to go about this.

Maybe it would be something like this if you insist on the clunky approach:

Dim strOldText as String, strNewText as String, strUser as String

strOldText = Me.[MemoField]
strNewText = Me.[txtTextControl]
strUser = {function or field to identify current user)

Me.[MemoField] = strUser & Now() & strNewText & _
                               vbCrLf & strOldText

Me.txtTextControl = Null

This assumes strNewText is an unbound text box in which the user types
"Customer notified of delivery" or whatever needs to be said, and that you
have a way of identifying the user so that you can assign a value to
strUser.  MemoField is the field in which you are storing this unruly
collection.

Once again, you should not do it this way, but if you are going to go ahead
with it I suppose this is a way to use a micrometer as a glue clamp.  I
would be interested in knowing why you do not want to use a related table..
If it is a case of not understanding how to implement it, there are
resources to guide you.

hi,

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf)
Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!

Try this

Dim Username As String
Username = functionToGetTheUsername() ' OrField
Me![Note__invoice_notes_] = Now() & " - " & Username & _
vbCrLf & _
Me![Note__invoice_notes_]

mfG
--> stefan <--

Tks Stefan, this is getting too complicated for me to get my head
around.
 How about a different approach.
How would I get the button to do the following once 'clicked' ?

1) go to [Memo field]
2) drop everything down one line
3) take the text from [text field] (this would be the Now() and user
name info)
4) place it on top of the data in the memo field

so the memo field would read something like this

BOB 5/8/2009 10:45:29 AM - Customer notified of delivery
BOB 5/8/2009 9:45:29 AM - Customer notified of delay

I think this may be easier for me to make work? I would greatly
appreciate your help though...
tks

Thanks for the input. typically there would only be one or two entries
in this memo field and instead of creating a separate table /query/
form etc i "thought" it might be easier to have a simple memo field &
button to update it etc.
If i can't get your idea to work at this end i think i'll end up going
the separate table route
thanks again for your ideas/help
S.

 

[BruceM]

The code is intended to update the single field by copying the current
information as strOldText and adding it to the end of the new text.

If there is a chance you could have more than two separate entries (as you
are describing for MemoField) it is better to have a related table. For
people accustomed to the spreadsheet approach it may be more comfortable at
first to think of it as you are, but once you become accustomed to thinking
in database terms you will have little inclination for mashing several
separate pieces of data into a single field. You say it is "typical" that
there will be only one or two entries, but what if there are three or four?
Or maybe there will be a change in the approach, so that many entries
becomes the norm. Saving yourself from creating a table now will very
likely limit you in the future, and it robs you of a lot of the power and
flexibility of a relational database.

Let me stress that my proposed solution is not what I recommend, but merely
shows a way of accomplishing what you seek. Even if you can get it to work,
I encourage you to rethink your approach.

If this seems complicated, it will be more so when you append your new
data
to the existing data. As has been mentioned elsewhere in this thread, a
related table is the way to go about this.

Maybe it would be something like this if you insist on the clunky
approach:

Dim strOldText as String, strNewText as String, strUser as String

strOldText = Me.[MemoField]
strNewText = Me.[txtTextControl]
strUser = {function or field to identify current user)

Me.[MemoField] = strUser & Now() & strNewText & _
vbCrLf & strOldText

Me.txtTextControl = Null

This assumes strNewText is an unbound text box in which the user types
"Customer notified of delivery" or whatever needs to be said, and that you
have a way of identifying the user so that you can assign a value to
strUser. MemoField is the field in which you are storing this unruly
collection.

Once again, you should not do it this way, but if you are going to go
ahead
with it I suppose this is a way to use a micrometer as a glue clamp. I
would be interested in knowing why you do not want to use a related table.
If it is a case of not understanding how to implement it, there are
resources to guide you.

hi,

The problem is the next time the user updates the field (via an update
button i've attached the code below to) , they delete the previous
data that was in the field.I would like the new data to be placed
before the old data and the old data to be moved down one line
(vbCrLf)
Private Sub Command6_Click()
Me.Note__invoice_notes_ = Now() & " - " & Forms![User ID and NOW()]!

Try this

Dim Username As String
Username = functionToGetTheUsername() ' OrField
Me![Note__invoice_notes_] = Now() & " - " & Username & _
vbCrLf & _
Me![Note__invoice_notes_]

mfG
--> stefan <--

Tks Stefan, this is getting too complicated for me to get my head
around.
How about a different approach.
How would I get the button to do the following once 'clicked' ?

1) go to [Memo field]
2) drop everything down one line
3) take the text from [text field] (this would be the Now() and user
name info)
4) place it on top of the data in the memo field

so the memo field would read something like this

BOB 5/8/2009 10:45:29 AM - Customer notified of delivery
BOB 5/8/2009 9:45:29 AM - Customer notified of delay

I think this may be easier for me to make work? I would greatly
appreciate your help though...
tks

Thanks for the input. typically there would only be one or two entries
in this memo field and instead of creating a separate table /query/
form etc i "thought" it might be easier to have a simple memo field &
button to update it etc.
If i can't get your idea to work at this end i think i'll end up going
the separate table route
thanks again for your ideas/help
S.