locked
Tab control form with two subforms... RRS feed

  • Question

  • This is my first Access project and I'm a novice developer, so please be as descriptive as possible. 

    I'm trying to create one single data entry form (frmCommercialDataEntry) where the tab controls are the records in tblCommercialFishers (25 tabs); each tab is one commercial fisher.

    Each tab has a subdatasheet which is tblCommercialCatch. This subdatasheet is the main data entry point for commercial catch of each commercial fisher. 

    I want to set default values for CommFishID (PK for tblCommercialFishers) in each tab to identify all data entered in a specific tab is for one specific commercial fisher, and I want to set default values for multiple fields in the subdatasheet, but I can't set those default values in the table b/c some will be different for each commercial fisher. 

    I created a blank form (frmCommercialDataEntry), added tab controls, then added a subform (subCommFishers - based on tblCommercialFishers) to the tab, and embedded a second subdatasheet (tblCommercialCatch) into the subform.  The subdatasheet is linked to the subform by CommFishID (PK for tblCommercialFishers/FK for tblCommercialCatch). 

    I clicked on the first tab and tried entering data into the fields in the subform header and got this message "Cannot add record(s); join key of table 'tblCommercialCatch' not in recordset."  I'm not sure what this means and I'm not sure how to link subCommFishers to frmCommercialDataEntry.  Any suggestions?

    Monday, February 13, 2012 9:01 PM

Answers

  • Hi,

    May I suggest an entirely different approach?

    When you say that "each tab i one commercial fisher", there's an alarm going off as this indicates that your data structures are not normalized.  This is an extremely common data modeling error referred to as "flat" or "spreadsheet-like" data structures rather than an actual Relational Database structure.

    Normalization is a huge thing, and absolutely required for working with databases.  It's like the foundation of a house... you have to have it in place before you can start building everything else... indeed, a good portion of a database developer's time is spent at the very beginning with a paper and pencil, defining the data model that the entire application will be built upon.

    I highly recommend reading on Normalization and Relationships.  There's an abundance of information on the subject, though if you're looking for a good place to start, in my signature is a link to the Utter Access Wiki.  Go there, click "Articles" in the left hand menu, and you'll see a category named "Normalization".  Click that and you'll be provided with a number of (hopefully) helpful articles.

    hth


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:11 AM
    Tuesday, February 14, 2012 12:07 AM
  • Look in the Form Properties sheet / Data tab. Parent/Child data relationships between Forms and Subforms are defined there.

    Jack Leach's suggestion is probably the first place you need to look - but you do seem to have some good ideas - that "catches" have a many-to-one relationship with boats or fishing companies. I don't see what's wrong with your database structure, personally. I think the problem is in the user-interface.

    You should first try to get this working by reconfiguring parent/child data relationships between form and subforms. I sometimes have trouble with that though - when the Access defaults for recordset updates are not quite what I am looking for. In this case, I remove the parent/child relationships completely and implement cascading updates/ defaults manually using Form events. The "Form_Current" and "Form_Open" events will be particualrly useful to you. If you set up some code that is triggered every time your main form moves to a new record or is opened to start with, you can use VBA code to detect which CommFishID the main form is on / is selected on the main form, and then use VBA code to manipulate the subforms and their controls (=> underlying recordsets along with that). One more thing to watch out for: your code triggered by Form_Current will probably need to check for whether you are looking at a new record (in which case the controls will be blank/ set to defaults, and the recordset will not be initialised for that record yet)...

    I don't like your idea of having separate tabs for each commercial fisher. I've tried that configuration and it's horrible, ergonomically. It's a nice idea but it just doesn't work for user-friendliness, or for maintainability of your code/ form design. You can set up new tabs in your TabCtl using VBA code... You can place a subform in there that is not actually inside a tab but sitting IN FRONT OF the form, pretending to be inside the tab, where its SourceObject is changed when the tab controls are updated/changed... But even if you succeed and get all that running smoothly, the results are not very good - I've tried it and didn't like what came out of that. You'd be much better off with a Combo Box at the top of your main form listing all your commercial fishers. Then respond to the Update event of the Combo Box, by manipulating your subform's RecordSource and various control parameters such as DefaultValue, Enabled, Locked etc.


    Matthew Slyman M.A. (Camb.)

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:11 AM
    Tuesday, February 14, 2012 10:13 AM

