Trying to create a notes section in an Access DB.

[Coxck]

I am in the process of trying to create a tracker for my work reports. I currently handle about 500 reports per year and I need to track all sorts of information. I have created the general database with all the fields that I want to keep track of, however the last field is kicking me hard. I need a notes section. Each report is for a job site. I have notes for each job site that I want to keep track of. I would like an input field on my form that allows me to input many notes individually and when I pull the report all of the notes for that job site show up on the report.

Currently with a normal short text box, I can put as many notes in the box, but they stay there in the input form. I do not like this because it is too easy to accidentally delete a note.



Are there other ways to create this. I have tried googling for an answer, but I do not think I am asking the correct questions, I have not found anything that relates to this with my searches.

One of the things I was thinking would be to create another table, and that table have its own records. I would then need to pull a query based on the dig I want and the query loads into the report.


Please and thank you,
Cory

[June7]

If you want many notes for each job then a related table is probably best approach. Use form/subform arrangement for data entry. And maybe report/subreport for data output.

Another rather obscure method involves use of Memo (long text) type field and ColumnHistory.

http://www.fmsinc.com/MicrosoftAcces...tory/Index.asp
https://msdn.microsoft.com/en-us/lib...or=-2147217396

I generally try to avoid memo fields because of their issues.

[Coxck]

http://www.fmsinc.com/MicrosoftAcces...tory/Index.asp
I have followed the steps for this and I am able to get the records to load in the immediate section just fine. That part seems to work great. However, The next part, getting it to display on the report does not work. I have even tried to copy the example to a T and I end up with an error. Is there steps missing from that part?

[June7]

I've never used ColumnHistory so just did a test. Works exactly as described. I would have to view your report setup. Post the expression you have in the textbox.

What error?

[Coxck]

My experience with Access is showing here. I have started a new DB and made with the Contact Table and 1 report. I am still able to get the first two steps done. However I cannot get the Text Box in the report to load. I get a #Name? error in the box. I copied =ColumnHistory([RecordSource],"Notes","[ID]=" & Nz([ID],0)) into the control Source box in properties and I set Enabled to No. There is no false for my version.

Am I supposed to use the script from the top of the page? If so where do I put it? I popped it into a module and into the text box, neither of which works.
Sub GetColumnHistory()
Dim strHistory As String

strHistory = Application.ColumnHistory("Contacts", "Notes", "ID=1")
Debug.Print strHistory
End Sub

[Coxck]

I have tried the the Enabled property in both Yes and No actually with no effect

[June7]

No, that code was just to demonstrate output of the ColumnHistory data. It is not needed.

That error usually means Access cannot find referenced item. You are using your actual field names? Of course you are since it works for Debug.Print output.

I tested on both form and report. Works perfect. Since I cannot replicate the issue, if you want to provide db I will analyze your effort. Follow instructions at bottom of my post.

[Coxck]

SUCCESS!! I ended up using =ColumnHistory("Contacts","Notes","ID=1") and I was able to get them to populate. Last question on this issue, What do I put in place of ID=1 so that it automatically pulls from the correct column based on the record I query?

[June7]

"ID=" & [ID]

Interesting, I tested both versions and both worked.