Field Update

[michalj]

Hi,
need to create two fields in access 2007 - "progress" and "update progress"
and "update" button as well. Idea is, if I write any text to the field
"update progress" and click on "update" button, system will automatically
move this text to "progress" field. At the same time, system will also save
current data from other fields from this form within "progress" field.
Progress field within the form would be "read only".
e.g.
===============================
Date/Time User
===============================
Status Priority
Category
Sub-category
--------------------------------------------------------------
free flow text .....

It`s like a column history field, which is available in ms access 2007
templates, but this should contain much more details. Don`t want the "chat"
format which is default in column history template [Version Date/Time] text...
. .

Really, would appreciate step by step, how to do this.
Thnx
Michal

 

[Allen Browne]

You could use the AfterUpdate event procedure of [progress update] to append
the value to your [progress] field, e.g.:

Private Sub progress_update.AfterUpdate
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]
End If
End Sub

The problem with that is that every time the user types into the box, you
append the value. So, if they typed:
Pour progress today
and then (after tabbing to the next control but before saving) realised the
mistake and changed it to:
Poor progress today
your AfterUpdate event will give both lines (even though it's only one
edit.)

For that reason, you need to use the AfterUpdate event procedure of the form
(not control) to make this update. That introduces 2 problems:
a) The form is now dirty again as soon as you save it.
b) You can't tell in Form_AfterUpdate whether it was changed or not (i.e.
the OldValue of the controls is not available.)

You can solve both, but you will need to use Form_BeforeUpdate to set a flag
to indicate if Form_AfterUpdate should trigger a re-update or not.

 

[michalj via AccessMonster.com]

You could use the AfterUpdate event procedure of [progress update] to append
the value to your [progress] field, e.g.:

Private Sub progress_update.AfterUpdate
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]
End If
End Sub

The problem with that is that every time the user types into the box, you
append the value. So, if they typed:
Pour progress today
and then (after tabbing to the next control but before saving) realised the
mistake and changed it to:
Poor progress today
your AfterUpdate event will give both lines (even though it's only one
edit.)

For that reason, you need to use the AfterUpdate event procedure of the form
(not control) to make this update. That introduces 2 problems:
a) The form is now dirty again as soon as you save it.
b) You can't tell in Form_AfterUpdate whether it was changed or not (i.e.
the OldValue of the controls is not available.)

You can solve both, but you will need to use Form_BeforeUpdate to set a flag
to indicate if Form_AfterUpdate should trigger a re-update or not.

need to create two fields in access 2007 - "progress" and "update
progress"

[quoted text clipped - 24 lines]

Thnx
Michal

Hi,
thnx for reply.

I have inserted the Aft Upd event procudure of progress update field as
follows:

Private Sub progress_update.AfterUpdate
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]
End If
End Sub


once I put any comment to progress update, response is:

The expression After Update you entered as the event property setting
produced the following error: Expected: end of statement.......

What `m I doing wrong pls?

Just one additional question, regarding the problem, you have described above:

What about to create the "Update Button", as I mentioned?

Thnx to describe the problem and what to do step by step, as not very
familiar in programming.
Michal

 

[Allen Browne]

In teh code window, choose Compile on the Debug menu. Fix any errors
identified there.

There are so many ways a record can be saved that using an 'update button'
is not reliable. Examples:
- menu
- toolbar
- keyboard (Shift+Enter)
- mouse (save button on A2007 ribbon)
- tabbing past the last control
- closing the form
- closing Access
- applying a filter
- changing the sort order
- ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You could use the AfterUpdate event procedure of [progress update] to
append
the value to your [progress] field, e.g.:

Private Sub progress_update.AfterUpdate
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]
End If
End Sub

The problem with that is that every time the user types into the box, you
append the value. So, if they typed:
Pour progress today
and then (after tabbing to the next control but before saving) realised
the
mistake and changed it to:
Poor progress today
your AfterUpdate event will give both lines (even though it's only one
edit.)

For that reason, you need to use the AfterUpdate event procedure of the
form
(not control) to make this update. That introduces 2 problems:
a) The form is now dirty again as soon as you save it.
b) You can't tell in Form_AfterUpdate whether it was changed or not (i.e.
the OldValue of the controls is not available.)

You can solve both, but you will need to use Form_BeforeUpdate to set a
flag
to indicate if Form_AfterUpdate should trigger a re-update or not.

need to create two fields in access 2007 - "progress" and "update
progress"

[quoted text clipped - 24 lines]

Thnx
Michal

Hi,
thnx for reply.

I have inserted the Aft Upd event procudure of progress update field as
follows:

Private Sub progress_update.AfterUpdate
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]
End If
End Sub


once I put any comment to progress update, response is:

The expression After Update you entered as the event property setting
produced the following error: Expected: end of statement.......

What `m I doing wrong pls?

Just one additional question, regarding the problem, you have described
above:

What about to create the "Update Button", as I mentioned?

Thnx to describe the problem and what to do step by step, as not very
familiar in programming.
Michal

 

[michalj via AccessMonster.com]

In teh code window, choose Compile on the Debug menu. Fix any errors
identified there.

There are so many ways a record can be saved that using an 'update button'
is not reliable. Examples:
- menu
- toolbar
- keyboard (Shift+Enter)
- mouse (save button on A2007 ribbon)
- tabbing past the last control
- closing the form
- closing Access
- applying a filter
- changing the sort order
- ...

You could use the AfterUpdate event procedure of [progress update] to
append

[quoted text clipped - 64 lines]

familiar in programming.
Michal

Hi,

thnx, have found one error:

Private Sub progress_update_AfterUpdate() ....replaced "." by "_"

but can`t unerstand syntax error of:
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]


regarding update button:
Idea was, if you put some text into progress update, text will remain there,
unless you click on update button. Consequently system will auto-move the
text frm progress update to progress.

thnx
Michal

 

[Allen Browne]

Underscore is a line continuation character. You can remove it and bring the
text from the next line back on the the one you removed the underscore from.
I didn't understand the issue with the dot.

The update button is fine for manual updates. I don't follow how it gives
you automatic updates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

In teh code window, choose Compile on the Debug menu. Fix any errors
identified there.

There are so many ways a record can be saved that using an 'update button'
is not reliable. Examples:
- menu
- toolbar
- keyboard (Shift+Enter)
- mouse (save button on A2007 ribbon)
- tabbing past the last control
- closing the form
- closing Access
- applying a filter
- changing the sort order
- ...

You could use the AfterUpdate event procedure of [progress update] to
append

[quoted text clipped - 64 lines]

familiar in programming.
Michal

Hi,

thnx, have found one error:

Private Sub progress_update_AfterUpdate() ....replaced "." by
"_"

but can`t unerstand syntax error of:
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update], Null, vbCrLf) & _
Me.[progress update]


regarding update button:
Idea was, if you put some text into progress update, text will remain
there,
unless you click on update button. Consequently system will auto-move the
text frm progress update to progress.

thnx
Michal

 

[michalj via AccessMonster.com]

Underscore is a line continuation character. You can remove it and bring the
text from the next line back on the the one you removed the underscore from.
I didn't understand the issue with the dot.

The update button is fine for manual updates. I don't follow how it gives
you automatic updates.
[quoted text clipped - 38 lines]

thnx
Michal

Hi, the syntax is now:

Option Compare Database

Private Sub progress_update_AfterUpdate()
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update]), Null, vbCrLf) & _
Me.[progress update]
End If
End Sub


As per compile issues, there is everything fine now. progress and progress
update fields are as memo and progress update is set to append only. But
nothing happens. If I save&close the form, nothing happens. text is remaining
in progress update field....

thnx
m.

 

[Allen Browne]

Add the line:
MsgBox "This ran"
to the top of the procedure.
If that doesn't execute, set the AfterUpdate property (in the Properties
box) of your text box to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Move the code into the right place.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Underscore is a line continuation character. You can remove it and bring
the
text from the next line back on the the one you removed the underscore
from.
I didn't understand the issue with the dot.

The update button is fine for manual updates. I don't follow how it gives
you automatic updates.

In teh code window, choose Compile on the Debug menu. Fix any errors
identified there.

[quoted text clipped - 38 lines]

thnx
Michal

Hi, the syntax is now:

Option Compare Database

Private Sub progress_update_AfterUpdate()
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update]), Null, vbCrLf) & _
Me.[progress update]
End If
End Sub


As per compile issues, there is everything fine now. progress and progress
update fields are as memo and progress update is set to append only. But
nothing happens. If I save&close the form, nothing happens. text is
remaining
in progress update field....

 

[michalj via AccessMonster.com]

Add the line:
MsgBox "This ran"
to the top of the procedure.
If that doesn't execute, set the AfterUpdate property (in the Properties
box) of your text box to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Move the code into the right place.
[quoted text clipped - 28 lines]

remaining
in progress update field....

Hi,
tried as follows:

Option Compare Database
MsgBox "This ran"

Private Sub progress_update_AfterUpdate()
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update]), Null, vbCrLf) & _
Me.[progress update]
End If
End Sub

system response after Debug/Compile Err: "Compile error: Invalid outside
procedure" and "This ran" is highlighted. Similarly same response is on the
form

thnx
Michal

 

[Allen Browne]

Place the MsgBox inside the procedure, i.e. first line after:
Private Sub ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Add the line:
MsgBox "This ran"
to the top of the procedure.
If that doesn't execute, set the AfterUpdate property (in the Properties
box) of your text box to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Move the code into the right place.

Underscore is a line continuation character. You can remove it and bring
the

[quoted text clipped - 28 lines]

remaining
in progress update field....

Hi,
tried as follows:

Option Compare Database
MsgBox "This ran"

Private Sub progress_update_AfterUpdate()
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update]), Null, vbCrLf) & _
Me.[progress update]
End If
End Sub

system response after Debug/Compile Err: "Compile error: Invalid outside
procedure" and "This ran" is highlighted. Similarly same response is on
the
form

 

[michalj via AccessMonster.com]

Place the MsgBox inside the procedure, i.e. first line after:
Private Sub ...
[quoted text clipped - 30 lines]

the
form

Hi,
done as below, but nothing happens. Once you wriote something to progress
update, still remains in this box. Btw, what shoule be in control source ...
("progress update")?

Option Compare Database

Private Sub progress_update_AfterUpdate()
MsgBox "This ran"
If Not IsNull(Me.progress) Then
Me.progress = Me.progress & _
IIf(IsNull(Me.[progress update]), Null, vbCrLf) & _
Me.[progress update]
End If
End Sub


thnx
m.

 

[Allen Browne]

I'm going to have to let you do your own debugging.

The first step is to get the code running.
The next step is to trace what steps it is taking.
Use F9 to create a break point, and F8 to single-step through.

We can't debug it for you.