Automatically adding data to a memo field (using a Macro?)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am automating a vehicle fleet operation. Two of the tables are tblDrivers
and tblVehicles (both with lots of fields) including memo fields DCmmts and
VCmmts respectively. I hope to post a few selected events to the respective
memo fields for historical purposes. For example, "12/24/05 22:00 Issued
Unit 400" on DCmmts for Santa and "12/24/05 22:00 Issued to Santa" for VCmmts
for Unit 400. I would like to do so without loosing what is already posted.
For example, Santa's DCmmts might now look like "12/24/04 22:00 Issued Unit
240; 12/25/04 05:00 Turned-In Unit 240;12/24/05 22:00 Issued Unit 400". The
application is being used to perform and track these events and many more
(e.g. print the signout sheet) but I don't want to carry the overhead for
large historical tables for more than say 30 days.

I there a way to do this without getting beyond Macros? I'm still learning
Access and am virtually illiterate in VB.

Thanks,
 
You would be better off adding a new table(s) to hold the comments. That way
you can easily delete older comments, search comments by date range, etc.

Table: tblLeaseComments
Field: DriverID
Field: VehicleID
Field: ActionDate
Field: Comments

You could also split this into two fields on for driver comments and one for
vehicle comments..

Adding comments to a memo field should be fairly simple, but removing part of
the data from a memo field can be fairly complex when you need to identify the
line(s) by a date and time field.
 
Thanks John. I am going to seriously consider your approach.

However, I would love to get your concept on how I can add to a memo field.
Only selected events will be posted to the memo field and I would never want
to edit any comments out - that's why I want to add them to the memo field -
so they are permenantly associated with the record.

Can you help?
 
I don't know a way to do this with Macros. I would use VBA code in the form to
do this. You can concatenate (add on) to the field by using an expression such as

Me.Comments = Me.Comments & vbCrLf & Format(Now(),"mm/dd/yy hh:nn") & " Issued
unit 400"

The problem is where you get the additional information and identifying when it
should be added.



Question said:
Thanks John. I am going to seriously consider your approach.

However, I would love to get your concept on how I can add to a memo field.
Only selected events will be posted to the memo field and I would never want
to edit any comments out - that's why I want to add them to the memo field -
so they are permenantly associated with the record.

Can you help?
Click to expand...
 
Yes,

I follow what you did. The only thing I need to do differently is to insert
the unit number (e.g. "400") as a variable.

Thanks
 
John, I follow everything you did but I need one thing more. How do I pick
up the "400" as a variable? By the way of explanation, posting the info to
the memo field will be the last step after actually performing the procedure
on the database. (e.g. Open the "Issue" form, fill in the correct
information - probably from drop down lists - and then clicking the Command
Button that exicutes the postings in the appropriate tables.
 
Do you have the value you want (400 or whatever) in a control on the form?

IF so then add that in.

Me.Comments = Me.Comments & vbCrLf & Format(Now(),"mm/dd/yy hh:nn") & " Issued
unit " & Me.SomeControlNameWithTheWantedValue

Question said:
John, I follow everything you did but I need one thing more. How do I pick
up the "400" as a variable? By the way of explanation, posting the info to
the memo field will be the last step after actually performing the procedure
on the database. (e.g. Open the "Issue" form, fill in the correct
information - probably from drop down lists - and then clicking the Command
Button that exicutes the postings in the appropriate tables.
Click to expand...
 
Back
Top