 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / December 2005Automatically adding data to a memo field (using a Macro?)
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,  SignatureQuestion Mark 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. > 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 [quoted text clipped - 14 lines] > -- > Question Mark 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? SignatureQuestion Mark > 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. [quoted text clipped - 30 lines] > > -- > > Question Mark 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. > Thanks John. I am going to seriously consider your approach. > [quoted text clipped - 41 lines] > > > -- > > > Question Mark 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. SignatureQuestion Mark > 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 [quoted text clipped - 50 lines] > > > > -- > > > > Question Mark 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 > 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 [quoted text clipped - 59 lines] > > > > > -- > > > > > Question Mark BINGO! Yes! Of coures! Thanks, John. SignatureQuestion Mark > Do you have the value you want (400 or whatever) in a control on the form? > [quoted text clipped - 66 lines] > > > > > > -- > > > > > > Question Mark 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 SignatureQuestion Mark |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.