All replies

  • Hi,

    May I suggest an entirely different approach?

    When you say that "each tab i one commercial fisher", there's an alarm going off as this indicates that your data structures are not normalized.  This is an extremely common data modeling error referred to as "flat" or "spreadsheet-like" data structures rather than an actual Relational Database structure.

    Normalization is a huge thing, and absolutely required for working with databases.  It's like the foundation of a house... you have to have it in place before you can start building everything else... indeed, a good portion of a database developer's time is spent at the very beginning with a paper and pencil, defining the data model that the entire application will be built upon.

    I highly recommend reading on Normalization and Relationships.  There's an abundance of information on the subject, though if you're looking for a good place to start, in my signature is a link to the Utter Access Wiki.  Go there, click "Articles" in the left hand menu, and you'll see a category named "Normalization".  Click that and you'll be provided with a number of (hopefully) helpful articles.

    hth


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:11 AM
    Tuesday, February 14, 2012 12:07 AM
  • Look in the Form Properties sheet / Data tab. Parent/Child data relationships between Forms and Subforms are defined there.

    Jack Leach's suggestion is probably the first place you need to look - but you do seem to have some good ideas - that "catches" have a many-to-one relationship with boats or fishing companies. I don't see what's wrong with your database structure, personally. I think the problem is in the user-interface.

    You should first try to get this working by reconfiguring parent/child data relationships between form and subforms. I sometimes have trouble with that though - when the Access defaults for recordset updates are not quite what I am looking for. In this case, I remove the parent/child relationships completely and implement cascading updates/ defaults manually using Form events. The "Form_Current" and "Form_Open" events will be particualrly useful to you. If you set up some code that is triggered every time your main form moves to a new record or is opened to start with, you can use VBA code to detect which CommFishID the main form is on / is selected on the main form, and then use VBA code to manipulate the subforms and their controls (=> underlying recordsets along with that). One more thing to watch out for: your code triggered by Form_Current will probably need to check for whether you are looking at a new record (in which case the controls will be blank/ set to defaults, and the recordset will not be initialised for that record yet)...

    I don't like your idea of having separate tabs for each commercial fisher. I've tried that configuration and it's horrible, ergonomically. It's a nice idea but it just doesn't work for user-friendliness, or for maintainability of your code/ form design. You can set up new tabs in your TabCtl using VBA code... You can place a subform in there that is not actually inside a tab but sitting IN FRONT OF the form, pretending to be inside the tab, where its SourceObject is changed when the tab controls are updated/changed... But even if you succeed and get all that running smoothly, the results are not very good - I've tried it and didn't like what came out of that. You'd be much better off with a Combo Box at the top of your main form listing all your commercial fishers. Then respond to the Update event of the Combo Box, by manipulating your subform's RecordSource and various control parameters such as DefaultValue, Enabled, Locked etc.


    Matthew Slyman M.A. (Camb.)

    • Marked as answer by Bruce Song Monday, March 5, 2012 2:11 AM
    Tuesday, February 14, 2012 10:13 AM
  • >> I don't see what's wrong with your database structure, personally. <<

    Having a set amount of objects (fishermen) indicates a severe breach in normalization and is very common among people familiar with Excel but not Access (or people that are not familiar with the 3d rdms approach in general).

    To be fair, I know nothing of the data structure, but generally when someone says they've got 25 tabs, one of each whatever, it means that they've got 25 whatevers setup in the data, and in almost all cases those 25 whatevers should be moved into a child table.

    Very common beginners mistake, but if I misjudged, my apologies.

    That said, I agree with the other mentions about the UI setup and usability, etc.

    Cheers,


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)

    Tuesday, February 14, 2012 10:21 AM