How to create one data entry form for multiple tables without using subforms?

Question

I have, what I think, is a simple db design. 3 tables in a simple hierarchy.

tbl01Doc     tbl02Sec       tbl03Para
DocID (pk)  SecID (pk)    ParaID (pk)
DocName    SectionNum   ParaText
DocDate      SectionTitle   ParaPage
SecID (fk)   ParaID (fk)

I'd like a single form that has only text boxes for the DocName, DocDate, SectionNum, SectionTitle, ParaText, and ParaPage.

I can create this form, and without using sub-forms, but the section and paragraph fields don't allow me to type any text in them. I suspect this is because of where they are in the relationship (i.e. many to one relationship with the tbl01Doc table). I haven't dug into sub-forms much because the "nested Excel table" look is non-starter.

The work flow for the use of the desired looking form is this:

All tables are completely empty,
Open a document and select the first paragraph to enter into the db,
Open empty form,
Paste in the document details,
Paste in the section details,
Paste in the paragraph text and details,
Hit submit/save button (all data saved to tables [new records for all three tables in this case] and text in text boxes remains),
Select another paragraph to enter into the db,
Paste over existing text in paragraph text field box,
Update paragraph details,
Do not change Section or Document details,
Hit submit/save button (records matching the section and document details already exists so no new records in those tables, new record in the paragraph table is created)
Continue doing this for more paragraphs in this same document and section,
Change the section details as you move on to paragraphs in other sections (and since the data is different new records will be created in the section table) [same for the document as you finish with one document and move on to another, only at that time will the document table have a new record added],
Exit the form.

Am I crazy? How doable is this?

Thank you for any assistance and hopefully I've described my problem and desired solution well enough.

Clayton

ps: I would have pasted pictures of my table relationships and the non-working but pretty and the semi-working but undesirable forms BUT... my account isn't verified yet.  Cue sad trombone.

Answer 1

Hi,

If you want to stick with this user interface design, maybe you can consider using an unbound form. After the user enters all the necessary information on the form, you can use a "Save" button to send all the data into their corresponding tables.

Just my 2 cents...

Answer 2

tbl01Doc     tbl02Sec       tbl03Para
DocID (pk)  SecID (pk)    ParaID (pk)
DocName    SectionNum   ParaText
DocDate      SectionTitle   ParaPage
SecID (fk)   ParaID (fk)

Hi Clayton,

What about a little changed design?

tbl01Doc     tbl02Sec       tbl03Para
DocID (pk)   SecID (pk)     ParaID (pk)
DocName      SectionNum     ParaText
DocDate      SectionTitle   ParaPage
             DocID (fk)     SecID (fk)

Edit:

Excuses for the bad alignment.

Now many Sections can refer to one Document, and many Paragraphs to one Section

Imb.

Answer 3

What about a little changed design?

tbl01Doc     tbl02Sec       tbl03Para
DocID (pk)   SecID (pk)     ParaID (pk)
DocName      SectionNum     ParaText
DocDate      SectionTitle   ParaPage
             DocID (fk)     SecID (fk)

Now many Sections can refer to one Document, and many Paragraphs to one Section

I agree.  If the relationships are supposed to be Document 1-->N Section 1-->N Paragraph, then Imb-hb's suggested table design is what you need.

If you implement this design, and build a single form on a query that joins the tables on the appropriate keys, then so long as you include all the requisite key fields in the query you shold be able to get an updatable query.  You may want to write code for your form so that each set of subordinate fields is only enabled when the necessary "parent" record has been filled in; that way, the user won't try to fill in a section or paragraph before the document details have been filled in.

HOWEVER, you (Clayton) did say that you wanted the user to "Hit submit/save button (all data saved to tables [new records for all three tables in this case] and text in text boxes remains)".  Do accomplish that, you would need to either use an unbound form (as suggested by thtDBGuy) or a bound form with *unbound controls*.  In the latter case, the code behind your Save button would simply assign the values from the unbound text boxes to the (non-visible) fields in the form's recordset, and then navigate the recordset to a new record.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Hi Clayton.W.703,

Is your issue solved?

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].