Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    I would have my Data Input form as shown in the attached.
    This is just one way of doing this.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  2. #17
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by mike60smart View Post
    I would have my Data Input form as shown in the attached.
    This is just one way of doing this.
    I see you changed my casing from PascalCase to camelCase.
    Hehehe...
    JJ

  3. #18
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by mike60smart View Post
    I would have my Data Input form as shown in the attached.
    This is just one way of doing this.
    First of all, thanks for taking the time to build a form and restructure some of my data.
    I noticed you did some things differently and am just wondering if it's a "you way", a best practice, or if there is some technical reason why that way benefits this particular workflow.

    1: tblEpisodes has SeriesID instead of SeriesName.
    I guess that makes sense as it's pulling linked to another table (I did that same thing in other places).

    2: tblEpisodes SeriesID doesn't use the query for Row Source.
    I just do that to make it more readable for myself and it automates the drop-down for forms.
    It looks like this is repeated in multiple tables.
    Do you just prefer seeing the IDs (the data should be the same, regardless).

    3: tbluScenenames broke the scene names into its own table (which I started out doing).
    Will tbluScenenames just be a super-long list of individual records that are mostly just used one time?
    I usually break data into tables when they have often-repeated data.
    Things like Welcome and Worship will be used every episode, but most of the entries will be on-offs over the course of a year.
    I would, definitely, agree to doing it this way if the majority of the records were used many times.

    4: Does the "u" in tbluScenenames signify something or was it a typo?

    I'm currently adding a new episode.
    It took a few minutes to figure out how things work, but I think I have a good handle on it.
    I appreciate the work you did.
    I'll see how to implement a similar workflow into my database.
    JJ

  4. #19
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi JJ

    In answer to your questions:-

    1: tblEpisodes has SeriesID instead of SeriesName.
    I guess that makes sense as it's pulling linked to another table (I did that same thing in other places).

    A1 - SeriesID is the PK autonumber in tblSeries. This is linked to SeriesID - Number DataType - FK which will automatically populate when
    you add a new Record in the tblEpisodes.
    You were using Lookup Fields in your tables to achieve this which is not a recommended method.

    2: tblEpisodes SeriesID doesn't use the query for Row Source.
    I just do that to make it more readable for myself and it automates the drop-down for forms.
    It looks like this is repeated in multiple tables.
    Do you just prefer seeing the IDs (the data should be the same, regardless).

    A2 - I always name the PK & FK's the same in all Related tables. ID indicating it is an Autonumber (PK) and a Number (FK) DataType

    3: tbluScenenames broke the scene names into its own table (which I started out doing).
    Will tbluScenenames just be a super-long list of individual records that are mostly just used one time?
    I usually break data into tables when they have often-repeated data.

    A3 - If Scenenames is not a repeating name then they should just be entered into the Control as required and NOT looking up a predetermined List.
    Things like Welcome and Worship will be used every episode, but most of the entries will be on-offs over the course of a year.
    I would, definitely, agree to doing it this way if the majority of the records were used many times.

    4: Does the "u" in tbluScenenames signify something or was it a typo?

    A4 - The u is just my method of indicating it is a Lookup Table.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #20
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by mike60smart View Post
    I would have my Data Input form as shown in the attached.
    This is just one way of doing this.
    I just want to thank you, again, for making this form.
    It really sparked some ideas, for mine, and it helped me really jump forward on this project.
    I'll post a new version of the database, when it's finished, so you can see the changes I made.
    JJ

  6. #21
    doubleJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    I think this is about finished.
    Functionally, it does everything that I currently want.
    VirtualChurch-20211115.zip

    I am having one issue where the subform's (scenes) Add buttons open a new form.
    When you tab through the fields and get to a new record (or just click in the new record), it forcibly creates a new record.
    It does that, because there is a hidden field that links the new form window to the subform's window that opened it.
    Before I did that, you could add records all day long and when you went back, they would be gone (in the database, but not linked).
    I created a public variable that I insert into that field and everything works.
    As mentioned, if you accidently go into a new record, that empty record is created.
    You can't Escape out, like you normally can.

    In case you want to mention acDialog, I was using that, but the form opening in a little window in the corner wasn't intuitive for the other users (especially data entry).
    The window opening full just works better.

    As a secondary point, I'm surprised that there isn't built-in functionality to create a new combo box entry by just typing into the combo box.
    I've seen various ways of doing it, and I understand that linked tables complicates things, but none of them seemed any more user-friendly.

    Again, thanks for the help.
    I'll continue making this better, but it's doing what was intended.
    JJ

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. 3 Tier Queries - Consolidate to One?
    By bs0d in forum Access
    Replies: 18
    Last Post: 03-03-2015, 06:41 PM
  2. Managing Contacts in a multi-tier database
    By MSAccessNewGirl in forum Forms
    Replies: 7
    Last Post: 02-27-2014, 12:08 AM
  3. Replies: 6
    Last Post: 05-11-2012, 11:16 AM
  4. Multi-User Design
    By tyrobrio in forum Access
    Replies: 3
    Last Post: 04-30-2012, 08:17 AM
  5. 3RD Tier Combo Box
    By Jademonkey2k in forum Access
    Replies: 4
    Last Post: 11-15-2010, 08:33 AM

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