Modifying the Issues database on MS Access 2007

[shellaccess]

Hi!

I have what seems to be a basic desire but yet cannot seem to accomplish this. I like the essential idea/template for this sample database. What I want to change, is the option where a comment is added to the history by the RecordSource option. I do not like this option since it cannot be edited and it also creates the Version History tag preceeding the comment. I would like to have a separate table that is called comments instead of what is implemented. I tried using an append query and was unsuccessful. I am not sure if I linked my table relationship correctly? I am assumming it should be one-to-many for every issue should have X amount of comments. I want to incorporate other fields in the comments table that will record the user, time and also allow the deleting/modification (only by the owner of the comment). Can someone suggest how I should have my table relationships setup and if i need to have a query or what the best method should be if the query is not the best choice? I have tried several different methods and am starting to destroy the original template file due to my lack of knowledge. Any help (preferably some step-by-step with explanation so I can understand, would be extremely helpful). thank you!

[June7]

Don't know what you mean by 'comment is added to the history by the RecordSource option'.

If you want to allow multiple editable comments for each issue record, then create a Comments table and consider a subform for data entry of comments.

[Rod]

What I want to change, is the option where a comment is added to the history by the RecordSource option.

Allow me to be pernickety (people get upset if I use the word pedantic ) and correct some of your misunderstanding about this history. Setting the 'Append Only' property to 'Yes' for a Memo data type field instructs Access to collect and store all separate entries made in that Memo data field. Normally when you display this field on a form, only the most recent entry is displayed. (This is editable but see my comment later in this post.) However Access provides a function called 'ColumnHistory' that extracts all the separate entries. This function prefixes each entry with a date and time. This list of entries is not editable and there is no option to hide or change the prefix.

Beware when I said the most recent entry is editable; it is but any change creates, as far as Access is concerned, a new entry to add to the list.

You mention 'RecordSource.' This actually has nothing to do with the history feature. 'ColumnHistory' requires amongst other arguments the name of the table from which to extract the history. In this case the argument is specified as RecordSource meaning the name of the table to which this form is bound.

Yes you can modify the db to include your own comments structure. First, as you realise, you must define your table and then relate it to the Issues table. The sense of the relationship is that: one issue has zero-to-many comments. Then you must modify the Issue Details form to allow for display, entry and modification of the comments. This is probably best handled as a subform - a continuous form - avoid datasheets for this purpose.

OK, that's it in brief. The Issues db is full of embedded macros, i.e. hidden macros, so you must find and examine each of these to make sure there is no contention with your new work.

If you want to go ahead I can give you step-by-step help but not today. Get back to me.

[shellaccess]

You mention 'RecordSource.' This actually has nothing to do with the history feature. 'ColumnHistory' requires amongst other arguments the name of the table from which to extract the history. In this case the argument is specified as RecordSource meaning the name of the table to which this form is bound.

....Hi-

Yes, that is what i meant and apologize for mixing up the argument. So, essentially this is a nice option but not really feasible for what I need. I would like to set up a one-many relationship having all the data on the form go to 2 different tables. I want everything on the form to go to table 1 and comments only to table 2--seeing that each record can have X amount of comments. I did try to create this relationship and I am not sure what I am doing wrong. Do I need to have a separate append query for the comment box/table 2? The form record source is table 1 since that is where the majority of fields are being saved in. I like the "new line" update option that the columnhistory does using the memobox. I want the same exact end-result...except not using the columnhistory method so that I may modify the inputs if necessary as well as customize the way the date shows by using the Date()/Now() fxns as well as not have the"version history" appearing every time. If you could possible provide a basic step by step that would be great as I am clearly overlooking something to make this happen.

Thank you!

[June7]

Did you consider my comments in post 2?

[shellaccess]

Did you consider my comments in post 2?

I am not sure what there is to consider.. your comment is analogous to saying we need peace on earth--no kidding.. HOW are we going to get it..?

If you read my comments, you will see that I am fully aware of what I need to do and create.. HOW do I create it? Your post is really useless and perhaps just done to add to your post count? I am aware that you are an 'admin' but let's be serious.. your one sentence post stating the obvious of what I already know --- why even bother? The post BELOW yours on the other hand.. definitely useful and clearly shows an effort. Sorry to be so blunt, but I am being honest as how I feel/the impression I was given.

[Rod]

I'm busy today - I'll get back to you soonest.

[Rod]

OK, step 1 is to create a new table for the comments. Click on 'Create' on the menu bar and then 'Table Design' on the ribbon. Here's a picture of what the table design should look like. (Click on it to enlarge.)



When defining the CommentTime, enter 'Now()' (without the quotes) as the default value in the lower pane. This will effectively timestamp each new comment. Issue should be a long number but that's the default anyway. Save the design calling the table 'Comments.'

Step 2 is to design a simple query that retrieves comments in latest to earliest sequence. Click on 'Create' on the menu bar and then 'Query Design' on the ribbon.



Add the newly created comments table to the upper pane. The grid may be completed by dragging and dropping entries from the table to the grid. Save the query design as 'Comments Descending.'

Post when you have done this and I will guide you through the next steps.

BTW I am not using best practice naming and design standards; I am using standards compatible with the MS template application!

[June7]

I made assumption that because you knew a Comments table was required that you knew how to build one and that what you were unsure of was what to do with the new table, hence my suggestion to use the table in a form/subform arrangement for data entry.

Hope Rod has provided enough detail for you to create a Comments table. Do you need additional clarification on building form/subform arrangement?