From a form create a new record in another table then add pk of new record in form field?

[mesb032870]

I am new so sorry if this does not sound tech enough, I am not a professional in anyway and I could use some help from some of you that obviously brilliant at this....

I have a simple table that tracks incoming documents from a client to start a clock for response. I have a form that my admin enters these documents into. For each type of document I have a separate table to track the many, many types of documents then send. For example “Letter”, “Response”, “Notice” and many others.

What I would like to do is provide the form with a series of buttons for each type of document (let’s use “letters” for now) that would add the “letter” to the proper table and then update the current incoming document record with the PK from the “letter” table.

The idea is that she would enter the new document, it would create the new “letter” record and then when she was finished and wanted to access the new “letter” she could just go to that form and take the next step in the workflow.

Would be happy to provide table info if needed but I wanted to see if that kind of pass back and forth was possible or if there is just a better idea. My admin is super smart but we handle a lot each day and going from table to table, excel sheet to excel sheet is a waste of her talents and I don’t trust anyone else to get this right.

Thanks for reading this.

 

[Micron]

I have a form that my admin enters these documents into

What does this mean, storing the document in an Access table? Not advisable.

For each type of document I have a separate table

This would mean your db is not normalized. One table for documents; a field to categorize the type as well as identify other attributes such as document ID. Ideally, another table (lookup table, not lookup field) to choose the type to record in the type field so that the type values are controlled.

series of buttons for each type of document

An option frame with option buttons would be better, and only one command button to proceed based on the option chosen. However, if using one table I'm not seeing the point of this.

when she was finished and wanted to access the new “letter” she could just go to that form

Why wouldn't this be the same form? Or does that statment have something to do with the "workflow"?

If you can think of it, it can probably be done. The limitations are time and knowledge. However, without understanding what some of your terms mean, there's not much I can offer in terms of a starting point other than speculation. That would be simply involve using a file dialog to choose a file and storing its path in a table. Perhaps an indicator on the form lets you know that there is a file associated with the record. If you're talking several documents per record, then a subform will likely be required. By record, I mean for example, there are 4 documents associated with a customer/issue/work order/PO etc.