Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Red face Unanswered: Add multiple records to same table from subform

    Ok where to begin,

    Ive only started using Access 2010 since the beginning of January and have googled almost every problem to date. This forum has been a gold mine of help but after days of searching I cannot find a solution for this problem .

    So Im hoping you guys can help.

    Some background

    My db is used to show the status of material through a manufacturing process.
    We start with one slab of material which gets cut up in to many parts.

    The db works so far however Ive come to the conclusion that the date was not normalized correctly. So Ive created the following tables to fix this.

    I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.


    Table 1 has the following fields

    Cast Number
    P/O
    UST Status
    UST Comment

    Table 2 has the following fields

    Cast Number
    Blade ID (which will be 1 to 32)
    Status
    Comment

    I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!

    I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.

    I did have 32 status fields for every part but realised that meany were left null. Am I even going down the right road here?

    I'm using, Access 2010 with Win 7


    I hope that makes sense... I'd appreciate any and all help please.

  2. #2
    Join Date
    Feb 2013
    Posts
    5

    Question

    Any joy with this. Please

    Ive attached a mock db which should help explain what Im trying to achieve.

    I have to admit my knowledge of VBA is not the best.

    I had imagined that each text box would require an after update or before update script that first defines its associated blade ID. Then write that ID to the next record within the BladesStatus Table along with the date entered within the text box.

    Thank you so much for any help.
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're breaking normalisation with your proposed form design. Blade ID should be a field within the table that forms part of the key, along with foreign key (cast ID).

    I will see if I can redo your database accordingly.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Right, I've re-created the tables and the forms. I've renamed pretty much all of the fields, as you should avoid characters that aren't letters, numbers and underscores. I also changed one or two field data types - store dates as dates rather than text.

    I've also defined the relationship as one to many. Having a one to one relationship means that each cast can have exactly one blade.

    Have a look at the tables and forms - it should give you a starting point.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Feb 2013
    Posts
    5
    Hi Weejas, thanks so much for having a stab at this!

    I understand what you've done with the subform in datasheet view but what I need is to have the form in single form view. I thunk that this will require unbound text boxes and some vba to control the forms data entry characteristics.

    The db I attached is a very small part of what I’ve designed so far, there are actually 6 subforms which show or hide up to 300 blade id bound text boxes dependent on a combo box selection based on the product type. I’ve written some vba to control their visibility and also there position/formation as that changes based on product.

    (To clarify I currently have a master table and 6 blade status tables with fields for up to 50 blades.)

    This is all to aid data entry and viewing. Having catered for the users I feel that I’ve neglected the structure of the db. I have populated it with a year’s worth of data and it responds as it did with just the test data. However I do worry about its performance in 5 years time.

    The data sheet view would work but would sacrifice user functionality and add to the data entry required. I have tried exactly as you have suggested but with some script to populate each blade id with "1 to 50" so the records for each are there in the subforms once the master table has a new cast record. But this in its self would generate needless records.

    Am I barking up the wrong tree with this, I didn’t know what a table was before xmas. It works now should I stick with what I’ve got?

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you go with a fixed number of child records per cast, you will end up with useless records taking up database space and slowing down queries. The idea of a one-to-many relationship in a relational database is that the child tables have as many records as required. Most of the time that I've used (or seen) these relationships in a form with one or more subforms, the subforms have been set to datasheet for ease of use. If you set the form up properly, you could have a continuous view - either way, the idea is to see all (or as many as possible) of the child records at once.

    On the other hand, if you are adamant that each cast must have the full complement of blades, then you could have a model in which each of the child tables has one record with a link back to the parent cast and a field for each blade. It's a long way from accepted database normalisation, but the database has to be fit for your business model too. Normalisation is a technique that (among other things) makes your database easier to maintain and can be faster for disk operations, but there is a trade-off in terms of reporting and data entry standards that are required.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Feb 2013
    Posts
    5
    I completely agree, but a full complement of blades is exactly what i don’t want. I want only the records that are required and new ones generated when and if needed.

    The reason I want a single form view is to have the best of both worlds. and efficient db with an efficient user interface.

    I thinking that i need the vba to allow an unbound text box to have a defined blade id then once that text box is populated it would trigger the code to populate a new record in the blade status table with the text boxes designated blade id in one field and the text box contents in the status field. The same text box needs code to display that entered data.

    So what I'd have would be 50 unbound text boxes that would display the blade status if there is a record available based on the cast number and blade ID and also allow data entry to those records.

    Could the text boxes be bound to an append query somehow that data entered would add records based on the cast number and the text box label?

    I guess i need the ability of the datasheet view with the customization of the single form view. Due to my inexperience with access I’m not sure if this is possible.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The reason I want a single form view is to have the best of both worlds. and efficient db with an efficient user interface.
    This is exactly what you won't have if you design a form with the maximum possible number of fields and use VBA to customise them on the fly. Each time you open the form, amend a record or move to another, the VBA has the be run.

    Using the Jet engine to pick up the child records and display them in a subform is far more efficient.

    So what I'd have would be 50 unbound text boxes that would display the blade status if there is a record available based on the cast number and blade ID and also allow data entry to those records.
    Um, if you're only showing the fields for the columns that have data, how will the users be able to enter data into new columns?

    I guess i need the ability of the datasheet view with the customization of the single form view.
    Investigate reworking the subform that I gave you, and then set it to allow continuous only. I think that this might be an acceptable compromise.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Feb 2013
    Posts
    5
    Thanks for your patience weejas ill have a play with the subform and see what i can come up with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •