How to Clear Field when exiting Record?

[MyEmailList]

I have two note fields on my form named...

1 - Today:
2 - History:

Client enters text into the "Today" field

When client exits the record that data is added to the "History"
field...

then clear the data from the "Today" field so it is blank the next
time they return to the record.

Can I do this with a marco? Or must I do some vba?

Can anyone get me started with some vba code to...
- copy data from one field and add it to the data already in another
when exiting a record
- clear data from a field when exiting a record

thanks for any help.

 

[BruceM]

You could probably do something like use an unbound text box for Today, then
in the form's Before Update event you could have code to append the text
from the Today text box to the History field and clear the text box, but you
may want to reconsider that approach. A linked History table may be a
better choice, where each Today entry is a separate field in the History
table. If you are determined to use a single History field, why not just
edit the field?
I don't know if you could use a macro. I first starting working seriously
with Access in the Access 2000 version, so did not need to use macros for
much of anything. I would use VBA if I had to do something like you are
proposing, but then I doubt I would be appending data to an ever-growing
History field.

 

[MyEmailList]

I considered the multiple table approach but need to do this project
in a single table.

We don't want anyone to be able to edit the "History" field.

I haven't programmed in years so need help with VBA code...

Maybe something like this in the proper VBA/Access syntax

On Lost Focus 'or however you tell Access to do this when client exit
the record
Let MyTable.History = Date & MyTable.Today & CRLF & MyTable.History
Let MyTable.History = ""
End

Maybe some VBA/Access programmer could help me with the code here as
you can see I need the help

thanks.

=====

 

[BruceM]

I considered the multiple table approach but need to do this project
in a single table.

That makes no sense. Why does it need to be a single table?

We don't want anyone to be able to edit the "History" field.

I haven't programmed in years so need help with VBA code...

Maybe something like this in the proper VBA/Access syntax

On Lost Focus 'or however you tell Access to do this when client exit
the record
Let MyTable.History = Date & MyTable.Today & CRLF & MyTable.History
Let MyTable.History = ""
End

Maybe some VBA/Access programmer could help me with the code here as
you can see I need the help

thanks.

In the form's Before Update event you could do something like:

Dim strToday as string, strHistory as String

strToday = Me.txtToday
strHistory = Me.History

Me.History = CStr(Date) & ": " & strToday & vbCrLf & strHistory

In the form's Current event:

Me.txtToday = ""

txtToday is the unbound text box into which the Today information is
entered. History is the memo field in which the mishmash of data will
appear. There is no need to write to a Today field and then clear the
field, but you could probably adapt the code to do that if you wish to
compound the complexity of a cumbersome system.

In case you haven't picked up on it, I think that jamming the date and some
text into an ever-growing History field is most likely not a good idea.

 

[MyEmailList]

Bruce,

Thanks for the help. The code snippit should get me started.

Regarding using a single table for this project... and a single
"History" field in each record to accumliate the data...

I understand you would do this database design differently... and
given different circumstances so would I... but it is what it is and
so on we go.

Regarding the "unbound text box" you mentioned ... I'm not sure I'm
clear on what that is... is it a seperate unbound table (meaning not
related to any other table) with only one field... a note field to
collect the "Today" text ??

Or is it a popup dialog that allows the user to key in some data in
response to a question?

I do Access so infrequently that I need reminding what and how... so
thanks again for your help.

Mel

 

[BruceM]

A control in the Access world is just about anything you can put onto a form
or report: text box, check box, label, line, etc. Some controls such as
text boxes, combo boxes, list boxes, and check boxes may be bound to a
field, in which case they are bound controls. They may also be unbound.
Some controls such as labels don't have the option; they are always unbound.
An unbound text box is one that does not have a field in the underlying
table (or query) as its Record Source property. You can give it a name such
as txtToday. Thereafter you can refer to its content in code.
You could also use an Input Box instead of the text box. Check Help for
more information about that. The text box is probably the simplest choice.

Dim strToday as String, strHistory as String ' defines variables
strHistory and strToday as text
strToday = Me.txtToday ' the value of strToday is the contents of
txtToday
strHistory = Me.History ' the value of strHistory is the contents of the
History field

' The History field is redefined as the date plus strToday plus the old
contents of History
' Plus a few odds and ends described below
Me.History = CStr(Date) & ": " & strToday & vbCrLf & strHistory

CStr(Date) converts today's date to a text string. The double quotes
surround a literal value. vbCrLf moves to a new line. The apostrophe in
the code denotes remarks that are not part of the code.

When you wrote "I considered the multiple table approach but need to do this
project in a single table" it suggested to me that using a single table was
a design choice.

I was not suggesting a single History field in each record. I don't know
what history this is, but presumably the main record is about a person or
ongoing event or something that is updated frequently. My suggestion was
that each main record could have any number of History records. Each Today
entry would be separate record in the History table. Using the capabilities
of Access to establish related records, those History records would be
associated with a single main record, just as each pay record is associated
with an employee rather than being stuffed together with money, text, and
dates as a string of text in the employee's main record. There are many
advantages to this approach, one of which is that you can look at History
over a range of time rather than having to sort through all of the History
that was ever entered for the main record (the entire contents of the
History field in the current set-up). However, I don't know the purpose of
this database, so I can only guess about the purpose of History.