Results 1 to 12 of 12
  1. #1
    claytonW703 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    8

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

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

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

    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
    Last edited by claytonW703; 01-16-2018 at 10:24 AM. Reason: formatting

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sub Forms don't have to look like sub forms.
    They certainly don't have to be datasheets, and if you format them as single forms, remove the record selectors, and set the borders suitably you wouldn't necessarily even be able to spot it was a sub form.

    Your workflow may need a little adjustment unless you want the hassle of using unbound forms, your description of "Hitting Save" might be better termed as "goto a new record within your paragraph sub form that doesn't look like a sub form"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see this working the way you describe it. If you cannot edit or add records directly in your query, you cannot do so in a form without using code. However, your description of the process suggests you remain on the first record when trying to add the second paragraph data, so all you will do is over-write what was there, not create a new associated record. A subform is the oft chosen method for one to many relationships. Perhaps you could get away with a split form, but to be honest, my use of them is practically nil, so I can't say for sure if you can add related records in those. Any other method that comes to mind at the moment involves so much effort and code as to be kind of pointless considering there is the old reliable subform method.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    claytonW703 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    8
    Thank you for this reply. I've adjusted how I'm approaching this (I'm now using a subform but formatted as a single form, not a datasheet). I'm also now googling "unbound forms" to infer what you meant.

  5. #5
    claytonW703 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    8
    First a correction...
    tbl01Doc - DocID (pk), DocName, DocDate
    tbl02Sec - SecID (pk), SectionNum, SectionTitle, DocID (fk)
    tbl03Para - ParaID (pk), ParaText, ParaPage, SecID (fk)

    Second... I did as you suggested and I do now have a form that looks like I want until I start entering data. AFter creating the first paragraph record a new form text box appears. To me this is essentially the same as having multiple rows in a spreadsheet visible (even though this is prettier).

    I did get happy when I was actually able to type text into the Sec and Para text boxes. I got even happier when I was able to see the records being added to tbl03Para. So I was surprised when tbl02Sec did create a record with the first bit of text I entered but as I moved on to other sections of the document and continued adding paragraphs the SecID didn't change in the tables even though I'd changed the text in the form. There was only one record in the tbl02Sec table and the foreign key, SecID, in tbl03Para also remained the same as the very first record entered into the form.

    From what I read about unbound forms it may be what I want. I don't really care or need to read any data into this data entry form. I only want it to submit data to the db for [consideration of] record entry. I'm not a great programmer but I'm no noob either. I have a tiny amount of C++, a bit of Java, and (back in the day) was a Perl guru.

    One thing I didn't do on the form that I prototyped after your comment was remove all actions from the text boxes and add a "save/add record" button.

    Lastly, besides needing to write code, are there other drawbacks of unbound forms if I plan on posting/publishing an unbound form to SharePoint?


  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Bound forms have many advantages in that they handle Parent/Child record adding very simply, they automatically save things when you move between records etc. etc. This is one of Access core strengths.

    Unbound forms remove all this lovely inbuilt default behaviour.
    You have to monitor all controls for data change and then make sure that the user wants to save the changes they have made.

    Whist there are genuine occasions to use unbound forms, generally they a few and far between, and they have a steep and often unnecessary learning curve.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    As Minty suggested, using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

    1. Do Data Validation
    2. Prevent Duplicate Records
    3. Do Formatting of Data before it's Saved
    4. Decide whether or not to actually Save a New or Edited Record


    which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

    Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

    Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

    If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

    • You can create an EXE file which gives total protection to your code/design
    • You can distribute the db to PCs without a copy of Access being on board
    • Your data security is far, far better than anything you can do in Access


    Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable, once again, as Minty suggested, but anyone who routinely uses them for everything, has simply made a bad choice, in my opinion, in deciding to work in Access.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    claytonW703 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    8
    Warnings heeded. I'll ignore I ever heard about unbound forms.

    Any hints as to what might be keeping my tbl02Sec and tbl01Doc from updating (ie receiving new records) after I make changes to their fields as well when I'm adding the text from new paragraphs to the form?
    Furthermore, any separate hints as to how to suppress the creation of "rows" of data entry boxes for the paragraph fields (one for each record)? It is not a workable solution to have all the records in the tbl03Para visible when using this form let alone trying to quickly do data entry.

  9. #9
    claytonW703 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    8
    More corrections, as I begin to look at how it's actually working this morning.

    Records are being added to the tbl03Para table fine (I think).
    A single record has been created in tbl02Sec and tbl01Doc and as I change these entries in the form they *change* that one record in each of these tables as opposed to adding a new record (if necessary).

    So I simply need to change the Events associated with moving around the from and only have records added/created/updated/etc. with a "On Mouse Up" event on a button?
    Last edited by claytonW703; 01-22-2018 at 08:11 AM. Reason: clarification

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you change the default form behaviour under the "Other" properties tab under "Cycle" change it to "Current Record", the form won't go to the next record, just cycle around the current one.
    Utilising this you can then add buttons to the main form to go to a New record.
    This same property can be set individually on the sub forms you have, set to either stay on one record (preferred behaviour for you Main form based on your description), or allow new ones to be added (your paragraph sub form for instance).
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by claytonW703 View Post

    ...A single record has been created in tbl02Sec and tbl01Doc and as I change these entries in the form they *change* that one record in each of these tables as opposed to adding a new record...
    You sound as if you're changing data in an existing Record and expecting this to be saved as a New Record...which it isn't...you're simply editing that existing Record! Access is not like MS Word, where you can change data in an existing document and save the changed document as a new document.

    In Access, to create a New Record, you must first move to a New Record, as Minty suggested, and then enter your new data. You can do this by utilizing the native control to move to a New Record, or by adding a custom Command Button to do the same.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're not the only one who thinks that might be the case. See sentence 3 of post 3.
    Welcome to the club!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data entry form for multiple tables
    By Nowbodys in forum Access
    Replies: 1
    Last Post: 06-20-2016, 04:33 AM
  2. Adding data to multiple tables with subforms
    By mooserider2 in forum Access
    Replies: 3
    Last Post: 03-02-2015, 02:41 PM
  3. Replies: 3
    Last Post: 02-11-2014, 03:26 PM
  4. Replies: 5
    Last Post: 08-12-2013, 12:53 AM
  5. Replies: 1
    Last Post: 11-19-2011, 10:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums