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 ready this.

[June7]

Pretty much anything is possible with enough code. The more 'user friendly', the more code.

Really need to provide your data structure. Why is Excel involved?

[Minty]

The process would probably be simpler if you had one table and a field to identify the type of document?
You normally wouldn't have a table per type of item, this isn't very normalised, and also means you have to create queries per table to identify what needs tracking, and updating.

One table would mean one set of queries to accommodate all the document types, and if another document type arrives you aren't recreating the wheel again.

You also won't need to duplicate the data into other tables unnecessarily?

[CJ_London]

struggle to understand your design concept. My first question would be why have separate tables for “Letter”, “Response”, “Notice” etc. I would expect a single table with a field to include the 'type' - i.e. “Letter”, “Response”, “Notice” etc

secondly, are these physical documents - pdf, doc, xls whatever? or are they just text of the contents stored in a field. If the former are they being stored in the db or externally and the db stores the path?

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.

Assuming the PK is an autonumber. it is created as soon as a new record is started, if the new record is cancelled then the PK is abandoned never to be used again. So you have the information to include as a field in the 'new letter' record when that is created. This may or may not work for you, as I said, I'm struggling to understand your design.