Help with Forms, fields appear to be locked, One big or many small tables, Another solution?

[bytreeide]

A few things here, first, my focus for this thread is FORM: F3Engineering.

1. I was wondering how the database structure looks. I used to have fewer TABLEs. T3ENG used to contain all of the information for T3BatchPrice, T3EngineeringCost, T3EngineeringSpecs, and T3EngShopCapes. Those all used to be one TABLE. Each record in T3BatchPrice, T3EngineeringCost, T3EngineeringSpecs, and T3EngShopCapes are going to be unique to the same record in T3ENG, so I was wondering if the way that I was setting this up is the right way. Your thoughts and suggestions would be most welcome.



2. I have been having difficulty with adding data to certain controls on FORM: F3Engineering. These would be the Specifications, Capabilities, and Batches; the fields appear to be locked for some reason. I can change information in ENG Cost fields on the form for record 1, but I cannot add information on Record 3. The source of this problem began when I split the tables up from a single "FAT" table to several smaller ones. I can fix this issue by going back to my old ways, but there must be some solution for making this work the way that the community suggested that I do things.

Thanks

bytreeide

Database attached

[CJ_London]

your recordsource for F3Engineering is simply not updateable - too many tables and right joins.

Without going into a detailed analysis, you would normally have a single table per form plus use subforms or combo/listboxes for the other tables.

So for example T3MaterialSpecs. I would have thought you would be wanting to update T3Eng.SpecID, not the related description. To do this:

1. remove T3EngineeringSpecs table from your form recordsource
2. change the T3MaterialSpecs control to a combobox (right click on the control and select Change To)
3. change the controlsource to SpecID
4. for consistency, change the control name (not the label) to SpecID
5. change the rowsource to T3EngineeringSpec
6. change column widths to 0;3;0;0
7. change the number of columns to 4

then for t3ProcessSpecs
8. change the controlsource to =specID.column(2)
9. set the control enabled to false and locked to true

10. then repeat for t3Finishspecs but controlsource would be = =specID.column(3)

Repeat for all the other 'ancilliary' tables in your form recordsource

[bytreeide]

Ajax,

Thanks for the reply. After following your instructions I can see what you want me to do with the Database. However I don't think that the way that you are having me change this is going to be the way that I envision this functioning. The control previously named "T3MaterialSpec" was supposed to be an entry point for material specifications (Aluminum 6061, Stainless Steel 303), not an ID#. I didn't want to set up a combo box like you are having me do, because the Finish and Processing Spec might not be the same every time. I could make these all (Material, Process, Finish Specs) combo boxes (the kind that allow you to add data, this is possible, right?) that way a material spec will not be locked in with a process or finishing spec. This method that I have just suggested might work for the specifications portion of this form, however I can't imagine it working for the ENG costing portion as these costs would all be job specific. I know that right now I have the cost part working for Record 1 and 2, but it isn't working (I can't add data) for 3.

I understand that you are the "Competent Performer" here and I am just the "Novice" but I feel like it would have been better to have the changed the Specifications ID control "T3SpecID" to the ComboBox and not "T3MaterialSpec". Then have the ControlSource for Material Spec be "=T3SpecID.Column(1)" and Process Spec be "=T3SpecID.Column(2)" and Finish Spec be "=T3SpecID.Column(3)". This is at least my understanding. If there is some error to it, then please explain to me the right way of thinking.

Also one last thing, did you mean to "SpecID" or "T3SpecID" (I tend not to infer something if omitted, very literal here)

bytreeide

[CJ_London]

So long as you understand the principle, then you can apply it. I envisioned you would store the SpecID because that is what you are using as a link in your relationships - but the combo can show the name once selected, even though it is storing the ID.

I mean't specID since that is what you have called it in your T3Eng table

[bytreeide]

I must not fully understand the principle of using the combo boxes in this way. I think I see what you are doing, but I don't see how that applies to what I am trying to do.

So, even after following your method I can still not use my form as intended. I cannot type anything in the "Material Spec", "Process Spec", or "Finish Spec" fields.

The only way that I can think that your suggestion would work is if I had information already entered into a table and the selection could be made from the table. Well lets assume for the purpose of this database that the user at this point will dealing with a "blank slate," there will not be a table of materials to be able to select from in the beginning. I want the users to be able to eventually select from a list of materials and if something is not there then to be able to add it to the table. That is what I want to happen. Currently what is still standing in my way is the ability to write anything in the fields above.

One of the things that I have done with Users names, email and ID#'s is create an entry for that allows to pick from a list and add another if necessary, but that required a separate form to make that information. Which worked because these things are always associated with each other. The materials on the other hand do not function exactly the same way, because with one metal, my company might have three different methods for finishing it. [I could make it work, but it doesn't fit my vision for the way that the DB should function]

bytreeide

[CJ_London]

I'm not clear what you are trying to achieve - from what you are saying, it sounds like you would need to add all the fields in T3EngineeringSpec table to your T3Eng table. I refer you back to my original post

your recordsource for F3Engineering is simply not updateable - too many tables and right joins.

Without going into a detailed analysis, you would normally have a single table per form plus use subforms or combo/listboxes for the other tables.

[bytreeide]

Ajax,

Thank you for your help.

bytreeide