Automatically Add New Memo Field

[Luke]

My boss has been asking me to create forms in which he can add new records at any time. He has different review cycles for each company he owns (either monthly, quarterly, or annually). I have a form called "REVIEWS" where my boss wants to be able to add a memo with a time stamp each time the review cycle is updated. How can I use the "addNew" function so that a new memo text box appears in the form each time the review cycle is completed? My boss wants to be able to see each of his notes for each time he reviews a company so I want to be able to keep the old memos as well. Thanks a lot.

-Luke

[TheShabz]

Have you considered storing all the reviews in a table and displaying them in a subform, with newest on top, and leaving a single textbox for entry purposes? Over time, your form would get cluttered if you add a new textbox every time.

[Luke]

Thanks for your advice. I added a subform but how can I display the notes with newest on top and leave a single text box on top for entry? Thanks.

[TheShabz]

How do you currently store all the review information?

[Luke]

How do you currently store all the review information?

I store all the review information in my main table called "all entity info". I had a separate table for the review information earlier but I started having problems with my primary keys and adding new records to each table. I now have queries and forms called "reviews" based on my "all entity info" table. The "reviews" query and form include the entity, review cycle, last review, and next review. By using the DateAdd function, access automatically updates my query/form to show when the next review date is based on the review cycle (m,q,yyyy) and the last review date. Thanks.

[TheShabz]

First create a SELECT query that will display whatever info you want from the reviews. Make sure to set the sort for the date to "descending." Once you have this saved, go to your form and put in a subform. the subform wizard should pop up. Choose the query you just created to bind it with. Now when you view the form, it will display the contents of that query as a subform on your form. Once you have that working, at the end of whatever code you have to enter the data, throw in a Me.subFormName.Requery and it should automatically update the form whenever you add a new review.

[Luke]

Thanks for the advice theShabz I really appreciate it. I'm still having problems with the form and subform though. I have a form called "Reviews" which contains:

the entity (my primary key),
the review cycle (m,q,yyyy),
the last review date, and
the next review date (DateAdd([Review Cycle],1,[Last Review]).

I want to put a subform in this form with only the "Notes" and the Date/Time the Note was written. However, I want the Date/Time field to show the Last Review Date(s) as well as the Next Review Date so that it can keep updating and get larger each time the review cycle is up and a new note is written. Do you know how I can do this? Basically I would like for the form & subform to look like this:

FORM

Entity: Company Name
Review Cycle: M (for month)
Last Review: 6/1/2011
Next Review: 7/1/2011

SUBFORM

Date/Time Notes

7/1/2011 (next review) "Blank"
6/1/2011 (last review) Last Note
5/1/2011 Notes
4/1/2011 Notes


Thanks for your help.
-Luke

[TheShabz]

If you are already displaying the "last review" and "next review" why do you want it displayed again in the subform? That makes it more difficult as you would have to create a record for the next review without the actual contents of the review. If you create a query, for example, SELECT reviewDate, reviewNotes FROM tblReviews WHERE companyID = Forms!formName!controlWithCompanyID ORDER BY reviewDate Desc, You will have all the reviews for the company selected, with the "last review" on top. The next review date is already displayed on you main form.

[Luke]

Thanks a lot, that makes sense I'm not going to include the last and next review dates in the subform. I now have a form with the entity, review cycle, last review date, and next review date. I also have a subform with "Notes" and "Date/Time" the note was written. The problem I'm now having is that each time I write a note and select a date for a record, the same exact notes and dates show up on each one of my records! I know I can fix this by having the entity name or ID number in the subform. However, when I have the entity (my primary key), in the subform it won't allow me to add a new note due to duplicate values with the primary key. If I have the ID number in the subform, it will allow me to add a new note except it adds an entirely new record. (For example if my ID number is "1" out of 92 records and I try to write another note underneath "1" in my first record, it adds an entirely new record "93"). I am completely lost on how to fix this problem. I just want to be able to write multiple notes and dates for a single record without the same notes/dates showing up on every single record. Thanks a lot for your help.

-Luke

[TheShabz]

The subform should be for display purposes only. Looks like I made some assumptions I shouldn't have. How are you currently entering in new reviews? Is your form bound directly to your table or are you using unbound controls and creating an INSERT INTO query in VBA?

[Luke]

Well I'm not exactly able to enter new reviews or notes because of primary key constraints or adding entirely new records. My form and subform are both bound to my table. I'm not too familiar with unbound controls or creating an INSERT INTO query in VBA. Thanks.

[TheShabz]

Give me a list of all the fields that you want entered into your table. I'll whip something up for you. It's going to be easier to show you than it will be to explain it all.

[Luke]

Ok thank you so much. I would like my "Reviews" Table to include the following:

Entity as the primary key
Review Cycle (month,quarter, or year)
The Last Review Date
The Notes
The Date the Note was written

I don't know if you would want to put the notes and the Date it was written in the Reviews Table though because wouldn't multiple notes and dates conflict with the primary key and adding duplicate values? Thanks.

I want to have the form with the entity, the review cycle, last review date, and next review date with a subform where I can add multiple notes and the date I wrote them at any time. Thank you so much.

-Luke

[TheShabz]

1. how do you decide on review cycle?
2. where are the company names stored?

[Luke]

The review cycle and entity names are for the most part irrelevant right now because my boss wants me to get the general setup for the database before I type in the correct information. However I have the review cycles and entity names on my database if you would like me to attach it so you can use it as a reference. My upload for the attachment failed but I can copy/paste it into a smaller database with less info. Thanks.