Possible child table for cumulative comments

[DanKerr]

Hello,

I am currently working on a form which has a "comments" box for people to update.

We like the data entry guys to use a specific format of the date and time and their initials and then the comment.

Unfortunately they don't always do this properly and miss out some details and have a tendency to overwrite what is in there already.

What I would be looking for is a "previous comments" box which displays everything written previously and another "comments" box that they can update.

When they update the "comments" and move on to another/new record it would add their comment with the date and their user id to the "previous comments" box and would not then be able to edit.

I can do the boxes and the locked editing on the Previous comments but I think it will probably involve a child table to record the date, user id and previous comments and new comments and some type of calculation field?

I am just looking for some pointers as where to start as I don't have too much experience with access and dumming it down would be appreciated.

Thanks

Dan

 

[xenou]

I think you could just create a table for your comments. There isn't a need to be updating anything - just create a new record for new comments, and display all comment records as a list of comments -- i.e, in a subform.

 

[DanKerr]

I think you could just create a table for your comments. There isn't a need to be updating anything - just create a new record for new comments, and display all comment records as a list of comments -- i.e, in a subform.

Hi Xenou,

Thanks for your feedback.

As I am fairly new to access I may be asking a basic question here but I assume that I will need some sort of Primary Key that is the same in both tables so that I can link the records together?

Say for example in the first table I had the following fields:
Account Number
Account Name
Date
Action Taken (Checkbox)
Comments

The account number is the primary key as there can only be one entry per account.

How would I tie the second table in so that it pulls through the correct notes for that account?

thanks

Dan

 

[xenou]

Hi,
your second table would have Account Number as a foreign key:

Comments: (CommentID, Comment, CommentDate, [Account Number])

CommentID is a primary key (probably just an arbitrary autonumber field here). Account Number is a foreign key.

 

[DanKerr]

Hello xenou,

I now have two tables, my main table and form and my second table called Comments table.

In my comments table I have:
Comments ID, User ID, Date Commented, New Comment, Account Number

the UserID is a combo box for them to choose from a list.
the Date Commented defaults to today's date and time and the account number is linked to the main table account number.

I have added a subform to display this on the side of the main form.

When I type in the account number to query an existing record, it pulls through the comments into the fields they were filled in, how do I make those fields just entry fields and display all of the info in a locked display box instead so they cant be edited.

Thanks for your patience and help so far.

Dan

 

[xenou]

This is where you have to make UI design choices. Good luck haha. I'm terrible at that.

I would probably do something like this:

• Set up the comments to display as a continuous subform display (i.e., just show all records).
• Format the subform to make it look more or less invisible except an edge around the subform container -- that is, set back colors to the same color as the main form, set field border edges to be small or invisible and possibly also the same color as the main form. Doing this makes it look like the subform is just something on the main form since you can't really see that its a subform.
• Have a button to "Add Comment"
• If the user clicks the button, open up a second form to add a new comment (this might be bad. You have a lot of choices here, including having the comment subform simply set up to allow adding new records).

 

[DanKerr]

Hi Xenou,

I have partially solved my issue now but something that you mentioned earlier is intriguing me.

I now have three tables, one for my main data, one for my comments input fields and one for a comments table.

When the user enters their comments and clicks on the "submit" button I created, it runs an append query and adds them to the table and then a delete query and removes them from the input table.

this ensures that the fields are blank again when they come to add the next comment if relevant.

which brings me to my point...

I now have a comments table, with a field called "Display Comment" which is the comment I want displayed, it is a calculated field which concatenates the User ID, Date and Comment.

I want to display on the main form all of the records that match the account number so there could be anything from one comment to 20 comments (eg) that I wish to display all in one box if possible, but id like it to show as a "text" or "list" box rather than a datasheet. you mentioned earlier about displaying it all in a list so I am hoping you may know a way.

Thanks

 

[xenou]

Hi, that's pretty easy - you only need to display the comments as a continuous form. That puts them in a list and you don't have the "datasheet" anymore.

 

[DanKerr]

Hi Xenou,

I tried the whole continuous form thing but am only seeing the first record for it.

when I switch it to datasheet view, it shows up with the three records I would expect.

Just to clarify.

I have a SubForm which is set to Continuous View.
It has a "Text Box" in it which links to the revelant field I need displaying.

Is the text box the issue, should it be a list box or something else?

thanks

 

[xenou]

possibly, not sure what your "Text Box" is but that could be an issue.
Create a form from your comments table independently - so you can see how continuous forms work. Then go back to what you have now and play with it. You can even drag the form in as a way of bringing it in to your main form.