My main form has a subform space called Property which I populate using Sourceobject and linking master/child fields. However, this removes any ability to view the subform as a Form rather than a Table. Is there away around this apparent Access limitation? The main form records are linked to the subform records by a One-To-Many relationship.
I can neither simply right click on the subform as the "Subform->Form" option is removed by Access and the VB property acCmdSubformFormView is warned as "...not Availiable now"
- A main form containing records of different products
- Each record in main form has X number of subforms
- Each subform itself contains records of different data for that specific product..however this must be displayed and be accessible as a form rather than a table due to shear number of data fields and drop down menus
Update: I have found a way to do this!! Let me know if there is an easier way
1. Create Tables for each of my products/items
2. Remove Primary Key and set the ID to Number (long integer) and Yes/Duplicates
3. Create subforms using wizard as Datasheet (table) instead of a Form type SubformFormView won't work by directly defining Sourceobject = Table.Subform
4. Create unbound subforms in main form
5. Assign One-To-Many relationship between Main Form ID and Subform ID
6. SetFocus to subform and run the acCmdSubformFormView command in the main form's Current()
Bug #1: Automation error: Entering values into subform will cause an automation error as for some reason Access doesn't know you are modifying a subform record
Bug #2: New subform records are not recognized by its main form parent and will have some unrelated ID number
This will take care of both bugs. Need to enter in for each subform:
Code:
Private Sub form_current()
If Me.NewRecord Then
Me.ID = Forms![MainForm]!ID
End If
End Sub
This will assign a value to the subform not only letting Access bypass the automation error but also link the subform ID to the main form ID and re-establish the One-To-Many relationship
However, this makes my Add and Delete Record buttons not work. How do i use VB to add/delete current record entry from a Table? There is nothing to distinguish a search on as ID numbers are all the same.
It's still not completely clear what exactly it is you're trying to accomplish, but it does appear that you're heading down the road to complete disaster.
- A main form containing records of different products
First step down the road to ruin. Are you planning on creating a new table every time you need to add a new product? What about the user interface? Create a different form for every product? Try to use one form and programmatically reset the record source every time you need to view a different product in the form?
2. Remove Primary Key and set the ID to Number (long integer) and Yes/Duplicates
Then you lose all the benefits that come with using bound forms, which is one of the most important RAD benefits that make Access the great tool that it is. You would need to write code to handle all record modifications (updates, inserts, deletes).
Private Sub form_current()
If Me.NewRecord Then
Me.ID = Forms![MainForm]!ID
End If
End Sub
Exactly. And one of your first indications that this isn't going to work. If you can provide some more details about your table structure and, not only what you are trying to accomplish, but why as well, someone may be able to offer some sound advice about how you should proceed.
It's still not completely clear what exactly it is you're trying to accomplish, but it does appear that you're heading down the road to complete disaster.
Fairly straightforward. You would use tblProducts (or a query thereof) as the record source.
Why does one entity (Products) need multiple sub forms?
And what do you mean by X number? Is it supposed to vary depending on the Product? If so, why?
Each Product record has a subform for Internal Requirements and Supplier Specs
Again, why would the data related to a Product need to be displayed in different sub forms?
There is general info on each Product like type, date, product number, etc. However, there will be multiple Requirements and Supplier Specs for each material. These requirements and specs will be stored in their own respective tables and pulled up as a subform for each product.
Can you be more specific about the "shear number of data fields"? How many are we talking about, and are you referring to fields in one table?
All tables for the Requirements nor Supplier spec are different. There are different Product types: ie. Masking Tape, Glue, Paper, Pencils, etc. Each product type's Requirements and Supplier tables will contain different information depending on what the product type is. There are also tolerance % and SI units selectable as dropdown menus for over half the data fields.
First step down the road to ruin. Are you planning on creating a new table every time you need to add a new product? What about the user interface? Create a different form for every product? Try to use one form and programmatically reset the record source every time you need to view a different product in the form?
Creating a new product only creates a new record in the main form. All Requirement and Supplier information is stored in its own product type table ie. Glue, Pencils, Paper... the entries in these tables have duplicate IDs matching what Product they belong to. (One-To-Many relationship)
Then how are you going to uniquely identify each product?
Products are identified by their primary IDs.
Then you lose all the benefits that come with using bound forms, which is one of the most important RAD benefits that make Access the great tool that it is. You would need to write code to handle all record modifications (updates, inserts, deletes). What is RAD? The subforms created as unbound forms, but will have its master/child and sourceobject linked to the main form once main form's current() function runs. Shouldn't that be the same as creating the subform with master/child link and sourceobject predefined through the wizard? I'm just doing it through VB when the main form loads.
If the sub form is unbound then this code wouldn't work. You would need to write an update or append query to get the data to the table.
Does assigning master/child link and sourceobject through VB "bind" the subform?Why won't Access allow me to delete records in form view while I can when I switch it to table view? How do I delete/append records to a table w/o using the Left/Right arrows on the subform window?
Private Sub form_current()
If Me.NewRecord Then
Me.ID = Forms![MainForm]!ID
End If
End Sub
Exactly. And one of your first indications that this isn't going to work.
The subform IDs are defined with the same number as the main form ID each time a NewRecord is created. The problem I'm having is that a subform new record can only be created using the Left/Right/asterisk arrows on the bottom. I can't delete the current record because Access doesn't seem to know which subform record I'm on as there is no unique ID per subform record.
If you can provide some more details about your table structure and, not only what you are trying to accomplish, but why as well, someone may be able to offer some sound advice about how you should proceed.
I'm trying to tie multiple records to each product entry but in the format of a Form and not a table. A picture is worth a thousand words. I am so close yet so far. I can append records to the subforms using the Right or Asterisk arrow on the subform window... but I don't know how to DELETE.
I'm not sure I'm any less confused, but we do seem to ba having a terminology roadblock.
I think I understand that you have multiple child tables for the Products table because each product may have a different set of fields necessary to define it's requirements or specifications (I think).
gave me the impression that you were planning on creating a different table for each Product, but according to this statement;
Creating a new product only creates a new record in the main form. All Requirement and Supplier information is stored in its own product type table ie. Glue, Pencils, Paper... the entries in these tables have duplicate IDs matching what Product they belong to. (One-To-Many relationship)
but if you're going to allow duplicates in the ID field then it won't be useful as a PK. I'm not really sure what your planning here.
What is RAD? The subforms created as unbound forms, but will have its master/child and sourceobject linked to the main form once main form's current() function runs. Shouldn't that be the same as creating the subform with master/child link and sourceobject predefined through the wizard? I'm just doing it through VB when the main form loads.
RAD stands for Rapid Application Development. Access is a good RAD tool in large part because of it's use of bound forms. A bound form means that it has a table or a query as it's record source (i.e. it is directly bound to the data). An unbound form has no record source so the only way to get any data from the form to the table is by writing code to run Append/Update/Delete queries (which means a lot more time invested in developing the User Interface). But I don't think you're really talking about unbound sub forms here. I think what you're trying to say is that you are planning to programmatically reset the Source Object of your Subform Controls to display different sub forms depending on which Product is currently selected in the main form. The sub forms themselves would still be bound to a record source (table or query). That's not the same thing as an unbound form (or sub form). You'll need to clarify if this is what you're really attempting to do.
Looking at the image in your last post, I don't really see what the issue is right off hand. You have two sub forms that are in standard form view (which I think is what you said you wanted in your first post). Also, I'm not really sure what the problem is with deleting/adding a new a record. Are you saying you can't delete records from the sub forms at all? Have you tried placing command buttons in the footers of the subforms to delete/add new records? There are certainly other ways to go about it besides command buttons, but that's a pretty common, and simple, UI technique, so I'm just ondering what you've tried.
Aside from all the above discussion, "Property" is a reserved word and only Bill Gates knows what fun its usage as a control name can lead to in Access
Requirements and specifications (subforms) stored in their own tables per product type
The products (main form) have unique PK IDs of course. The subforms have duplicate IDs enabled because they are tied to the product IDs as One-to-Many.
For example:
Main Form Record ID: 125 Yellow Sticky Paper
Specification Subform ID: 125 Record 1
Color: Non-clear
Width 4 inch
Length: 10 inch
Requirements Subform ID: 125 Record 1
Color: Yellow
Width: 4 inch
Length: 10 inch Record 2
Color: Red
Width: 5 inch
Length: 10 inch Record 3 Color: Blue
Width: 4 inch
Length: 12 inch
I can create new records in the subforms using the subwindow arrows/asterisk keys because it just appends to the table. But I cannot delete a current item from the subform tables.
If you look at the screen shot i posted previously, I wouldn't be able to delete Record 5 of 7 in the Specifications subform if I wanted to.
